First JOIN: fact_orders to dim_stores. SUM aggregate over gross_amount, GROUP BY store, ORDER BY total DESC, LIMIT 10. The shape behind every regional director's dashboard.
Quarterly review prep. Pull the top 10 stores by total gross revenue across all channels. Join fact_orders to dim_stores so I get readable store names. Three columns: store_id, store_name, gross_revenue. ROUND revenue to 2 decimals. ORDER BY gross_revenue DESC, store_id ASC for ties. LIMIT 10. Only orders with units > 0 — skip foot-traffic-no-purchase rows.
| 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.
INNER JOIN fact_orders to dim_stores on store_id.
Aggregate SUM(gross_amount) — round to 2 decimals with ROUND(SUM(...)::numeric, 2).
Filter units > 0 to skip foot-traffic-no-purchase rows.
GROUP BY store_id and store_name (both non-aggregated columns).