SQL Sell-Through and Stockout Rate | Dataford Interview Questions

Write a SQL query to analyze store performance by week at Sephora, joining sales and inventory data to calculate sell-through and stockout rate.

Use calendar weeks beginning on Monday. Sell-through is units sold divided by units sold plus the latest recorded ending inventory for the store's products that week. Stockout rate is stockout inventory observations divided by all inventory observations.

Output

One row per store and calendar week, including weeks present in either source.
Columns: store_id, week_start, units_sold, ending_inventory, sell_through_rate, and stockout_rate.
Sort by store_id, then week_start ascending. Return unavailable rates as NULL.

Schema

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
store_id	INT	Sephora store identifier
product_id	INT	Product identifier
sale_date	DATE	Date of sale
units_sold	INT	Number of units sold in the transaction

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory observation identifier
store_id	INT	Sephora store identifier
product_id	INT	Product identifier
inventory_date	DATE	Date of inventory observation
ending_units	INT	Units available at the observation date
stockout_flag	BOOLEAN	Whether the product was stocked out at the observation date

Your question is SQL Sell-Through and Stockout Rate. Start with the requirements and the two 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