Interview Questions/SQL/Inventory Stockout Period Detection

Inventory Stockout Period Detection

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Inventory Stockout Period Detection

You are given Products (with a reorder_point threshold) and StockMovements (positive = restock, negative = sale). Using window functions, compute a running stock level and detect every contiguous period where the stock falls strictly below the reorder_point. For each stockout episode, return: product_id, stockout_start (the move_date when stock first dropped below reorder_point), and stockout_end (the move_date when stock first recovered to or above reorder_point). If stock never recovers, stockout_end should be NULL. Return results ordered by product_id, then stockout_start. Table: Products

Column Name	Type	Description
product_id	INT	Primary key
name	VARCHAR	Product name
reorder_point	INT	Stock level below which a stockout is active

Table: StockMovements

Column Name	Type	Description
move_id	INT	Primary key
product_id	INT	References Products
move_date	DATE	Date of movement
quantity	INT	Positive = restock, Negative = sale

Examples

Example 1

Compute running stock using SUM OVER ordered by date. Flag each row as below_reorder (1) or not (0). Use LAG to detect transitions: 0→1 is a stockout start, 1→0 is a stockout end. Pair starts and ends per product using ROW_NUMBER. A start with no matching end gets stockout_end = NULL.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql