SQL Inventory Interview Questions: Stock Movements, Stockouts, and Negative Balances

SQL inventory interview questions become difficult when “current stock” turns into “how long were we unable to fulfill an order?” A final balance cannot answer that. You need an opening quantity, ordered stock movements, a definition of available inventory, and the interval during which each reconstructed balance applies.

The reliable approach is to build the complete SKU-location timeline first, calculate its running balance, find the next change, and only then classify stockout intervals. Preserve negative balances for diagnosis. Filtering out healthy inventory rows too early can erase the exact event that ended a stockout.

The exercise below uses original data and executable SQL; it is not a claimed employer test or a design for preventing concurrent overselling.

Which inventory quantity are you reconstructing?

On-hand inventory is the quantity physically recorded at a location. Available inventory is the quantity eligible for new sales after the system's reservations and other exclusions. A stockout occurs when the chosen fulfillment quantity cannot satisfy the declared demand rule; zero on-hand is only one possible definition.

Shopify, for example, distinguishes on-hand, available, committed, unavailable, and incoming inventory states. Those documented categories demonstrate why “inventory” is not one universal number. Our SQL uses a simpler original model: signed movements update a recorded on-hand balance, and a unit-demand stockout means that balance is less than or equal to zero. Shopify inventory states

Partition by both SKU and location, and keep quantities in one declared unit. Five cases and five individual items cannot be added without a conversion rule. If lots, ownership, or quality states matter to fulfillment, those dimensions also belong in the model rather than being discarded during aggregation.

Establish an opening anchor and a closed reporting window

Our reporting interval is August 1, 2026, from 08:00 through, but not including, 18:00 UTC. Opening quantities describe state immediately before 08:00. Movements at 08:00 belong to this window; earlier movements are already incorporated into the opening and must not be counted again.

The opening table contains A/East with five units, A/West with two, and B/East with zero. Each pair has exactly one opening row. An absent opening is an unknown anchor, not permission to assume zero. Verify key uniqueness before joining any movements to snapshots.

The report treats each movement as effective at its recorded timestamp and uses a ledger sequence to order ties. Timestamps are normalized UTC values. This is a reporting contract, not a claim that every warehouse records physical operations at sufficient precision to reconstruct their real-world order.

Load the complete inventory fixture

Run the following in an empty DuckDB session. The six movement records have unique event IDs and non-NULL quantities. Receipts are positive; shipments are negative. B/East intentionally has no movement records.

CREATE TABLE opening (
 sku VARCHAR, location VARCHAR, qty INTEGER
);
INSERT INTO opening VALUES
 ('A','East',5),('A','West',2),('B','East',0);
CREATE TABLE movements (
 event_id INTEGER, sku VARCHAR, location VARCHAR,
 effective_at TIMESTAMP, seq INTEGER, delta INTEGER
);
INSERT INTO movements VALUES
 (1,'A','East','2026-08-01 09:00',1,-5),
 (2,'A','East','2026-08-01 10:00',2,4),
 (3,'A','East','2026-08-01 10:00',3,-6),
 (4,'A','East','2026-08-01 12:00',4,3),
 (5,'A','East','2026-08-01 16:00',5,-1),
 (6,'A','West','2026-08-01 11:00',1,2);

Before writing a window function, calculate A/East by hand. Its balance is five at opening, zero at 09:00, four after the receipt at 10:00, negative two after the same-time shipment, one at noon, and zero at 16:00. A/West finishes with four; B/East stays zero throughout.

That negative two is deliberate. It might represent a genuine operational exception or a source-data problem, depending on the underlying system. The fixture asks you to expose and explain it, not silently change its value until the report looks plausible.

Reconstruct every balance, including opening-only stock

Create a synthetic opening point for every pair and union in the period movements. The anchor uses sequence minus one; this fixture's movement sequences are positive, so same-time movements follow the anchor. In a broader schema, use an explicit anchor-priority field instead of relying on an undocumented numeric convention.

CREATE TEMP VIEW balances AS
WITH points AS (
 SELECT sku, location, TIMESTAMP '2026-08-01 08:00' AS ts,
        -1 AS seq, qty AS delta
 FROM opening
 UNION ALL
 SELECT sku, location, effective_at, seq, delta
 FROM movements
 WHERE effective_at >= TIMESTAMP '2026-08-01 08:00'
   AND effective_at < TIMESTAMP '2026-08-01 18:00'
)
SELECT sku, location, ts, seq,
 SUM(delta) OVER (
  PARTITION BY sku, location ORDER BY ts, seq
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS on_hand
FROM points;

SELECT * FROM balances ORDER BY sku, location, ts, seq;

The explicit ROWS frame accumulates ordered entries through the current row. PostgreSQL documents how frame selection controls window aggregates and how timestamp peers can behave under the default frame. A complete ordering key is still necessary; ROWS alone does not make ambiguous ties meaningful. PostgreSQL window-frame syntax

The opening union is what keeps B/East visible. Starting exclusively from movement rows would omit it and report no evidence of its ten-hour stockout. Conversely, a movement for a pair absent from the opening table must fail an upstream completeness check; the union itself does not validate that relationship.

Turn balances into stockout durations

The balance after a point applies until the next point. Use LEAD over the complete timeline, with the reporting end as the final boundary. PostgreSQL's documented LEAD semantics return a following row's value, with an optional default when no following row exists. PostgreSQL window functions

WITH spans AS (
 SELECT *, LEAD(ts, 1, TIMESTAMP '2026-08-01 18:00')
   OVER (PARTITION BY sku, location ORDER BY ts, seq) AS next_ts
 FROM balances
)
SELECT sku, location,
 SUM(CASE WHEN on_hand <= 0
          THEN EXTRACT(EPOCH FROM (next_ts - ts))
          ELSE 0 END) / 3600.0 AS stockout_hours
FROM spans
GROUP BY sku, location
ORDER BY sku, location;

The results are A/East = five hours, A/West = zero hours, and B/East = ten hours. A/East contributes one hour from 09:00 to 10:00, two hours from 10:00 to noon, and two hours from 16:00 to 18:00. The intermediate positive balance at 10:00 has zero elapsed duration.

Subtracting timestamps produces intervals; extracting epoch seconds makes the unit conversion explicit. PostgreSQL documents interval extraction and date/time arithmetic. For business-hour stockouts, intersect these spans with the store's open schedule before totaling them; elapsed hours and opening hours are different metrics. PostgreSQL date/time functions

Why filtering early produces the wrong recovery time

Suppose you filter balances to on_hand <= 0 before applying LEAD. A/East's noon recovery row disappears. The negative balance at 10:00 then appears to continue until the next low-stock row at 16:00, even though inventory was positive from noon to 16:00.

The incorrect query reports nine stockout hours for A/East rather than five. Its output looks reasonable because all durations are nonnegative and remain inside the reporting day. The defect is a missing state transition, not a syntax error or an impossible final number.

Keep all state changes through interval construction, then filter or conditionally aggregate the resulting spans. This pattern also applies to machine downtime and account suspension, but the inventory exercise adds opening stock, location boundaries, and signed quantity reconciliation that those neighboring tasks do not share.

Ties and negative balances need different decisions

At 10:00, a receipt of four and shipment of six share a timestamp. The declared ledger sequence yields intermediate balances four and negative two. Reversing their order gives negative six and negative two. The final stock and elapsed stockout duration stay the same here, while the minimum recorded balance changes.

If the source provides no authoritative ordering, do not invent physical causality from a random event ID. For duration at timestamp resolution, aggregate all same-time movements before reconstructing spans. For an event-by-event audit, request a sequence or report that intermediate states are ambiguous.

Do not clamp negative balances before later additions. Replacing negative two with zero before a receipt of three would produce three instead of the correct one. A display might separately show zero sellable units, but the underlying ledger should preserve the discrepancy and its provenance.

Investigate duplicate shipment application, missing receipts, delayed ingestion, returns recorded at the wrong location, or genuine overselling. A negative ledger does not identify which cause occurred. Compare source event IDs, effective timestamps, ingestion timestamps, and a physical or authoritative snapshot before proposing a correction.

Reversals and backfills must preserve reconciliation

An explicit reversal is another signed movement linked to the original event. A shipment of six followed by a reversal of six cancels in the ending total, but the earlier negative state may still belong in an as-recorded timeline. Restating the original event instead answers a different historical question.

Late arrivals can change several later balances and durations. If a receipt effective at 09:30 arrives tomorrow, recomputing only tomorrow's partition will not repair today's timeline. Rebuild the affected SKU-location sequence from a valid prior anchor and retain a data-as-of timestamp for reproducibility.

For warehouse transfers, pair the source and destination movements through a transfer identifier and account for any in-transit state. A negative source movement and delayed destination receipt need not imply lost global stock. Summing locations can also hide a local stockout, so keep location-level availability separate from total ownership.

Prove the query with invariants and mutations

We executed the three SQL blocks in DuckDB 1.5.2 and checked the balances and five/zero/ten-hour totals. The intentionally wrong early-filter query returns nine hours for A/East. Reordering physical input does not change the correct output because the window has an explicit order.

For every pair, ending balance must equal opening plus eligible signed movements. Each adjacent balance difference must equal the corresponding delta. Every measured span must stay inside the ten-hour reporting window, and stockout duration cannot exceed that window for one pair.

Test movement exactly at 18:00: it should not change this report. Test a SKU with no movement and positive opening, then one with zero opening. Validate duplicate event IDs before summation; two byte-identical deliveries are still two rows unless a deduplication stage removes them.

The running-balance SQL article develops ledger ordering further. Here the additional proof obligation is that the reconstruction preserves recovery boundaries and opening-only stockouts, not merely that the final sum is correct.

Practice inventory state and temporal SQL

The following records are related practice, not a claim that every employer asks inventory SQL. The inventory-design and log-processing prompts exercise transferable data contracts rather than the exact query above.

Question	Practice focus
Solve window-function SQL without joins	Ordered state.
Write SQL Data Transformation Queries	Cumulative totals.
Design Inventory Availability and Order Placement	Stock versus holds.
Deduplicate and Order Batch and Streaming Logs	Clean event input.
Maintain Fractional Stock Inventory Across Orders	Exact quantity units.

For your next SQL inventory interview answer, describe the state timeline before the SUM. Practice Solve window-function SQL without joins, then explain which recovery row would disappear if you filtered too early.

Sources and Further Reading

• Shopify inventory states
• PostgreSQL window functions
• PostgreSQL window expressions and frames
• PostgreSQL date/time arithmetic

Sources checked September 21, 2026. Dataset and checks are original. Local execution validates this fixture, not warehouse inventory accuracy or a PostgreSQL performance claim.