Daily Stock Carried Forward From the Last Count

Hard
396.

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. 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

ColumnType
skuvarchar
count_datedate
counted_qtyint

Primary key (together): sku, count_date.

One row per physical count of a SKU (at most one a day).

Example

The rows must come back in the order shown.

StockCount

skucount_datecounted_qty
GRO-10012025-04-2834
GRO-10012025-05-0318
GRO-10012025-05-0652
DAI-20022025-05-0511
DAI-20022025-05-0940
SNK-60012025-05-1275

Output

skustock_dayqtycounted_on
DAI-20022025-05-05112025-05-05
DAI-20022025-05-06112025-05-05
DAI-20022025-05-07112025-05-05
GRO-10012025-05-01342025-04-28
GRO-10012025-05-02342025-04-28
GRO-10012025-05-03182025-05-03
GRO-10012025-05-04182025-05-03
GRO-10012025-05-05182025-05-03
GRO-10012025-05-06522025-05-06
GRO-10012025-05-07522025-05-06
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.

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

ColumnType
skuvarchar
count_datedate
counted_qtyint

Primary key: sku, count_date.

One row per physical count of a SKU (at most one a day).

Examples

Example 1

StockCount

skucount_datecounted_qty
GRO-10012025-04-2834
GRO-10012025-05-0318
GRO-10012025-05-0652
DAI-20022025-05-0511
DAI-20022025-05-0940
SNK-60012025-05-1275

Output

skustock_dayqtycounted_on
DAI-20022025-05-05112025-05-05
DAI-20022025-05-06112025-05-05
DAI-20022025-05-07112025-05-05
GRO-10012025-05-01342025-04-28
GRO-10012025-05-02342025-04-28
GRO-10012025-05-03182025-05-03
GRO-10012025-05-04182025-05-03
GRO-10012025-05-05182025-05-03
GRO-10012025-05-06522025-05-06
GRO-10012025-05-07522025-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_day

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