AVG aggregate with WHERE pre-filter (units > 0 to skip foot-traffic-no-purchase), JOIN, multi-key ORDER BY. The in-store AOV ranking every store director watches.
Store comp prep. Average order value for in-store purchases only (channel = 'store_walk_in', units > 0 — drop the foot-traffic-no-purchase rows). Top 10 stores. Four columns: store_id, store_name, order_count, aov. ROUND aov to 2 decimals. ORDER BY aov DESC, store_id ASC.
| 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.
Filter channel = 'store_walk_in' AND units > 0 (CRITICAL — skip foot-traffic-no-purchase).
AVG(gross_amount) for AOV, ROUND to 2 decimals.
GROUP BY store_id and store_name.