Store Stock-Out Rate From Daily Closing Stock — SQL Problem
Each store records the closing stock of every tracked SKU at the end of the day. A stock-out day is a SKU-day whose closing_qty is 0.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #383
Problem statement
Each store records the closing stock of every tracked SKU at the end of the day. A stock-out day is a SKU-day whose closing_qty is 0. When the handheld scanner failed, closing_qty was saved as NULL; those SKU-days are left out of both the numerator and the denominator.
For every store with at least one recorded (non-NULL) closing, return store_id, sku_days (recorded SKU-days), stockout_days and stockout_pct = 100 × stockout_days / sku_days, rounded to 2 decimal places. Order the rows by stockout_pct highest first, then by store_id.
Tables
Table: DailyClosing
| Column | Type |
|---|---|
| store_id | int |
| sku | varchar |
| stock_date | date |
| closing_qty | int |
Primary key: store_id, sku, stock_date.
One row per store, SKU and day; closing_qty is NULL where the count failed.
Examples
Example 1
DailyClosing
| store_id | sku | stock_date | closing_qty |
|---|---|---|---|
| 1 | GRO-1001 | 2025-05-01 | 14 |
| 1 | GRO-1001 | 2025-05-02 | 0 |
| 1 | DAI-2002 | 2025-05-01 | 0 |
| 1 | DAI-2002 | 2025-05-02 | NULL |
| 2 | GRO-1001 | 2025-05-01 | 8 |
| 2 | GRO-1001 | 2025-05-02 | 3 |
| 2 | SNK-6001 | 2025-05-01 | 0 |
| 3 | BEV-3001 | 2025-05-01 | NULL |
Output
| store_id | sku_days | stockout_days | stockout_pct |
|---|---|---|---|
| 1 | 3 | 2 | 66.67 |
| 2 | 3 | 1 | 33.33 |
How to solve Store Stock-Out Rate From Daily Closing Stock
This is a ratio of two counts over the same group, computed with conditional aggregation. SUM(CASE WHEN closing_qty = 0 THEN 1 ELSE 0 END) counts the stock-out days; COUNT(closing_qty) counts the recorded days, skipping the scanner failures by itself because COUNT of a column ignores NULLs. A NULL closing is also never = 0, so it does not enter the numerator either.
A store whose rows are all NULL has a denominator of 0; the statement leaves it out, and HAVING COUNT(closing_qty) > 0 does that before the division could fail. Filtering NULLs in WHERE first, as the first alternative does, removes such a store automatically because it then has no rows left to group.
Multiplying by 100 before dividing and rounding to two places keeps MySQL's four-decimal division and other engines' floats in agreement. NULLIF(sku_days, 0) is the general guard against division by zero when you cannot filter. One scan of the table does it all.
Reference solution (MySQL)
SELECT store_id,
COUNT(closing_qty) AS sku_days,
SUM(CASE WHEN closing_qty = 0 THEN 1 ELSE 0 END) AS stockout_days,
ROUND(100 * SUM(CASE WHEN closing_qty = 0 THEN 1 ELSE 0 END) / COUNT(closing_qty), 2) AS stockout_pct
FROM DailyClosing
GROUP BY store_id
HAVING COUNT(closing_qty) > 0
ORDER BY stockout_pct DESC, store_idAnother way
SELECT store_id, COUNT(*) AS sku_days, SUM(IF(closing_qty = 0, 1, 0)) AS stockout_days, ROUND(100 * SUM(IF(closing_qty = 0, 1, 0)) / COUNT(*), 2) AS stockout_pct FROM DailyClosing WHERE closing_qty IS NOT NULL GROUP BY store_id ORDER BY stockout_pct DESC, store_idAnother way
SELECT store_id, sku_days, stockout_days, ROUND(100 * stockout_days / NULLIF(sku_days, 0), 2) AS stockout_pct FROM (SELECT store_id, COUNT(closing_qty) AS sku_days, COUNT(CASE WHEN closing_qty = 0 THEN 1 END) AS stockout_days FROM DailyClosing GROUP BY store_id) t WHERE sku_days > 0 ORDER BY stockout_pct DESC, store_id← Purchase Order Lines Received Short · Top-Selling SKU in Each Store, Ties Included →