Top Categories by Delivery RegionCompany: Amazon | Difficulty: medium

Amazon wants to identify the product categories generating the most fulfilled revenue in each delivery region during the first quarter of 2025.

Write a query to return the delivery region, product category, and total fulfilled revenue for the top two categories in each region. Include only orders with a status of Completed and an order date from January 1, 2025 through March 31, 2025. If categories tie for the second position, include all tied categories.

Return the columns in this order: region, category, total_revenue, and category_rank. Sort by region descending, category rank descending, and category descending.

Table Schema

Table	Column	Type
orders	order_id	integer
orders	region	varchar(50)
orders	category	varchar(80)
orders	order_date	date
orders	quantity	integer
orders	unit_price	decimal(10,2)
orders	status	varchar(20)

Example Input: orders

order_id	region	category	order_date	quantity	unit_price	status
1	East	Electronics	2025-01-10	2	500.00	Completed
2	East	Books	2025-02-05	10	20.00	Completed
3	East	Home	2025-03-01	4	100.00	Completed
4	West	Books	2025-01-15	5	40.00	Completed
5	West	Home	2025-02-20	2	100.00	Completed
6	West	Toys	2025-03-10	1	50.00	Cancelled

Example Output Explanation

In the East region, Electronics generates 1000.00 and Home generates 400.00, making them the top two categories. Books is excluded because its revenue is lower. In the West region, Books and Home are included, while the cancelled Toys order is ignored.

SchemaDROP TABLE IF EXISTS orders; CREATE TABLE orders ( order_id INT, region VARCHAR(50), category VARCHAR(80), order_date DATE, quantity INT, unit_price DECIMAL(10, 2), status VARCHAR(20) ); INSERT INTO orders VALUES (1, 'East', 'Electronics', DATE '2025-01-10', 2, 500.00, 'Completed'), (2, 'East', 'Books', DATE '2025-02-05', 10, 20.00, 'Completed'), (3, 'East', 'Home', DATE '2025-03-01', 4, 100.00, 'Completed'), (4, 'West', 'Books', DATE '2025-01-15', 5, 40.00, 'Completed'), (5, 'West', 'Home', DATE '2025-02-20', 2, 100.00, 'Completed'), (6, 'West', 'Toys', DATE '2025-03-10', 1, 50.00, 'Cancelled');← All SQL Challenges · Amazon Questions · Learn SQL Free