Queries · q189 · Director of Fulfilment Operations · week 1, night 2
“When stock is reserved for an order, does the reservation get released normally (order fulfilled/cancelled in time), or do we see reservations left stuck past their expiry — quietly overstating what's actually available to sell?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Auditors and merchants question inventory accuracy. How often do counts, adjustments and reservations disagree with recorded stock, where, and why?
The manager, at the start of week 1
Subject: Inventory accuracy — what I need for Friday's memo
Fatima,
Tobias and I need to give the auditors (and our bigger merchants) a credible answer on how trustworthy our stock numbers are, where it breaks down, and whether it's getting better or worse. This feeds the joint audit response and will likely shape where we invest in warehouse process fixes next quarter — so I'd rather have three sharp answers than ten partial ones. Please use your nightly slots on these, in rough priority order:
If you can only get to three this week, do 1, 2 and 3 — those are the ones Tobias and I will lean on most heavily in the audit conversation. Happy to talk through trade-offs before you lock in Monday night's requests.
Thanks, Tomasz
Subject: Finance questions for the inventory accuracy review
Hi Fatima,
A few things from the finance side as you dig into this with Tomasz:
Keep it high level for now — happy to go deeper once you have an initial read.
Thanks, Tobias
The analyst · week 1, night 2, request 3 of 3
Reading night 1 critically, two things jump out before I trust the headline numbers, plus one new question worth opening.
(1) Suspicious invariant: in report 1, abs_units_variance equals cycle_count_events exactly in every single month (e.g. Jan 764=764), and avg_abs_units_per_event is 1.0 in all 12 months. Report 3 shows the same identity for every one of 105 categories (Bikes 102=102, Tops 1086=1086, etc). That means every recorded cycle-count discrepancy is off by exactly one unit, with zero variation — never 2, never 5. That's either a real and important finding (our "size of discrepancy" story is really a "frequency of single-unit misses" story, which changes the materiality narrative for Tobias) or a sign that stock_movements.quantity isn't what I think it is for this kind. I want to verify this against other movement kinds before I put it in the memo as fact.
(2) Report 2's top 50 rows (ranked by value) are all location_kind='pick' — not one bulk/overflow/other row appears. That's a strong concentration signal for Tomasz's "particular kinds of storage locations" question, but I need a company-wide total by location_kind (not filtered to top-50-by-warehouse) to confirm it isn't just pick locations dominating the value ranking while other kinds still carry meaningful event counts further down the list.
(3) Tomasz's Q4 (reservation reliability) hasn't been touched yet. With two slots going to validate/sharpen findings 1 and 2 above, I have a free slot to open Q4 tonight rather than wait, since nights 3-4 will still be needed for deeper drill-down and the trend view (Q5).
Tonight's three requests: (A) movement-magnitude distribution by kind, to sanity-check the ±1 pattern and see if 'adjustment'-kind movements (a different, less formal correction path) carry larger swings; (B) a clean location-kind-only rollup (not crossed with warehouse) so kinds with little/no cycle-count activity are visible as explicit zero/low rows; (C) a first read on stock reservation reliability — how often holds are released normally vs. left stuck past their expiry, which overstates sellable availability.
The engineer · 1 dry run on the empty schema, then the submission
SELECT category AS outcome_category, count(*) AS reservation_count, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_total, sum(quantity) AS total_quantity_units, round(avg(quantity * 1.0), 1) AS avg_quantity_per_reservation, round(avg(duration_days), 1) AS avg_hold_duration_days FROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN EXTRACT(EPOCH FROM (released_at - created_at)) / 86400.0 ELSE EXTRACT(EPOCH FROM (TIMESTAMP '2025-12-31 00:00:00' - created_at)) / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00' ) t GROUP BY category ORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report has one row per outcome category (4 rows, funnel-ordered: released_on_time → released_late → stuck_past_expiry → still_open_active) with reservation count, share of total, total and average quantity, and average hold duration in days, computed over 2025 reservations with 2025-12-31 as the fixed reference date.”
As Fatima Zahra saw it, from the small dataset the agents worked on
outcome_category | reservation_count | pct_of_total | total_quantity_units | avg_quantity_per_reservation | avg_hold_duration_days ------------------+-------------------+--------------+----------------------+------------------------------+----------------------- released_on_time | 40453 | 99.3 | 86762 | 2.1 | 1.1 released_late | 139 | 0.3 | 156 | 1.1 | 9.7 still_open_active | 128 | 0.3 | 206 | 1.6 | -0.5 (3 rows)
Changed only where the benchmark needs the same result on every system
SELECT category AS outcome_category, count(*) AS reservation_count, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_total, 100.0 * count(*) / sum(count(*)) OVER () AS pct_of_total, sum(quantity) AS total_quantity_units, round(avg(quantity * 1.0), 1) AS avg_quantity_per_reservation, round(avg(duration_days), 1) AS avg_hold_duration_days avg(quantity * 1.0) AS avg_quantity_per_reservation, avg(duration_days) AS avg_hold_duration_daysFROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN EXTRACT(EPOCH FROM (released_at - created_at)) / 86400.0 ELSE EXTRACT(EPOCH FROM (TIMESTAMP '2025-12-31 00:00:00' - created_at)) / 86400.0 WHEN released_at IS NOT NULL THEN {{seconds_between(created_at, released_at)}} / 86400.0 ELSE {{seconds_between(created_at, TIMESTAMP '2025-12-31 00:00:00')}} / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00') tGROUP BY categoryORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END END NULLS LAST
SELECT category AS outcome_category, count(*) AS reservation_count, 100.0 * count(*) / sum(count(*)) OVER () AS pct_of_total, sum(quantity) AS total_quantity_units, avg(quantity * 1.0) AS avg_quantity_per_reservation, avg(duration_days) AS avg_hold_duration_days FROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (released_at - created_at)) AS NUMERIC(18,6)) / 86400.0 ELSE CAST(EXTRACT(EPOCH FROM (TIMESTAMP '2025-12-31 00:00:00' - created_at)) AS NUMERIC(18,6)) / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00' ) t GROUP BY category ORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END NULLS LAST
SELECT category AS outcome_category, count(*) AS reservation_count, 100.0 * count(*) / sum(count(*)) OVER () AS pct_of_total, sum(quantity) AS total_quantity_units, avg(quantity * 1.0) AS avg_quantity_per_reservation, avg(duration_days) AS avg_hold_duration_days FROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, released_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 ELSE CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, TIMESTAMP '2025-12-31 00:00:00'), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00' ) t GROUP BY category ORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Fatima Zahra (analyst for the Director of Fulfilment Operations): Title: Stock reservation outcomes: are soft holds reliable? Question: When stock is reserved for an order, does the reservation get released normally (order fulfilled/cancelled in time), or do we see reservations left stuck past their expiry — quietly overstating what's actually available to sell? Grain: reservation outcome category Columns: - outcome_category - reservation_count - pct_of_total (rounded 1dp) - total_quantity_units (sum of quantity across reservations in this category) - avg_quantity_per_reservation (rounded 1dp) - avg_hold_duration_days (for released rows: released_at minus created_at; for still-open rows: 2025-12-31 minus created_at; rounded 1dp) Details: Source: stock_reservations where created_at is between 2025-01-01 and 2025-12-31. Define four mutually exclusive outcome categories, evaluated with 2025-12-31 as the fixed 'as of' reference date (the last date the warehouse has complete data for — do not use CURRENT_DATE): (1) 'released_on_time' = released_at is not null and (expires_at is null or released_at <= expires_at); (2) 'released_late' = released_at is not null and expires_at is not null and released_at > expires_at; (3) 'stuck_past_expiry' = released_at is null and expires_at is not null and expires_at < 2025-12-31 (an expired hold that was never released — the 'zombie' case that overstates unavailable stock); (4) 'still_open_active' = released_at is null and (expires_at is null or expires_at >= 2025-12-31). Order rows in that fixed category order (released_on_time, released_late, stuck_past_expiry, still_open_active) rather than alphabetically, so the report reads as a funnel. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT category AS outcome_category, count(*) AS reservation_count, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_total, sum(quantity) AS total_quantity_units, round(avg(quantity * 1.0), 1) AS avg_quantity_per_reservation, round(avg(duration_days), 1) AS avg_hold_duration_days FROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN EXTRACT(EPOCH FROM (released_at - created_at)) / 86400.0 ELSE EXTRACT(EPOCH FROM (TIMESTAMP '2025-12-31 00:00:00' - created_at)) / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00' ) t GROUP BY category ORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: outcome_category, reservation_count, pct_of_total, total_quantity_units, avg_quantity_per_reservation, avg_hold_duration_days
submitSELECT category AS outcome_category, count(*) AS reservation_count, round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_total, sum(quantity) AS total_quantity_units, round(avg(quantity * 1.0), 1) AS avg_quantity_per_reservation, round(avg(duration_days), 1) AS avg_hold_duration_days FROM ( SELECT quantity, CASE WHEN released_at IS NOT NULL AND (expires_at IS NULL OR released_at <= expires_at) THEN 'released_on_time' WHEN released_at IS NOT NULL AND expires_at IS NOT NULL AND released_at > expires_at THEN 'released_late' WHEN released_at IS NULL AND expires_at IS NOT NULL AND expires_at < TIMESTAMP '2025-12-31 00:00:00' THEN 'stuck_past_expiry' ELSE 'still_open_active' END AS category, CASE WHEN released_at IS NOT NULL THEN EXTRACT(EPOCH FROM (released_at - created_at)) / 86400.0 ELSE EXTRACT(EPOCH FROM (TIMESTAMP '2025-12-31 00:00:00' - created_at)) / 86400.0 END AS duration_days FROM stock_reservations WHERE created_at >= TIMESTAMP '2025-01-01 00:00:00' AND created_at < TIMESTAMP '2026-01-01 00:00:00' ) t GROUP BY category ORDER BY CASE category WHEN 'released_on_time' THEN 1 WHEN 'released_late' THEN 2 WHEN 'stuck_past_expiry' THEN 3 WHEN 'still_open_active' THEN 4 END
Submitted to tonight's batch. Your work on this request is done.