Find top-spend categories per customer with ranking

Using the schema and sample data below, write a single ANSI SQL query (CTEs allowed; no temp tables) that returns, for each customer, their top 2 product categories by total spend. Output columns: customer_id, category, total_spend, earliest_order_date, rank_in_customer. Requirements:

• total_spend = SUM(oi.qty * oi.unit_price) per (customer_id, category).
• earliest_order_date = MIN(o.order_date) within that (customer_id, category).
• Use RANK() OVER (PARTITION BY customer_id ORDER BY total_spend DESC, earliest_order_date ASC) as rank_in_customer.
• Return rows where rank_in_customer <= 2, including all ties at rank 2.
• Include customers with no orders as a single row: category = NULL, total_spend = 0, earliest_order_date = NULL, rank_in_customer = NULL (do not assign rank for zero-spend customers). Hint: careful LEFT JOINs and conditional ranking.
• Avoid vendor-specific extensions beyond standard window functions. Follow-ups (closely related; keep as part of the same query via additional columns/CTEs): A) Add percent_of_total = total_spend / SUM(total_spend) OVER (PARTITION BY customer_id), and ensure it is 0 for customers with no orders. B) Return only customer_ids whose top category’s percent_of_total < 0.5 (i.e., their spend is not dominated by a single category).

Schema:

customers(id INT PK, name TEXT)
orders(id INT PK, customer_id INT FK -> customers.id, order_date DATE)
order_items(id INT PK, order_id INT FK -> orders.id, product_id INT FK -> products.id, qty INT, unit_price DECIMAL(10,2))
products(id INT PK, category TEXT, name TEXT)

Sample data:

customers
id | name
1 | Alice
2 | Bob
3 | Cara
4 | Dana

orders
id | customer_id | order_date
101 | 1 | 2025-07-02
102 | 1 | 2025-07-15
103 | 2 | 2025-07-20
104 | 2 | 2025-07-21
105 | 3 | 2025-07-22

order_items
id | order_id | product_id | qty | unit_price
1001 | 101 | 201 | 2 | 10.00
1002 | 101 | 202 | 1 | 20.00
1003 | 102 | 201 | 1 | 10.00
1004 | 102 | 203 | 5 | 5.00
1005 | 103 | 202 | 2 | 20.00
1006 | 104 | 203 | 10 | 5.00
1007 | 105 | 204 | 1 | 100.00

products
id | category | name
201 | CatA | A
202 | CatA | B
203 | CatB | C
204 | CatC | D

Top-2 spend categories per customer with ranking, percent-of-total, and dominance filter

Using the tables and sample data below, write a single ANSI SQL query (CTEs allowed; no temp tables) that returns, for each customer, their top 2 product categories by total spend.

Output columns:

• customer_id
• category
• total_spend
• earliest_order_date
• rank_in_customer
• percent_of_total

Rules:

1. total_spend = SUM(order_items.qty * order_items.unit_price) per (customer_id, category).
2. earliest_order_date = MIN(orders.order_date) within that (customer_id, category).
3. rank_in_customer must be computed as: RANK() OVER (PARTITION BY customer_id ORDER BY total_spend DESC, earliest_order_date ASC)
4. Return rows where rank_in_customer <= 2, including all ties at rank 2.
5. Include customers with no orders as a single row with: category = NULL, total_spend = 0, earliest_order_date = NULL, rank_in_customer = NULL, percent_of_total = 0. (Do not assign a rank for zero-spend customers.)
6. percent_of_total = total_spend / SUM(total_spend) OVER (PARTITION BY customer_id). Ensure it is 0 for customers with no orders.
7. Final filter: return only customer_ids whose TOP category’s percent_of_total < 0.5 (i.e., their spend is not dominated by a single category). Customers with no orders should be treated as having top category percent_of_total = 0.

Use only standard SQL features (window functions are allowed).

Table customers

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

id	name
1	Alice
2	Bob
3	Cara
4	Dana
5	Evan

Table orders

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

customer_id	id	order_date
1	101	2025-07-02
1	102	2025-07-15
2	103	2025-07-20
2	104	2025-07-21
3	105	2025-07-22
5	106	2025-07-10

Table order_items

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
order_id	INT	NOT NULL
product_id	INT	NOT NULL
qty	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL

Sample data:

id	order_id	product_id	qty	unit_price
1001	101	201	2	10
1002	101	202	1	20
1003	102	201	1	10
1004	102	203	5	5
1005	103	202	2	20
1006	104	203	10	5
1007	105	204	1	100
1008	106	201	4	10
1009	106	203	7	5
1010	106	204	1	35

Table products

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
category	VARCHAR(50)	NOT NULL
name	VARCHAR(100)	NOT NULL

Sample data:

category	id	name
CatA	201	A
CatA	202	B
CatB	203	C
CatC	204	D

Expected output

Each returned row is one of the (up to) top-2 ranked categories per qualifying customer (including ties at rank 2), with spend, earliest order date for that category, the category rank within the customer, and percent of that customer's total spend. Customers with no orders appear as one row with NULL category, 0 spend, NULL dates/rank, and percent_of_total = 0. Additionally, only customers whose top category percent_of_total is < 0.5 are included.

category	customer_id	earliest_order_date	percent_of_total	rank_in_customer	total_spend
NULL	4	NULL	0	NULL	0
CatA	5	2025-07-10	0.3636	1	40
CatB	5	2025-07-10	0.3182	2	35
CatC	5	2025-07-10	0.3182	2	35

Hints

1. Aggregate spend and earliest order date per (customer_id, category) first, then apply window functions.
2. Use RANK() and filter rank_in_customer <= 2 to keep ties at rank 2.
3. Compute top-category percent via MAX(CASE WHEN rank=1 THEN percent END) OVER (PARTITION BY customer_id) and filter it to be < 0.5.
4. Handle customers with no orders using a separate CTE and UNION ALL a single default row; keep their rank NULL and percent_of_total = 0.