Queries · q192 · Director of Fulfilment Operations · week 1, night 3

Cross-check: stock_adjustments table vs stock_movements adjustment-kind ledger, 2025

“Does the stock_adjustments table (the human-entered correction record with a required reason) cover the same volume as the stock_movements kind='adjustment' ledger, or is one capturing materially more/less than the other — i.e., which is the authoritative source for 'a physical count or review found a quantity discrepancy'?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

1. A business event lands on the Director of Fulfilment Operations's desk

   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?

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Tomasz Nowak writes to Fatima Zahra

   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:

   1. How big is the gap between what we think we have and what's actually there? Across our warehouses, how often do physical counts come back different from the stock we had on record, and how large are those differences in units (and ideally value)? I want a sense of scale before anything else.
   2. Where is it worst? Does the mismatch concentrate in particular warehouses, or particular kinds of storage locations (e.g. bulk shelving vs. pick bins)? If a handful of sites or location types account for most of the problem, that tells us where to send help first.
   3. Is it a product or a process problem? Are the discrepancies spread evenly across products, or concentrated in certain categories, high-velocity items, or items we handle in lots/serials? This will tell Tobias and me whether this is a handling issue or a tracking-method issue.
   4. Are our "soft holds" on stock reliable? When we reserve stock for an order, does that reservation actually get fulfilled as expected, or do we see a lot of stuck/expired holds that quietly overstate what's available to sell? This matters for whether we're overpromising stock to customers.
   5. Is this getting better or worse over time? A simple trend over the last several months would tell us whether recent process changes are helping or whether the problem is growing — useful context for the auditors either way.

   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

   Show the whole note
   A note from the Chief Financial Officer, Tobias Lindqvist

   Subject: Finance questions for the inventory accuracy review

   Hi Fatima,

   A few things from the finance side as you dig into this with Tomasz:

   1. Do stock discrepancies cluster around particular merchants or warehouses in a way that could point to a write-off or valuation exposure we need to flag in the annual accounts?
   2. When counts don't match recorded stock, is it skewed more toward unexplained losses (shrinkage) versus unexplained gains — and has that pattern shifted over the year? That matters for how we size a shrinkage provision.
   3. Are the mismatches concentrated in slower-moving or higher-value goods, or spread evenly? That shapes how material this is for the merchants we'd flag to auditors.

   Keep it high level for now — happy to go deeper once you have an initial read.

   Thanks, Tobias

3. Fatima Zahra plans the night

   The analyst · week 1, night 3, request 3 of 3

   Night 2's magnitude check (report 1) landed a bigger finding than expected: cycle_count movements are literally always exactly ±1 unit (min=max=1, 100% of 10,193 events) — confirmed, not a query artifact, since adjustment/receipt/return/shipment all show wide, varied distributions in the same query. That means my night-1 "scale of the problem" was built entirely on a stream that, by construction, can never show a large miss. Meanwhile kind='adjustment' events are rare (407 in the year) but average ~10 units and run up to 192 units per event — a completely different animal, and likely the stream that actually matters for Tobias's write-off/valuation question. I under-covered this on night 1 and need to correct course before locking the memo.

   Also from night 2: report 2 confirms cycle-count activity is 100% in 'pick' locations (120 of 157 pick locations active, 0 of 157 bulk, 0 of 157 returns locations) — a clean, decisive answer to "which location kind" for that stream, so I don't need to spend more on that question. And report 3 shows reservations are overwhelmingly reliable (99.3% released_on_time, only 0.3% ever stuck past expiry) — Q4 looks like a non-issue, I'll give it one more sanity pass later if time allows but it's not using a slot tonight.

   Tonight I'm redirecting all three slots to properly characterize the 'adjustment' stream, since it's likely the financially material one: (1) its own monthly scale/trend, parallel to night 1's cycle_count view, so I can compare the two streams side by side in the memo; (2) the actual top individual events (only 407 total, so a top-30 detail table efficiently reveals whether size concentrates by warehouse/tenant/category/product all at once, rather than slicing a thin 407-row base three more ways); (3) a definitional cross-check against the separate stock_adjustments table, since that table is described as the human-entered correction record with a reason, and I want to confirm it's the same universe as stock_movements kind='adjustment' before I tell auditors which number is authoritative.

   Read the whole plan
   Cross-check: stock_adjustments table vs stock_movements adjustment-kind ledger, 2025

   Question

   Does the stock_adjustments table (the human-entered correction record with a required reason) cover the same volume as the stock_movements kind='adjustment' ledger, or is one capturing materially more/less than the other — i.e., which is the authoritative source for 'a physical count or review found a quantity discrepancy'?

   One row per

   data source (2 rows: 'stock_adjustments table' and 'stock_movements kind=adjustment ledger')

   Columns

   • source
   • row_count
   • total_abs_quantity (sum of abs(quantity_delta) for stock_adjustments, abs(quantity) for stock_movements)
   • distinct_variants_affected
   • earliest_date
   • latest_date

4. Viktor writes the SQL

   The engineer · 3 dry runs on the empty schema, then the submission

   Dry run 1 · STATUS: REJECTED

   select
     'stock_adjustments table' as source,
     count(*) as row_count,
     sum(abs(quantity_delta)) as total_abs_quantity,
     count(distinct variant_id) as distinct_variants_affected,
     min(created_at) as earliest_date,
     max(created_at) as latest_date
   from stock_adjustments
   where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
   
   union all
   
   select
     'stock_movements kind=adjustment ledger' as source,
     count(*) as row_count,
     sum(abs(quantity)) as total_abs_quantity,
     count(distinct variant_id) as distinct_variants_affected,
     min(occurred_at) as earliest_date,
     max(occurred_at) as latest_date
   from stock_movements
   where kind = 'adjustment'
     and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
   
   order by case when source = 'stock_adjustments table' then 0 else 1 end

   Dry run 2 · 3 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

   select  'stock_adjustments table' as source,  count(*) as row_count,  sum(abs(quantity_delta)) as total_abs_quantity,  count(distinct variant_id) as distinct_variants_affected,  min(created_at) as earliest_date,  max(created_at) as latest_datefrom stock_adjustmentswhere created_at >= date '2025-01-01' and created_at < date '2026-01-01'select * from (  select    'stock_adjustments table' as source,    count(*) as row_count,    sum(abs(quantity_delta)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(created_at) as earliest_date,    max(created_at) as latest_date,    0 as sort_order  from stock_adjustments  where created_at >= date '2025-01-01' and created_at < date '2026-01-01'union all  union allselect  'stock_movements kind=adjustment ledger' as source,  count(*) as row_count,  sum(abs(quantity)) as total_abs_quantity,  count(distinct variant_id) as distinct_variants_affected,  min(occurred_at) as earliest_date,  max(occurred_at) as latest_datefrom stock_movementswhere kind = 'adjustment'  and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'order by case when source = 'stock_adjustments table' then 0 else 1 end  select    'stock_movements kind=adjustment ledger' as source,    count(*) as row_count,    sum(abs(quantity)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(occurred_at) as earliest_date,    max(occurred_at) as latest_date,    1 as sort_order  from stock_movements  where kind = 'adjustment'    and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01') torder by sort_order

   Dry run 3 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

   select * from (select source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date from (  select    'stock_adjustments table' as source,    count(*) as row_count,    sum(abs(quantity_delta)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(created_at) as earliest_date,    max(created_at) as latest_date,    0 as sort_order  from stock_adjustments  where created_at >= date '2025-01-01' and created_at < date '2026-01-01'  union all  select    'stock_movements kind=adjustment ledger' as source,    count(*) as row_count,    sum(abs(quantity)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(occurred_at) as earliest_date,    max(occurred_at) as latest_date,    1 as sort_order  from stock_movements  where kind = 'adjustment'    and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01') torder by sort_order

   “Query compiles cleanly with the requested 6 columns, 2 rows, ordered as specified.”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. The report gives two side-by-side aggregate rows (stock_adjustments vs. stock_movements kind='adjustment') for 2025, with row counts, summed absolute quantities, distinct variants affected, and date ranges — enough for Fatima to see whether the two sources reconcile or diverge in volume.”

5. The report comes back overnight

   As Fatima Zahra saw it, from the small dataset the agents worked on

   source                                 | row_count | total_abs_quantity | distinct_variants_affected | earliest_date       | latest_date               
   ---------------------------------------+-----------+--------------------+----------------------------+---------------------+---------------------------
   stock_adjustments table                |     10194 |              10194 |                       9373 | 2025-01-01 09:00:00 | 2025-12-31 16:00:00       
   stock_movements kind=adjustment ledger |       407 |               4067 |                        407 | 2025-05-19 09:00:00 | 2025-12-31 22:59:59.999999
   (2 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q193Monthly volume of stock_adjustments table entries, 2025

   It builds on:

   • q190Scale of adjustment-kind stock movements by month, 2025

7. It becomes benchmark query q192

   Changed only where the benchmark needs the same result on every system

   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
   The benchmark text, against the submitted SQL

   select source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date from (  select    'stock_adjustments table' as source,    count(*) as row_count,    sum(abs(quantity_delta)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(created_at) as earliest_date,    max(created_at) as latest_date,    0 as sort_order  from stock_adjustments  where created_at >= date '2025-01-01' and created_at < date '2026-01-01'  union all  select    'stock_movements kind=adjustment ledger' as source,    count(*) as row_count,    sum(abs(quantity)) as total_abs_quantity,    count(distinct variant_id) as distinct_variants_affected,    min(occurred_at) as earliest_date,    max(occurred_at) as latest_date,    1 as sort_order  from stock_movements  where kind = 'adjustment'    and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01') torder by sort_orderorder by sort_order NULLS LAST

   As each system received it; highlighted, the parts spelled for that system

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   select source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date from (
     select
       'stock_adjustments table' as source,
       count(*) as row_count,
       sum(abs(quantity_delta)) as total_abs_quantity,
       count(distinct variant_id) as distinct_variants_affected,
       min(created_at) as earliest_date,
       max(created_at) as latest_date,
       0 as sort_order
     from stock_adjustments
     where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
   
     union all
   
     select
       'stock_movements kind=adjustment ledger' as source,
       count(*) as row_count,
       sum(abs(quantity)) as total_abs_quantity,
       count(distinct variant_id) as distinct_variants_affected,
       min(occurred_at) as earliest_date,
       max(occurred_at) as latest_date,
       1 as sort_order
     from stock_movements
     where kind = 'adjustment'
       and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
   ) t
   order by sort_order NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q192 on the start page, with every configuration.

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Viktor's whole session, 11 turns

   • The request, as Viktor received it

     Report request from Fatima Zahra (analyst for the Director of Fulfilment Operations):
     
     Title: Cross-check: stock_adjustments table vs stock_movements adjustment-kind ledger, 2025
     Question: Does the stock_adjustments table (the human-entered correction record with a required reason) cover the same volume as the stock_movements kind='adjustment' ledger, or is one capturing materially more/less than the other — i.e., which is the authoritative source for 'a physical count or review found a quantity discrepancy'?
     Grain: data source (2 rows: 'stock_adjustments table' and 'stock_movements kind=adjustment ledger')
     Columns:
     - source
     - row_count
     - total_abs_quantity (sum of abs(quantity_delta) for stock_adjustments, abs(quantity) for stock_movements)
     - distinct_variants_affected
     - earliest_date
     - latest_date
     Details: For 'stock_adjustments table': filter created_at (or occurred_at if present — use whichever timestamp column represents when the adjustment was recorded) between 2025-01-01 and 2025-12-31, count rows, sum abs(quantity_delta), count distinct variant_id. For 'stock_movements kind=adjustment ledger': filter occurred_at in the same 2025 window, count rows, sum abs(quantity), count distinct variant_id. Report both rows side by side so I can see whether row counts and totals roughly reconcile (same universe) or diverge (two different things). No row-level join needed — two independent aggregates is sufficient to answer the question. Order with 'stock_adjustments table' first, then the ledger row.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Viktor calls dry_run

     select
       'stock_adjustments table' as source,
       count(*) as row_count,
       sum(abs(quantity_delta)) as total_abs_quantity,
       count(distinct variant_id) as distinct_variants_affected,
       min(created_at) as earliest_date,
       max(created_at) as latest_date
     from stock_adjustments
     where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
     
     union all
     
     select
       'stock_movements kind=adjustment ledger' as source,
       count(*) as row_count,
       sum(abs(quantity)) as total_abs_quantity,
       count(distinct variant_id) as distinct_variants_affected,
       min(occurred_at) as earliest_date,
       max(occurred_at) as latest_date
     from stock_movements
     where kind = 'adjustment'
       and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
     
     order by case when source = 'stock_adjustments table' then 0 else 1 end

   • The warehouse answers

     STATUS: REJECTED
       - does not compile: Binder Error: Could not ORDER BY column "CASE  WHEN (("source" = 'stock_adjustments table')) THEN (0) ELSE 1 END": add the expression/function to every SELECT, or move the UNION into a FROM clause.

   • Viktor calls dry_run

     select * from (
       select
         'stock_adjustments table' as source,
         count(*) as row_count,
         sum(abs(quantity_delta)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(created_at) as earliest_date,
         max(created_at) as latest_date,
         0 as sort_order
       from stock_adjustments
       where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
     
       union all
     
       select
         'stock_movements kind=adjustment ledger' as source,
         count(*) as row_count,
         sum(abs(quantity)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(occurred_at) as earliest_date,
         max(occurred_at) as latest_date,
         1 as sort_order
       from stock_movements
       where kind = 'adjustment'
         and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
     ) t
     order by sort_order

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date, sort_order

   • Viktor calls dry_run

     select source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date from (
       select
         'stock_adjustments table' as source,
         count(*) as row_count,
         sum(abs(quantity_delta)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(created_at) as earliest_date,
         max(created_at) as latest_date,
         0 as sort_order
       from stock_adjustments
       where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
     
       union all
     
       select
         'stock_movements kind=adjustment ledger' as source,
         count(*) as row_count,
         sum(abs(quantity)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(occurred_at) as earliest_date,
         max(occurred_at) as latest_date,
         1 as sort_order
       from stock_movements
       where kind = 'adjustment'
         and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
     ) t
     order by sort_order

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date

   • ViktorQuery compiles cleanly with the requested 6 columns, 2 rows, ordered as specified.
   • Viktor calls submit

     select source, row_count, total_abs_quantity, distinct_variants_affected, earliest_date, latest_date from (
       select
         'stock_adjustments table' as source,
         count(*) as row_count,
         sum(abs(quantity_delta)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(created_at) as earliest_date,
         max(created_at) as latest_date,
         0 as sort_order
       from stock_adjustments
       where created_at >= date '2025-01-01' and created_at < date '2026-01-01'
     
       union all
     
       select
         'stock_movements kind=adjustment ledger' as source,
         count(*) as row_count,
         sum(abs(quantity)) as total_abs_quantity,
         count(distinct variant_id) as distinct_variants_affected,
         min(occurred_at) as earliest_date,
         max(occurred_at) as latest_date,
         1 as sort_order
       from stock_movements
       where kind = 'adjustment'
         and occurred_at >= date '2025-01-01' and occurred_at < date '2026-01-01'
     ) t
     order by sort_order

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • ViktorSubmitted. The report gives two side-by-side aggregate rows (stock_adjustments vs. stock_movements kind='adjustment') for 2025, with row counts, summed absolute quantities, distinct variants affected, and date ranges — enough for Fatima to see whether the two sources reconcile or diverge in volume.

   Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

   Viktor's transcript (271 KB) · Fatima Zahra's transcript (935 KB) · Tomasz Nowak's transcript (261 KB) · the night's plan (6 KB) · the night's reports (10 KB) · the week's memo (3 KB)