SQL for Stock Changes and Reorders | Dataford Interview Questions

Write a SQL query to calculate week-over-week changes in stock levels and flag items that fall below reorder thresholds for Acme Fresh Market.

Use the provided product, store, and inventory snapshot data. Include only snapshots from active stores and return weeks that have a prior week for the same item.

Output

One row per item and week, with item_name, week_start, current_stock, prior_week_stock, wow_change, wow_change_percent, and below_reorder_threshold
Include only rows with a prior week, ordered by item_name and week_start ascending

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
reorder_threshold	INT	Minimum desired total stock level

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store name
is_active	BOOLEAN	Whether the store currently contributes inventory

inventory_snapshots

Column	Type	Description
snapshot_idPK	INT	Unique inventory snapshot identifier
product_id	INT	Referenced product
store_id	INT	Referenced store
snapshot_date	DATE	Date of the inventory observation
stock_level	INT	Units in stock at the store

Your question is SQL for Stock Changes and Reorders. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem