Find top category per region in Aug 2025

You are given the following schema and sample data.

Schema:

• customers(customer_id INT, name TEXT, region TEXT)
• orders(order_id INT, customer_id INT, order_date DATE, status TEXT)
• order_items(order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2))
• products(product_id INT, category TEXT)

Sample tables:

customers
+-------------+-------+--------+
| customer_id | name | region |
+-------------+-------+--------+
| 1 | Alice | East |
| 2 | Bob | East |
| 3 | Cara | West |
| 4 | Dan | West |
+-------------+-------+--------+

products
+------------+----------+
| product_id | category |
+------------+----------+
| 10 | Gadgets |
| 11 | Gadgets |
| 12 | Widgets |
+------------+----------+

orders
+----------+-------------+------------+----------+
| order_id | customer_id | order_date | status |
+----------+-------------+------------+----------+
| 101 | 1 | 2025-08-05 | Delivered|
| 102 | 1 | 2025-08-20 | Cancelled|
| 103 | 2 | 2025-08-21 | Delivered|
| 104 | 3 | 2025-08-25 | Delivered|
| 105 | 4 | 2025-08-30 | Delivered|
| 106 | 3 | 2025-09-02 | Delivered|
+----------+-------------+------------+----------+

order_items
+----------+------------+-----+------------+
| order_id | product_id | qty | unit_price |
+----------+------------+-----+------------+
| 101 | 10 | 2 | 50.00 |
| 101 | 12 | 1 | 80.00 |
| 103 | 10 | 1 | 60.00 |
| 103 | 12 | 3 | 30.00 |
| 104 | 11 | 2 | 40.00 |
| 104 | 12 | 1 | 100.00 |
| 105 | 12 | 4 | 25.00 |
| 106 | 10 | 5 | 10.00 |
+----------+------------+-----+------------+

Task: Write a single SQL statement that returns, for each region, the product category with the highest total delivered revenue in August 2025 (2025-08-01 to 2025-08-31). Revenue is SUM(qty*unit_price) from order_items, but only include orders with status='Delivered' and order_date in August 2025. Output columns: region, category, total_revenue. Break ties by choosing the lexicographically smallest category. You must use exactly one subquery or one CTE (no more), and you may not use window functions.

Top product category by delivered revenue per region (Aug 2025)

Given the tables customers, orders, order_items, and products, write a single SQL statement that returns, for each customer region, the product category with the highest total delivered revenue in August 2025 (from 2025-08-01 to 2025-08-31 inclusive).

Revenue is computed as SUM(qty * unit_price) from order_items, but only for orders where status = 'Delivered' and order_date is within August 2025.

Output columns:

• region
• category
• total_revenue

Tie-break rule: if multiple categories have the same highest total_revenue within a region, return the lexicographically smallest category.

Constraint: You must use exactly one CTE or exactly one subquery (no more than one), and you may not use window functions.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
region	VARCHAR(20)	NOT NULL

Sample data:

customer_id	name	region
1	Alice	East
2	Bob	East
3	Cara	West
4	Dan	West

Table products

column	type	constraints
product_id	INT	PRIMARY KEY, NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id
Gadgets	10
Gadgets	11
Widgets	12

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

customer_id	order_date	order_id	status
1	2025-08-05	101	Delivered
1	2025-08-20	102	Cancelled
2	2025-08-21	103	Delivered
3	2025-08-25	104	Delivered
4	2025-08-30	105	Delivered
3	2025-09-02	106	Delivered

Table order_items

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

Sample data:

order_id	product_id	qty	unit_price
101	10	2	50
101	12	1	80
103	10	1	60
103	12	3	30
104	11	2	40
104	12	1	100
105	12	4	25
106	10	5	10

Expected output

One row per region showing the category with the highest total delivered revenue in August 2025, with ties broken by lexicographically smallest category.

category	region	total_revenue
Widgets	East	170
Widgets	West	200

Hints

1. First aggregate revenue by (region, category) for Delivered orders in 2025-08-01 to 2025-08-31.
2. To pick the top category per region without window functions, use a self-join anti-join pattern that eliminates rows beaten by a higher revenue (or same revenue but lexicographically smaller category).