Your question is Top Customers by Net Sales. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
Write a SQL query to return the top 10 customers by net sales across all time.
orders.total_amount for orders with status = 'PAID'.refunds.refund_amount for refunds with refund_status = 'COMPLETED'.customer_id, full_name, gross_sales, refund_amount, net_sales.net_sales descending; break ties by customer_id ascending.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Primary key |
| full_name | VARCHAR(200) | Customer display name; not null |
| created_at | TIMESTAMP | Account creation timestamp; not null |
| Column | Type | Description |
|---|---|---|
| order_idPK | BIGINT | Primary key |
| customer_id | INT | Foreign key to customers.customer_id; not null |
| order_date | DATE | Date order was placed; not null |
| status | VARCHAR(20) | Order status (PAID, CANCELLED, etc.); not null |
| total_amount | DECIMAL(12,2) | Total charged amount in USD; not null |
| Column | Type | Description |
|---|---|---|
| refund_idPK | BIGINT | Primary key |
| order_id | BIGINT | Foreign key to orders.order_id; not null |
| refund_date | DATE | Date refund was issued; not null |
| refund_status | VARCHAR(20) | Refund status (COMPLETED, PENDING, etc.); not null |
| refund_amount | DECIMAL(12,2) | Amount refunded in USD; not null |