Task
Nimbus Retail wants to identify its top-performing sales region for Q1 2024. Write a SQL query to find the region with the highest total completed sales amount during that period.
Requirements
- Use only orders with
status = 'completed'.
- Include only orders with
order_date from 2024-01-01 through 2024-03-31.
- Sum
amount by region using the customer-to-region mapping.
- Return the single top-performing region with its total revenue.
Table definitions
regions
| column | type | description |
|---|
| region_id | INT | Primary key for each region |
| region_name | VARCHAR(50) | Region name |
customers
| column | type | description |
|---|
| customer_id | INT | Primary key for each customer |
| customer_name | VARCHAR(100) | Customer name |
| region_id | INT | Customer's assigned region |
orders
| column | type | description |
|---|
| order_id | INT | Primary key for each order |
| customer_id | INT | Customer placing the order |
| order_date | DATE | Date of the order |
| amount | DECIMAL(10,2) | Order amount |
| status | VARCHAR(20) | Order status |
Sample data
regions
| region_id | region_name |
|---|
| 1 | North |
| 2 | South |
| 3 | East |
| 4 | West |
customers
| customer_id | customer_name | region_id |
|---|
| 101 | Ava Corp | 1 |
| 102 | Blue Labs | 2 |
| 103 | Crest LLC | 3 |
| 104 | Delta Inc | 1 |
| 105 | Echo Stores | 4 |
orders
| order_id | customer_id | order_date | amount | status |
|---|
| 1001 | 101 | 2024-02-10 | 500.00 | completed |
| 1002 | 102 | 2024-01-15 | 900.00 | completed |
| 1003 | 103 | 2024-03-05 | 700.00 | pending |
| 1004 | 104 | 2024-03-20 | 800.00 | completed |
| 1005 | 105 | 2024-02-28 | 650.00 | completed |
Expected output
| region_name | total_revenue |
|---|
| North | 1600.00 |
