Drill 10 of 12 in Logistics questions
0

Interview Question Bank · Signed running SUM · PARTITION BY · tie-breaking the frame

10. Running inventory balance

HardLogisticsQuestion #166

Concept: Signed running SUM · PARTITION BY · tie-breaking the frameshow

A running balance is SUM(signed quantity) OVER (PARTITION BY location ORDER BY time). With ORDER BY, the default frame is RANGE ... CURRENT ROW, which includes every row that ties on the sort key, so two moves at the same timestamp both show the balance after both. Add a unique tie-breaker (ORDER BY time, id) or use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW so each row shows its own balance.

Example (different data):

SELECT till, logged_at, kind, amount,
  SUM(CASE WHEN kind = 'in' THEN amount ELSE -amount END)
    OVER (PARTITION BY till ORDER BY logged_at, id) AS cash
FROM till_movements;

Your task

Larkspur Freight's auditors are checking how much Packing tape (SKU-400) sat in each warehouse through the first week of April 2025. Both warehouses started the month with none, and moves that share a timestamp were applied in id order.

For SKU-400, return warehouse (code), moved_at, kind, qty and balance (stock on hand in that warehouse right after the move: receipts add qty, picks subtract it). Sort by warehouse, then moved_at, then id.

Starting Postgres in your browser…

Checking your access…