Net Stock Change per SKU From the Movement Log

Easy
378.

Net Stock Change per SKU From the Movement Log

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

ColumnTypeValues
move_idint
skuvarchar
move_typeenumGRN, RETURN, PICK, DAMAGE, TRANSFER_OUT
qtyint
moved_atdatetime

Primary key: move_id.

qty is always positive; the direction comes from move_type.

Example

The rows must come back in the order shown.

StockMovement

move_idskumove_typeqtymoved_at
1GRO-1001GRN1002025-04-01 09:10:00
2GRO-1001PICK352025-04-01 13:45:00
3GRO-1001RETURN22025-04-02 11:00:00
4DAI-2002GRN402025-04-01 07:30:00
5DAI-2002DAMAGE62025-04-02 18:20:00
6DAI-2002PICK382025-04-03 12:05:00
7SNK-6001TRANSFER_OUT242025-04-03 16:40:00

Output

skuunits_inunits_outnet_change
DAI-20024044-4
GRO-10011023567
SNK-6001024-24
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.

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

ColumnType
move_idint
skuvarchar
move_typeenum(GRN, RETURN, PICK, DAMAGE, TRANSFER_OUT)
qtyint
moved_atdatetime

Primary key: move_id.

qty is always positive; the direction comes from move_type.

Examples

Example 1

StockMovement

move_idskumove_typeqtymoved_at
1GRO-1001GRN1002025-04-01 09:10:00
2GRO-1001PICK352025-04-01 13:45:00
3GRO-1001RETURN22025-04-02 11:00:00
4DAI-2002GRN402025-04-01 07:30:00
5DAI-2002DAMAGE62025-04-02 18:20:00
6DAI-2002PICK382025-04-03 12:05:00
7SNK-6001TRANSFER_OUT242025-04-03 16:40:00

Output

skuunits_inunits_outnet_change
DAI-20024044-4
GRO-10011023567
SNK-6001024-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 sku

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