Daily Stock Carried Forward From the Last Count
A dark store counts SKUs only now and then, but the dashboard needs a figure for every day from 2025-05-01 to 2025-05-07.
- Difficulty: Hard
- Topics: Dates, Subqueries, Joins
- Dialect: MySQL
- Problem: #396
Problem statement
A dark store counts SKUs only now and then, but the dashboard needs a figure for every day from 2025-05-01 to 2025-05-07. Each day shows the most recent count taken on or before that day (a count from April carries into May). A SKU has no row for days before its first count ever.
Return sku, stock_day, qty (the carried-forward count) and counted_on (the date of that count) for every SKU in StockCount and every day of the window on which it has a count to carry. Counts after the window change nothing. Order the rows by sku, then by stock_day.
Tables
Table: StockCount
| Column | Type |
|---|---|
| sku | varchar |
| count_date | date |
| counted_qty | int |
Primary key: sku, count_date.
One row per physical count of a SKU (at most one a day).
Examples
Example 1
StockCount
| sku | count_date | counted_qty |
|---|---|---|
| GRO-1001 | 2025-04-28 | 34 |
| GRO-1001 | 2025-05-03 | 18 |
| GRO-1001 | 2025-05-06 | 52 |
| DAI-2002 | 2025-05-05 | 11 |
| DAI-2002 | 2025-05-09 | 40 |
| SNK-6001 | 2025-05-12 | 75 |
Output
| sku | stock_day | qty | counted_on |
|---|---|---|---|
| DAI-2002 | 2025-05-05 | 11 | 2025-05-05 |
| DAI-2002 | 2025-05-06 | 11 | 2025-05-05 |
| DAI-2002 | 2025-05-07 | 11 | 2025-05-05 |
| GRO-1001 | 2025-05-01 | 34 | 2025-04-28 |
| GRO-1001 | 2025-05-02 | 34 | 2025-04-28 |
| GRO-1001 | 2025-05-03 | 18 | 2025-05-03 |
| GRO-1001 | 2025-05-04 | 18 | 2025-05-03 |
| GRO-1001 | 2025-05-05 | 18 | 2025-05-03 |
| GRO-1001 | 2025-05-06 | 52 | 2025-05-06 |
| GRO-1001 | 2025-05-07 | 52 | 2025-05-06 |
How to solve Daily Stock Carried Forward From the Last Count
Two things are missing from the data: the days themselves, and a value on days nobody counted. A recursive CTE supplies the first — it starts at 2025-05-01 and adds a day with DATE_ADD(…, INTERVAL 1 DAY) until 2025-05-07, a bounded seven-row series. CAST(… AS DATE) in the anchor fixes the column's type so MySQL does not treat it as a seven-character string.
Every SKU is then paired with every day (CROSS JOIN of the distinct SKUs and the calendar), and a correlated MAX(count_date) … WHERE count_date <= stock_day finds the count that applies — the last observation carried forward. Joining back to StockCount on that date fetches the quantity; on days before a SKU's first count the MAX is NULL, the join finds nothing and the row disappears, exactly as the statement asks. Counts after the window can never be ≤ a window day, so they are ignored.
The alternative joins every day to every earlier count and keeps the newest with ROW_NUMBER() … ORDER BY count_date DESC. Both are small here: seven days × the counts per SKU.
Reference solution (MySQL)
WITH RECURSIVE calendar (stock_day) AS (
SELECT CAST('2025-05-01' AS DATE)
UNION ALL
SELECT DATE_ADD(stock_day, INTERVAL 1 DAY) FROM calendar WHERE stock_day < '2025-05-07'
), latest AS (
SELECT k.sku, c.stock_day,
(SELECT MAX(s.count_date) FROM StockCount s WHERE s.sku = k.sku AND s.count_date <= c.stock_day) AS counted_on
FROM (SELECT DISTINCT sku FROM StockCount) k
CROSS JOIN calendar c
)
SELECT l.sku, l.stock_day, s.counted_qty AS qty, l.counted_on
FROM latest l
JOIN StockCount s ON s.sku = l.sku AND s.count_date = l.counted_on
ORDER BY l.sku, l.stock_dayAnother way
WITH RECURSIVE calendar (stock_day) AS (
SELECT CAST('2025-05-01' AS DATE) UNION ALL SELECT DATE_ADD(stock_day, INTERVAL 1 DAY) FROM calendar WHERE stock_day < '2025-05-07'
), candidates AS (
SELECT s.sku, c.stock_day, s.counted_qty, s.count_date,
ROW_NUMBER() OVER (PARTITION BY s.sku, c.stock_day ORDER BY s.count_date DESC) AS rn
FROM calendar c JOIN StockCount s ON s.count_date <= c.stock_day
)
SELECT sku, stock_day, counted_qty AS qty, count_date AS counted_on FROM candidates WHERE rn = 1 ORDER BY sku, stock_day← Supplier On-Time-In-Full Rate by Order Quarter · Products Most Often Bought in the Same Basket →