Operations & Supply Chain Analytics · Mission 9 of 30Easy

Stock-out risk: SKUs with under 7 days of supply

Filter on latest snapshot, JOIN to dim for readable output.

Back to Operations & Supply Chain Analytics

The Brief

Devon ParkWarehouse Leadslack-dm

Stock-out risk alert: which SKUs have under 7 days of supply on the latest snapshot? Pull sku_name, warehouse_name, and days_of_supply. Use the latest snapshot date only. Same dedup trick as before — that table has duplicates.

You'll practice

Latest snapshotWHERE filterJOIN to dim

Tables & columns available

fact_inventory_snapshotsfact6 columns

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

dim_skusdim6 columns

Column	Type	Key
sku_id	INT	PK
sku_code	TEXT
sku_name	TEXT
category	TEXT
unit_cost	REAL
reorder_point	INT

dim_warehousesdim3 columns

Column	Type	Key
warehouse_id	INT	PK
warehouse_name	TEXT
region	TEXT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

Filter to the latest snapshot: `WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM fact_inventory_snapshots)`.

Hint 2

Filter on `days_of_supply < 7`. JOIN to `dim_skus` for sku_name and `dim_warehouses` for warehouse_name.

Hint 3

Use DISTINCT to handle duplicate snapshot rows. ORDER BY days_of_supply ASC so the most-at-risk float to the top.

← Previous

Mission 8

Mission 10