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
| Column | Type |
|---|---|
| receipt_id | int |
| sku | varchar |
| received_on | date |
| qty | int |
| unit_cost | decimal |
Primary key: receipt_id.
One row per goods receipt (a FIFO layer); unit_cost in rupees per unit.
Table: UnitsIssued
| Column | Type |
|---|---|
| sku | varchar |
| qty_issued | int |
Primary key: sku.
Total units sold or transferred out of each SKU so far.
Examples
Example 1
StockReceipt
| receipt_id | sku | received_on | qty | unit_cost |
|---|---|---|---|---|
| 1 | GRO-1001 | 2025-01-03 | 50 | 500 |
| 2 | GRO-1001 | 2025-01-17 | 40 | 520 |
| 3 | GRO-1001 | 2025-02-02 | 30 | 515.5 |
| 4 | DAI-2002 | 2025-01-10 | 100 | 62 |
| 5 | DAI-2002 | 2025-01-10 | 60 | 64.25 |
| 6 | SNK-6001 | 2025-01-05 | 200 | 19.8 |
| 7 | BEV-3001 | 2025-01-21 | 25 | 98 |
UnitsIssued
| sku | qty_issued |
|---|---|
| GRO-1001 | 70 |
| DAI-2002 | 100 |
| SNK-6001 | 200 |
Output
| sku | units_left | fifo_value |
|---|---|---|
| BEV-3001 | 25 | 2450 |
| DAI-2002 | 60 | 3855 |
| GRO-1001 | 50 | 25865 |
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 skuAnother 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 →