First conditional aggregation. Practice SUM(refunded) treating the 0/1 INT as boolean for counting, computing a percentage with explicit ROUND, and a 4-column output the customer-experience team can read.
Weekly CX review. Compute the all-time refund rate by marketplace. Four columns: marketplace_name, total_orders, refunded_orders (sum of fact_orders.refunded which is 0/1), refund_rate_pct (rounded to 1 decimal). Sort refund_rate_pct descending, marketplace_name ascending. Refund rate above 6% on any marketplace is the early warning we escalate.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| marketplace_id | INT | FK → dim_marketplaces |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| refunded | INT |
| Column | Type | Key |
|---|---|---|
| marketplace_id | INT | PK |
| marketplace_name | TEXT | |
| marketplace_fee_pct | REAL | |
| fba_supported | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN dim_marketplaces on marketplace_id.
SUM(refunded) gives the count of refunded orders since refunded is 0/1 INT.
ROUND(100.0 * SUM(refunded) / COUNT(*), 1) — the 100.0 forces float promotion.
Sort refund_rate_pct DESC, marketplace_name ASC.