Multi-CTE pipeline. AVG inventory + SUM sold across the same 6-month window. Ratio of velocity to inventory. The fundamental inventory metric every merchandiser reads weekly.
Buying-window review. For each (store, hot SKU) pair, compute inventory turns over Oct 2025 → Mar 2026 (6 months): units sold / average daily units on hand. Top 10 by turns. Five columns: store_name, product_title, units_sold, avg_inv, turns. ROUND avg_inv to 1 decimal, turns to 2 decimals. ORDER BY turns DESC, store_id ASC, sku_id ASC for ties. Only is_hot_inventory = 1 SKUs (the 20 we track daily).
| 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 |
|---|---|---|
| snapshot_id | INT | PK |
| store_id | INT | FK → dim_stores |
| sku_id | INT | FK → dim_skus |
| snapshot_date | TEXT | |
| units_on_hand | INT |
| Column | Type | Key |
|---|---|---|
| store_id | INT | PK |
| store_name | TEXT | |
| region | TEXT | |
| city | TEXT | |
| state | TEXT | |
| sq_ft | INT | |
| opened_date | TEXT |
| Column | Type | Key |
|---|---|---|
| sku_id | INT | PK |
| sku_code | TEXT | |
| product_title | TEXT | |
| category | TEXT | |
| supplier_cost | REAL | |
| list_price | REAL | |
| weight_lbs | REAL | |
| is_hot_inventory | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two CTEs: `sold` (SUM units from fact_orders, filtered to the 6-month window + units > 0 + store_id NOT NULL) and `inv` (AVG units_on_hand from fact_inventory_by_location in the same window).
JOIN sold + inv on (store_id, sku_id), then JOIN to dim_stores + dim_skus for names.
Filter dim_skus.is_hot_inventory = 1 in the outer SELECT.
turns = units_sold / NULLIF(avg_inv, 0).