Store Stock-Out Rate From Daily Closing Stock

Medium
383.

Store Stock-Out Rate From Daily Closing Stock

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

ColumnType
store_idint
skuvarchar
stock_datedate
closing_qtyint

Primary key (together): store_id, sku, stock_date.

One row per store, SKU and day; closing_qty is NULL where the count failed.

Example

The rows must come back in the order shown.

DailyClosing

store_idskustock_dateclosing_qty
1GRO-10012025-05-0114
1GRO-10012025-05-020
1DAI-20022025-05-010
1DAI-20022025-05-02NULL
2GRO-10012025-05-018
2GRO-10012025-05-023
2SNK-60012025-05-010
3BEV-30012025-05-01NULL

Output

store_idsku_daysstockout_daysstockout_pct
13266.67
23133.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
store_idint
skuvarchar
stock_datedate
closing_qtyint

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_idskustock_dateclosing_qty
1GRO-10012025-05-0114
1GRO-10012025-05-020
1DAI-20022025-05-010
1DAI-20022025-05-02NULL
2GRO-10012025-05-018
2GRO-10012025-05-023
2SNK-60012025-05-010
3BEV-30012025-05-01NULL

Output

store_idsku_daysstockout_daysstockout_pct
13266.67
23133.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_id

Another 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_id

Another 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 →