Latest-snapshot pattern (MAX(date)) + GROUP BY warehouse.
For my morning huddle: total on-hand quantity per warehouse, using only the latest snapshot. Pull from `fact_inventory_snapshots` joined to `dim_warehouses`. Use `MAX(snapshot_date)` to get the latest, then SUM `on_hand_qty` per warehouse. Fair warning — that table has some duplicate rows (same SKU/warehouse/date appears twice for a few). Use DISTINCT or you'll double-count.
| Column | Type | Key |
|---|---|---|
| snapshot_id | INT | PK |
| sku_id | INT | FK → dim_skus |
| warehouse_id | INT | FK → dim_warehouses |
| snapshot_date | TEXT | |
| on_hand_qty | INT | |
| days_of_supply | REAL |
| Column | Type | Key |
|---|---|---|
| warehouse_id | INT | PK |
| warehouse_name | TEXT | |
| region | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-step query: first find the latest snapshot date (`SELECT MAX(snapshot_date) FROM fact_inventory_snapshots`), then filter snapshots to that date.
Use a CTE or subquery: `WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM fact_inventory_snapshots)`.
Wrap in DISTINCT on (sku_id, warehouse_id) before SUM, or use `SELECT DISTINCT sku_id, warehouse_id, on_hand_qty` in a subquery, then GROUP BY warehouse.
JOIN to `dim_warehouses` for the name. SUM(on_hand_qty), GROUP BY warehouse_name, ORDER BY total_on_hand DESC.