Task
Whatnot's operations team wants a quick view of buyer monetization by geography. Write a SQL query to calculate the average order value per user by region.
Requirements
- Join
orders to users using user_id.
- Group results by
region.
- Return each region and its average order value as
avg_order_value.
- Exclude orders that are not linked to a valid user region.
- Order the final result by
avg_order_value descending, then region ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| username | VARCHAR(50) | Whatnot username |
| region | VARCHAR(50) | User region |
| signup_date | DATE | Date the user joined |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| user_id | INT | User who placed the order |
| order_total | DECIMAL(10,2) | Total order amount |
| order_date | DATE | Date of purchase |
Sample Data
users
| user_id | username | region | signup_date |
|---|
| 1 | seller_sam | West | 2024-01-10 |
| 2 | vintage_via | East | 2024-01-12 |
| 3 | card_king | South | 2024-02-01 |
| 4 | toy_town | West | 2024-02-05 |
orders
| order_id | user_id | order_total | order_date |
|---|
| 101 | 2 | 80.00 | 2024-03-01 |
| 102 | 1 | 120.00 | 2024-03-03 |
| 103 | 4 | 60.00 | 2024-03-04 |
| 104 | 2 | 40.00 | 2024-03-05 |
Expected Output
| region | avg_order_value |
|---|
| North | 200.00 |
| West | 90.00 |
| East | 60.00 |
| South | 20.00 |
