FIFO Valuation of the Stock Left on Hand

Hard
391.

FIFO Valuation of the Stock Left on Hand

The chain values inventory first in, first out: units issued from a SKU are taken from its oldest receipt first (earliest received_on, then lowest receipt_id), so what is left on hand is the newest stock, valued at the cost of the receipts it came from. UnitsIssued holds each SKU's total issued since the receipts began; a SKU with no row there has issued nothing. Issues never exceed receipts.

For every SKU with units left, return sku, units_left and fifo_value (the sum over its receipts of the units still left from that receipt × its unit_cost, rounded to 2 decimal places). SKUs with nothing left are not returned. Order the rows by sku.

Tables

Table: StockReceipt

ColumnType
receipt_idint
skuvarchar
received_ondate
qtyint
unit_costdecimal

Primary key: receipt_id.

One row per goods receipt (a FIFO layer); unit_cost in rupees per unit.

Table: UnitsIssued

ColumnType
skuvarchar
qty_issuedint

Primary key: sku.

Total units sold or transferred out of each SKU so far.

Example

The rows must come back in the order shown.

StockReceipt

receipt_idskureceived_onqtyunit_cost
1GRO-10012025-01-0350500
2GRO-10012025-01-1740520
3GRO-10012025-02-0230515.5
4DAI-20022025-01-1010062
5DAI-20022025-01-106064.25
6SNK-60012025-01-0520019.8
7BEV-30012025-01-212598

UnitsIssued

skuqty_issued
GRO-100170
DAI-2002100
SNK-6001200

Output

skuunits_leftfifo_value
BEV-3001252450
DAI-2002603855
GRO-10015025865
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.

FIFO Valuation of the Stock Left on Hand — SQL Problem

The chain values inventory first in, first out: units issued from a SKU are taken from its oldest receipt first (earliest received_on, then lowest…

  • Difficulty: Hard
  • Topics: Window Functions, Aggregation, Joins
  • Dialect: MySQL
  • Problem: #391

Problem statement

The chain values inventory first in, first out: units issued from a SKU are taken from its oldest receipt first (earliest received_on, then lowest receipt_id), so what is left on hand is the newest stock, valued at the cost of the receipts it came from. UnitsIssued holds each SKU's total issued since the receipts began; a SKU with no row there has issued nothing. Issues never exceed receipts.

For every SKU with units left, return sku, units_left and fifo_value (the sum over its receipts of the units still left from that receipt × its unit_cost, rounded to 2 decimal places). SKUs with nothing left are not returned. Order the rows by sku.

Tables

Table: StockReceipt

ColumnType
receipt_idint
skuvarchar
received_ondate
qtyint
unit_costdecimal

Primary key: receipt_id.

One row per goods receipt (a FIFO layer); unit_cost in rupees per unit.

Table: UnitsIssued

ColumnType
skuvarchar
qty_issuedint

Primary key: sku.

Total units sold or transferred out of each SKU so far.

Examples

Example 1

StockReceipt

receipt_idskureceived_onqtyunit_cost
1GRO-10012025-01-0350500
2GRO-10012025-01-1740520
3GRO-10012025-02-0230515.5
4DAI-20022025-01-1010062
5DAI-20022025-01-106064.25
6SNK-60012025-01-0520019.8
7BEV-30012025-01-212598

UnitsIssued

skuqty_issued
GRO-100170
DAI-2002100
SNK-6001200

Output

skuunits_leftfifo_value
BEV-3001252450
DAI-2002603855
GRO-10015025865

How to solve FIFO Valuation of the Stock Left on Hand

FIFO valuation is a running-total problem. Sort each SKU's receipts by received_on, receipt_id and compute cum_qty, the units received up to and including each layer (SUM(qty) OVER (PARTITION BY sku ORDER BY … ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — the explicit ROWS frame and the receipt_id tie-break make two receipts on the same day consume in a fixed order).

Issues eat the layers from the oldest, so with issued units gone each layer is in one of three states. If cum_qty <= issued, the layer is fully consumed. If the units before it (cum_qty - qty) already cover the issues, it is untouched and all qty remain. Otherwise it is the layer the issues stopped in, and cum_qty - issued units remain. That is the same as clamping cum_qty - issued between 0 and qty, which is how the alternative writes it with GREATEST/LEAST.

The value is the sum of remaining units × their layer's cost, rounded to 2 places; SKUs whose layers are all consumed are dropped in HAVING. A SKU missing from UnitsIssued gets 0 through the LEFT JOIN and COALESCE. The window version sorts each SKU once (O(n log n)); the correlated running total is quadratic in receipts per SKU but shows the definition directly.

Reference solution (MySQL)

WITH layers AS (
  SELECT r.sku, r.qty, r.unit_cost,
         SUM(r.qty) OVER (PARTITION BY r.sku ORDER BY r.received_on, r.receipt_id
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_qty,
         COALESCE(u.qty_issued, 0) AS issued
  FROM StockReceipt r
  LEFT JOIN UnitsIssued u ON u.sku = r.sku
), remaining AS (
  SELECT sku, unit_cost,
         CASE WHEN cum_qty <= issued THEN 0
              WHEN cum_qty - qty >= issued THEN qty
              ELSE cum_qty - issued END AS units_left
  FROM layers
)
SELECT sku, SUM(units_left) AS units_left,
       ROUND(SUM(units_left * unit_cost), 2) AS fifo_value
FROM remaining
GROUP BY sku
HAVING SUM(units_left) > 0
ORDER BY sku

Another way

SELECT sku, SUM(left_qty) AS units_left, ROUND(SUM(left_qty * unit_cost), 2) AS fifo_value FROM (
  SELECT r.sku, r.unit_cost,
         GREATEST(0, LEAST(r.qty,
           (SELECT SUM(r2.qty) FROM StockReceipt r2 WHERE r2.sku = r.sku
              AND (r2.received_on < r.received_on OR (r2.received_on = r.received_on AND r2.receipt_id <= r.receipt_id)))
           - IFNULL((SELECT u.qty_issued FROM UnitsIssued u WHERE u.sku = r.sku), 0))) AS left_qty
  FROM StockReceipt r) t
GROUP BY sku
HAVING SUM(left_qty) > 0
ORDER BY sku

← Stores Beating Their City's Average in Q1 · Longest Consecutive Stock-Out Run per SKU →