Queries · q193 · Director of Fulfilment Operations · week 1, night 4

Monthly volume of stock_adjustments table entries, 2025

“Does the stock_adjustments table show a steady, roughly even pattern across 2025 (like the cycle_count movement stream it appears to feed), or does it also cluster at year-end like the stock_movements kind='adjustment' ledger?”

Manager · asked the questionTomasz NowakDirector of Fulfilment Operations. Ran warehouses for a large retailer before joining.

Analyst · planned the reportFatima ZahraDefinition-minded: checks what each field really records before relying on it.

Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

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
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
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
Fatima Zahra plans the night
The analyst · week 1, night 4, request 1 of 3
Night 3 reframes the whole story. Report 1 shows kind='adjustment' movements are NOT a steady monthly stream at all: 406 of 407 events fall on a single timestamp, 2025-12-31 22:59:59.999999, with only one lone event back in May. Report 2 confirms this: all 30 largest rows share that exact timestamp and reference_type='correction', and — strikingly — every single one of the top 30 belongs to B2B wholesale merchants (Deskline Supplies, OfficeHub Nordic, Kontor24 Bürobedarf, Hanse Industriebedarf, Castell Fasteners, Ballycorran Industrial Supply, Midland Safety Wear), in categories like Desks, Copy Paper, Toner, Storage, Drills, Bolts — exactly the "industrial supplies / office equipment" verticals from the company briefing, not apparel/electronics/food. And the net direction is all positive (units_over, i.e. gains, not shrinkage) — the opposite skew from the cycle_count stream. This looks like a once-a-year bulk stocktake/reconciliation true-up specific to the B2B wholesale book, not an ongoing operational accuracy problem.

Report 3's cross-check delivers the key structural finding: stock_adjustments table has 10,194 rows, averaging 1.0 unit each, spread Jan 1 - Dec 31 — almost exactly matching stock_movements kind='cycle_count' (10,193 events, also always 1 unit). That strongly suggests stock_adjustments is the human-entered source behind the routine cycle_count stream, while stock_movements kind='adjustment' (407 events, all Dec 31) is a completely separate, rare, bulk mechanism unrelated to stock_adjustments. I need to confirm this before writing the memo, since it changes which number is "the" ongoing accuracy metric (cycle_count/stock_adjustments, ~260k EUR/year, steady) versus a one-off year-end event (adjustment-kind, ~138k EUR, concentrated in one day and one merchant segment).

This is the last night, so my three requests tonight are built to close out open definitional questions before I write Friday's memo: (A) confirm stock_adjustments is spread evenly through the year like cycle_count (not clustered like the Dec-31 batch) — settling the "two separate mechanisms" theory; (B) quantify exactly how concentrated the Dec-31 bulk correction is by merchant/vertical, since Tobias specifically asked whether exposure clusters by merchant; (C) get the reason-text breakdown behind the routine stock_adjustments entries, to give Tobias's shrinkage-vs-gain provision question real qualitative grounding beyond just the signed quantity. Reservation reliability (Q4) I'm treating as settled from night 2 (99.3% released on time, 0% stuck past expiry) and don't need another slot there.
Monthly volume of stock_adjustments table entries, 2025
Question
Does the stock_adjustments table show a steady, roughly even pattern across 2025 (like the cycle_count movement stream it appears to feed), or does it also cluster at year-end like the stock_movements kind='adjustment' ledger?
One row per
calendar month (Jan 2025 - Dec 2025)
Columns
- month (YYYY-MM)
- adjustment_count (rows in stock_adjustments)
- total_abs_quantity_delta (sum of abs(quantity_delta))
- net_quantity_delta (signed sum; negative = net shrinkage)
- distinct_variants_affected
- avg_abs_quantity_delta (rounded 2dp)

Bea writes the SQL

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

Dry run 1 · STATUS: REJECTED

SELECT
    to_char(date_trunc('month', t0.created_at), 'YYYY-MM') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC

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

SELECT    to_char(date_trunc('month', t0.created_at), 'YYYY-MM') AS month,    strftime(date_trunc('month', t0.created_at), '%Y-%m') AS month,    COUNT(*) AS adjustment_count,    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,    SUM(t0.quantity_delta) AS net_quantity_delta,    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_deltaFROM stock_adjustments t0WHERE t0.created_at >= DATE '2025-01-01'  AND t0.created_at < DATE '2026-01-01'GROUP BY date_trunc('month', t0.created_at)ORDER BY month ASC

“This compiles and matches the requested grain and columns exactly.”

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

“Submitted. The report groups stock_adjustments by month of created_at for 2025, with adjustment counts, total/net/average quantity deltas, and distinct variants affected per month — exactly matching the requested grain and columns.”

The report comes back overnight

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

