Query top spenders and 7-day growth
Company: Boston Consulting Group
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Assume 'today' = 2025-09-01. Write a SQL query to: (1) for each model, compute total revenue in the last 7 days (2025-08-26 to 2025-09-01 inclusive) and the previous 7 days (2025-08-19 to 2025-08-25), excluding orders with status <> 'completed'; (2) for each model, list the top 3 customers by spend in the last 7 days with their spend and their percentage share of the model’s 7-day revenue; (3) report, per model, the 7-day-over-7-day revenue growth rate: (curr - prev)/NULLIF(prev, 0). Return: model_name, curr_revenue, prev_revenue, growth_rate, customer_id, customer_name, customer_spend, customer_share_pct. Break ties for top-3 by the earliest first purchase date of the customer on that model (use the full order history).
Schema and small sample data:
models
model_id | model_name
1 | Sedan
2 | SUV
customers
customer_id | customer_name | country
101 | Alice | US
102 | Bob | US
103 | Chen | CN
orders
order_id | order_date | customer_id | model_id | qty | unit_price | status
1 | 2025-08-27 | 101 | 1 | 1 | 20000 | completed
2 | 2025-08-30 | 102 | 2 | 1 | 30000 | completed
3 | 2025-08-31 | 101 | 2 | 1 | 32000 | completed
4 | 2025-09-01 | 103 | 2 | 2 | 29000 | completed
5 | 2025-08-20 | 101 | 1 | 1 | 21000 | completed
6 | 2025-08-26 | 102 | 1 | 1 | 19000 | cancelled
7 | 2025-08-25 | 103 | 2 | 1 | 28000 | completed
8 | 2025-08-24 | 102 | 2 | 1 | 27000 | completed
Notes: revenue = qty*unit_price. Use window functions for ranking, partitioning by model, and tie-breaking by first purchase date.
Overview: This question evaluates proficiency in SQL data manipulation, including aggregations, date-range filtering, joins, window functions for ranking and tie-breaking, and computing revenue shares and growth rates within a relational schema.
Assume today is 2025-06-01.
Write a SQL query to:
1) For each model, compute total revenue in the last 7 days (2025-05-26 to 2025-06-01 inclusive) and in the previous 7 days (2025-05-19 to 2025-05-25 inclusive), **excluding** orders whose status is not 'completed'. Revenue is defined as `qty * unit_price`.
2) For each model, list the **top 3 customers** by spend in the last 7 days (2025-05-26 to 2025-06-01), along with:
- their total spend on that model in that 7-day window, and
- their percentage share of the model’s 7-day revenue (customer_spend / model_7_day_revenue * 100).
3) Report, per model, the 7-day-over-7-day revenue growth rate, defined as:
`(curr_revenue - prev_revenue) / NULLIF(prev_revenue, 0)`.
Return the following columns:
- `model_name`
- `curr_revenue` (revenue from 2025-05-26 to 2025-06-01, completed orders only)
- `prev_revenue` (revenue from 2025-05-19 to 2025-05-25, completed orders only)
- `growth_rate` (as defined above)
- `customer_id`
- `customer_name`
- `customer_spend` (customer's spend on that model in the last 7 days)
- `customer_share_pct` (percentage share of the model's 7-day revenue)
If a model has fewer than 3 customers with spend in the last 7 days, return only the available customers. Break ties for the top-3 ranking by the **earliest first purchase date of the customer on that model** using the full order history (all dates), and if still tied, by `customer_id`.
Use window functions for ranking (partitioning by model) and for implementing the tie-breaker logic.
Tables
models(model_id INT, model_name VARCHAR(50))
customers(customer_id INT, customer_name VARCHAR(100), country VARCHAR(50))
orders(order_id INT, order_date DATE, customer_id INT, model_id INT, qty INT, unit_price DECIMAL(10,2), status VARCHAR(20))
Hints
- First aggregate per model to get revenue in each 7-day window, using CASE expressions in a GROUP BY.
- Then aggregate per (model, customer) for the last 7 days, compute each customer's first purchase date per model, and use ROW_NUMBER with an ORDER BY that applies the tie-breaker to select the top 3 customers per model.