Task
NovaCart wants a monthly customer performance report built from raw transactional tables. Write a PostgreSQL query from scratch to return customer-level metrics for January 2024.
Requirements
- Return one row per customer who placed at least one completed order in January 2024.
- Show each customer's total completed orders, total revenue, average order value, and a spending tier using
CASE WHEN:
High for revenue >= 500Medium for revenue between 200 and 499.99Low for revenue < 200
- Rank customers by total revenue within their region using
ROW_NUMBER().
- Only include customers with at least 2 completed orders in January 2024.
- Sort by region, then revenue descending, then customer name.
Table Definitions
customers
| column | type | description |
|---|
| customer_id | INT | Primary key |
| customer_name | VARCHAR(100) | Customer name |
| region | VARCHAR(50) | Customer region |
| signup_date | DATE | Account creation date |
orders
| column | type | description |
|---|
| order_id | INT | Primary key |
| customer_id | INT | References customers |
| order_date | DATE | Order date |
| status | VARCHAR(20) | Order status |
| shipping_fee | NUMERIC(10,2) | Shipping charge |
order_items
| column | type | description |
|---|
| order_item_id | INT | Primary key |
| order_id | INT | References orders |
| product_name | VARCHAR(100) | Product name |
| quantity | INT | Quantity purchased |
| unit_price | NUMERIC(10,2) | Price per unit |
Sample Data
customers
| customer_id | customer_name | region | signup_date |
|---|
| 3 | Carla | East | 2023-11-20 |
| 1 | Alice | North | 2023-12-15 |
| 8 | Hank | West | 2024-01-10 |
| 5 | Eva | South | 2023-08-01 |
orders
| order_id | customer_id | order_date | status | shipping_fee |
|---|
| 104 | 2 | 2024-01-20 | completed | 0.00 |
| 101 | 1 | 2024-01-05 | completed | 10.00 |
| 110 | 7 | 2024-01-21 | completed | 4.00 |
| 106 | 3 | 2024-01-25 | completed | 12.00 |
order_items
| order_item_id | order_id | product_name | quantity | unit_price |
|---|
| 1008 | 105 | Keyboard | 1 | 80.00 |
| 1001 | 101 | Laptop Sleeve | 2 | 25.00 |
| 1014 | 110 | Mouse | 1 | 25.00 |
| 1005 | 103 | Dock | 1 | 150.00 |
Expected Output
| customer_id | customer_name | region | completed_orders | total_revenue | avg_order_value | spending_tier | regional_rank |
|---|
| 3 | Carla | East | 2 | 667.00 | 333.50 | High | 1 |
| 1 | Alice | North | 2 | 330.00 | 165.00 | Medium | 1 |
| 7 | Gina | North | 2 | 90.00 | 45.00 | Low | 2 |
