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 |