month   | adjustment_count | total_abs_quantity_delta | net_quantity_delta | distinct_variants_affected | avg_abs_quantity_delta
--------+------------------+--------------------------+--------------------+----------------------------+-----------------------
2025-01 |              764 |                      764 |               -256 |                        760 |                    1.0
2025-02 |              760 |                      760 |               -270 |                        755 |                    1.0
2025-03 |              822 |                      822 |               -336 |                        818 |                    1.0
2025-04 |              783 |                      783 |               -259 |                        780 |                    1.0
2025-05 |              854 |                      854 |               -358 |                        850 |                    1.0
2025-06 |              818 |                      818 |               -266 |                        812 |                    1.0
2025-07 |              849 |                      849 |               -311 |                        842 |                    1.0
2025-08 |              865 |                      865 |               -311 |                        857 |                    1.0
2025-09 |              875 |                      875 |               -341 |                        867 |                    1.0
2025-10 |              941 |                      941 |               -393 |                        933 |                    1.0
2025-11 |              917 |                      917 |               -383 |                        909 |                    1.0
2025-12 |              946 |                      946 |               -396 |                        938 |                    1.0
(12 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q195Reasons behind routine stock_adjustments entries, 2025
- q389Trend of stock-count discrepancies within 2025: steady climb or step change?
It builds on:
- q192Cross-check: stock_adjustments table vs stock_movements adjustment-kind ledger, 2025

It becomes benchmark query q193

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

placeholderyear month
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
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    strftime(date_trunc('month', t0.created_at), '%Y-%m') AS month,    {{year_month(date_trunc('month', t0.created_at))}} AS month,    COUNT(*) AS adjustment_count,    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,    SUM(t0.quantity_delta) AS net_quantity_delta,    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_delta    AVG(CAST(ABS(t0.quantity_delta) AS NUMERIC(18,6))) AS avg_abs_quantity_deltaFROM stock_adjustments t0WHERE t0.created_at >= DATE '2025-01-01'  AND t0.created_at < DATE '2026-01-01'GROUP BY date_trunc('month', t0.created_at)ORDER BY month ASCORDER BY month ASC NULLS LAST

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

SELECT
    strftime(date_trunc('month', t0.created_at), '%Y-%m') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    AVG(CAST(ABS(t0.quantity_delta) AS NUMERIC(18,6))) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC NULLS LAST

SELECT
    to_char(date_trunc('month', t0.created_at), 'YYYY-MM') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    AVG(CAST(ABS(t0.quantity_delta) AS NUMERIC(18,6))) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC NULLS LAST

SELECT
    to_char(CAST(date_trunc('month', t0.created_at) AS timestamp), 'YYYY-MM') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    AVG(CAST(ABS(t0.quantity_delta) AS NUMERIC(18,6))) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC NULLS LAST

SELECT
    formatDateTime(date_trunc('month', t0.created_at), '%Y-%m') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    AVG(CAST(ABS(t0.quantity_delta) AS NUMERIC(18,6))) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC NULLS LAST

DataFusionrow 1: col 1: "2025-01" vs "YYYY-MM"; col 2: "23153" vs "26541"; col 3: "23153" vs "26541"

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

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

Bea's whole session, 9 turns

The request, as Bea received it

Report request from Fatima Zahra (analyst for the Director of Fulfilment Operations):

Title: Monthly volume of stock_adjustments table entries, 2025
Question: Does the stock_adjustments table show a steady, roughly even pattern across 2025 (like the cycle_count movement stream it appears to feed), or does it also cluster at year-end like the stock_movements kind='adjustment' ledger?
Grain: calendar month (Jan 2025 - Dec 2025)
Columns:
- month (YYYY-MM)
- adjustment_count (rows in stock_adjustments)
- total_abs_quantity_delta (sum of abs(quantity_delta))
- net_quantity_delta (signed sum; negative = net shrinkage)
- distinct_variants_affected
- avg_abs_quantity_delta (rounded 2dp)
Details: Source: stock_adjustments, filtered on created_at between 2025-01-01 and 2025-12-31 (half-open: created_at >= 2025-01-01 and < 2026-01-01), since the table has no occurred_at column. Group by the month of created_at. No joins needed. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Bea calls dry_run

SELECT
    to_char(date_trunc('month', t0.created_at), 'YYYY-MM') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Bea calls dry_run

SELECT
    strftime(date_trunc('month', t0.created_at), '%Y-%m') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, adjustment_count, total_abs_quantity_delta, net_quantity_delta, distinct_variants_affected, avg_abs_quantity_delta

BeaThis compiles and matches the requested grain and columns exactly.

Bea calls submit

SELECT
    strftime(date_trunc('month', t0.created_at), '%Y-%m') AS month,
    COUNT(*) AS adjustment_count,
    SUM(ABS(t0.quantity_delta)) AS total_abs_quantity_delta,
    SUM(t0.quantity_delta) AS net_quantity_delta,
    COUNT(DISTINCT t0.variant_id) AS distinct_variants_affected,
    ROUND(AVG(ABS(t0.quantity_delta)), 2) AS avg_abs_quantity_delta
FROM stock_adjustments t0
WHERE t0.created_at >= DATE '2025-01-01'
  AND t0.created_at < DATE '2026-01-01'
GROUP BY date_trunc('month', t0.created_at)
ORDER BY month ASC

The warehouse answers

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

BeaSubmitted. The report groups `stock_adjustments` by month of `created_at` for 2025, with adjustment counts, total/net/average quantity deltas, and distinct variants affected per month — exactly matching the requested grain and columns.

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

Bea's transcript (264 KB) · Fatima Zahra's transcript (935 KB) · Tomasz Nowak's transcript (261 KB) · the night's plan (6 KB) · the night's reports (13 KB) · the week's memo (3 KB)

Monthly volume of stock_adjustments table entries, 2025

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

Tomasz Nowak writes to Fatima Zahra

Fatima Zahra plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q193

Everything, as recorded