SQL for Store Sell-Through | Dataford Interview Questions

Write a SQL query to analyze weekly store performance by location, joining sales and inventory tables to identify stores with declining sell-through and low stock at Specialized Bicycle Components.

Treat low stock as total ending inventory below the store's total reorder point, and declining sell-through as lower than the prior available week.

Output

One row per qualifying store and week, with location, week_start, sell_through, prior_sell_through, ending_stock, and reorder_point.
Include only weeks meeting both conditions.
Order by location, then week_start.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
location	VARCHAR(100)	Store location

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
store_id	INT	Store where the sale occurred
product_id	INT	Product sold
sale_date	DATE	Date of sale
units_sold	INT	Units sold in the transaction record

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory snapshot identifier
store_id	INT	Store holding the inventory
product_id	INT	Product being stocked
inventory_date	DATE	Date of inventory snapshot
units_on_hand	INT	Units available at the snapshot
reorder_point	INT	Minimum desired units for the product

Your question is SQL for Store Sell-Through. 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