Interview Guides
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.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 |
| 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 |
