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 |