Net Stock Change per SKU From the Movement Log — SQL Problem
The warehouse logs every stock movement with a positive qty and a move_type.
- Difficulty: Easy
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #378
Problem statement
The warehouse logs every stock movement with a positive qty and a move_type. Goods receipts (GRN) and customer returns (RETURN) add stock; sales picks (PICK), damaged write-offs (DAMAGE) and transfers out (TRANSFER_OUT) remove it.
Return one row per SKU that has movements, with the columns sku, units_in, units_out and net_change (units_in minus units_out; negative when more left than came in). Order the rows by sku.
Tables
Table: StockMovement
| Column | Type |
|---|---|
| move_id | int |
| sku | varchar |
| move_type | enum(GRN, RETURN, PICK, DAMAGE, TRANSFER_OUT) |
| qty | int |
| moved_at | datetime |
Primary key: move_id.
qty is always positive; the direction comes from move_type.
Examples
Example 1
StockMovement
| move_id | sku | move_type | qty | moved_at |
|---|---|---|---|---|
| 1 | GRO-1001 | GRN | 100 | 2025-04-01 09:10:00 |
| 2 | GRO-1001 | PICK | 35 | 2025-04-01 13:45:00 |
| 3 | GRO-1001 | RETURN | 2 | 2025-04-02 11:00:00 |
| 4 | DAI-2002 | GRN | 40 | 2025-04-01 07:30:00 |
| 5 | DAI-2002 | DAMAGE | 6 | 2025-04-02 18:20:00 |
| 6 | DAI-2002 | PICK | 38 | 2025-04-03 12:05:00 |
| 7 | SNK-6001 | TRANSFER_OUT | 24 | 2025-04-03 16:40:00 |
Output
| sku | units_in | units_out | net_change |
|---|---|---|---|
| DAI-2002 | 40 | 44 | -4 |
| GRO-1001 | 102 | 35 | 67 |
| SNK-6001 | 0 | 24 | -24 |
How to solve Net Stock Change per SKU From the Movement Log
The log stores quantities without a direction, so the query supplies the direction itself. Conditional aggregation does it in one pass: inside SUM, a CASE returns qty for the movement types that belong to the column and 0 for the rest. One sum collects the inbound types, another the outbound ones, and a third adds qty or -qty to get the net change directly.
Every SKU in the answer has at least one movement, so no sum is NULL — a SKU with only outbound moves gets units_in 0, because the ELSE 0 turns its non-matching rows into zeros rather than leaving the sum empty. A negative net_change is a real situation (stock that was on the shelf before the log began left this week).
The alternative computes the two totals with IF in a derived table and subtracts outside; it is the same single scan of the table. A UNION ALL of signed rows would also work but reads the table twice.
Reference solution (MySQL)
SELECT sku,
SUM(CASE WHEN move_type IN ('GRN', 'RETURN') THEN qty ELSE 0 END) AS units_in,
SUM(CASE WHEN move_type IN ('PICK', 'DAMAGE', 'TRANSFER_OUT') THEN qty ELSE 0 END) AS units_out,
SUM(CASE WHEN move_type IN ('GRN', 'RETURN') THEN qty ELSE -qty END) AS net_change
FROM StockMovement
GROUP BY sku
ORDER BY skuAnother way
SELECT sku, units_in, units_out, units_in - units_out AS net_change FROM (SELECT sku, SUM(IF(move_type = 'GRN' OR move_type = 'RETURN', qty, 0)) AS units_in, SUM(IF(move_type = 'GRN' OR move_type = 'RETURN', 0, qty)) AS units_out FROM StockMovement GROUP BY sku) t ORDER BY sku← Open Purchase Orders Past Their Promised Date · March POS Bills by Day of the Week →