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.