Business Context
You’re a data engineer at a large e-commerce marketplace (millions of monthly active buyers, tens of millions of orders per month). Finance and Growth teams use a weekly dashboard to identify the highest-value customers for VIP perks and retention campaigns. The dashboard must rank customers by net sales (paid revenue minus refunds) and break ties deterministically.
The data model is normalized: customer profiles live in customers, orders live in orders, and refunds are recorded at the order level in refunds. Not every order has a refund, and some refunds may be partial.
Task
Write a SQL query to return the top 10 customers by net sales across all time.
Requirements
- Compute gross_sales as the sum of
orders.total_amount for orders with status = 'PAID'.
- Compute refund_amount as the sum of
refunds.refund_amount for refunds with refund_status = 'COMPLETED'.
- Compute net_sales = gross_sales - refund_amount.
- Return:
customer_id, full_name, gross_sales, refund_amount, net_sales.
- Rank customers by
net_sales descending; break ties by customer_id ascending.
- Return only the top 10 customers.
Table Definitions
customers
| column | type | description |
|---|
| customer_id | INT | Primary key |
| full_name | VARCHAR(200) | Customer display name |
| created_at | TIMESTAMP | Account creation timestamp |
orders
| column | type | description |
|---|
| order_id | BIGINT | Primary key |
| customer_id | INT | Foreign key to customers.customer_id |
| order_date | DATE | Date order was placed |
| status | VARCHAR(20) | Order status (e.g., PAID, CANCELLED) |
| total_amount | DECIMAL(12,2) | Total charged amount in USD |
refunds
| column | type | description |
|---|
| refund_id | BIGINT | Primary key |
| order_id | BIGINT | Foreign key to orders.order_id |
| refund_date | DATE | Date refund was issued |
| refund_status | VARCHAR(20) | Refund status (e.g., COMPLETED, PENDING) |
| refund_amount | DECIMAL(12,2) | Amount refunded in USD |
Sample Data
customers
| customer_id | full_name | created_at |
|---|
| 101 | Ava Chen | 2023-01-10 09:15:00 |
| 102 | Mateo Rivera | 2023-02-05 14:20:00 |
| 103 | Priya Nair | 2023-02-21 08:00:00 |
| 104 | Noah Johnson | 2023-03-12 18:45:00 |
orders
| order_id | customer_id | order_date | status | total_amount |
|---|
| 9001 | 101 | 2024-01-02 | PAID | 120.00 |
| 9002 | 101 | 2024-01-20 | PAID | 80.00 |
| 9003 | 102 | 2024-01-05 | PAID | 200.00 |
| 9004 | 103 | 2024-01-07 | CANCELLED | 150.00 |
| 9005 | 104 | 2024-01-09 | PAID | 50.00 |
refunds
| refund_id | order_id | refund_date | refund_status | refund_amount |
|---|
| 7001 | 9002 | 2024-01-25 | COMPLETED | 30.00 |
| 7002 | 9003 | 2024-01-10 | PENDING | 50.00 |
| 7003 | 9005 | 2024-01-12 | COMPLETED | 10.00 |
Expected Output (from sample data)
| customer_id | full_name | gross_sales | refund_amount | net_sales |
|---|
| 102 | Mateo Rivera | 200.00 | 0.00 | 200.00 |
| 101 | Ava Chen | 200.00 | 30.00 | 170.00 |
| 104 | Noah Johnson | 50.00 | 10.00 | 40.00 |
| 103 | Priya Nair | 0.00 | 0.00 | 0.00 |
