Query top spenders and 7-day growth

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.

Top 3 customers per model with 7-day revenue and growth

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.

Table models

column	type	constraints
model_id	INT	PRIMARY KEY, NOT NULL
model_name	VARCHAR(50)	NOT NULL

Sample data:

model_id	model_name
1	Sedan
2	SUV

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
customer_name	VARCHAR(100)	NOT NULL
country	VARCHAR(50)	NOT NULL

Sample data:

country	customer_id	customer_name
US	101	Alice
US	102	Bob
CN	103	Chen

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
order_date	DATE	NOT NULL
customer_id	INT	NOT NULL
model_id	INT	NOT NULL
qty	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

customer_id	model_id	order_date	order_id	qty	status	unit_price
101	1	2025-05-27	1	1	completed	20000
102	2	2025-05-30	2	1	completed	30000
101	2	2025-05-31	3	1	completed	32000
103	2	2025-06-01	4	2	completed	29000
101	1	2025-05-20	5	1	completed	21000
102	1	2025-05-26	6	1	cancelled	19000
103	2	2025-05-25	7	1	completed	28000
102	2	2025-05-24	8	1	completed	27000

Expected output

One row per model per top customer (up to 3 customers per model) for the last 7-day window (2025-05-26 to 2025-06-01). Each row includes the model's current-/previous-7-day revenue, growth rate, and the customer's spend and share of the model's current 7-day revenue.

curr_revenue	customer_id	customer_name	customer_share_pct	customer_spend	growth_rate	model_name	prev_revenue
20000.00	101	Alice	100	20000.00	-0.0476	Sedan	21000.00
120000.00	103	Chen	48.3333	58000.00	1.1818	SUV	55000.00
120000.00	101	Alice	26.6667	32000.00	1.1818	SUV	55000.00
120000.00	102	Bob	25	30000.00	1.1818	SUV	55000.00

Hints

1. First aggregate per model to get revenue in each 7-day window, using CASE expressions in a GROUP BY.
2. 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.