Conditional aggregation for ratio metrics. JOIN, GROUP BY region, ratio with NULLIF. The omnichannel mix lens every regional VP reviews quarterly.
Quarterly omnichannel review. By region: what pct of orders are store_ship_from_store vs total fulfilled orders (units > 0)? Four columns: region, total_orders, sfs_orders, sfs_pct. ROUND sfs_pct to 2 decimals. ORDER BY sfs_pct DESC, region ASC. Skip foot-traffic-no-purchase rows entirely (units > 0).
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| sku_id | INT | FK → dim_skus |
| store_id | INT | FK → dim_stores |
| channel | TEXT | |
| customer_zip | TEXT | |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| discount_amount | REAL | |
| fulfillment_cost | REAL |
| Column | Type | Key |
|---|---|---|
| store_id | INT | PK |
| store_name | TEXT | |
| region | TEXT | |
| city | TEXT | |
| state | TEXT | |
| sq_ft | INT | |
| opened_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN fact_orders to dim_stores for region (web_ship orders won't join — they have store_id NULL).
Use INNER JOIN to drop web_ship from the count — the question is by-region.
Conditional aggregation: SUM(CASE WHEN channel = 'store_ship_from_store' THEN 1 ELSE 0 END) AS sfs_orders.
sfs_pct = 100.0 * sfs_orders / NULLIF(total_orders, 0), rounded.