Queries · q395 · Director of Fulfilment Operations · week 2, night 3

Is the December 31 correction one single batch event, or genuinely independent per merchant?

“Did the 407 stock_movements kind='adjustment' events in 2025 all fire at the exact same timestamp (suggesting one system/data event), or do different merchants' corrections have distinct timestamps (consistent with independent, organic annual stocktakes)?”

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 2
Subject: Re: Inventory accuracy memo — good start, a few things need tightening before I take this to Tobias/auditors

Fatima,

This is a strong first cut — the two-mechanism split and the "reservations are fine" finding are exactly what I needed, and I can rule out overselling as a risk now. A few things surprised me and I want them firmed up before we put numbers in front of auditors:
1. The 24% rise in monthly shrinkage worries me more than the headline total. Is that a steady climb all year, or did it step up around a specific point (new site opening, staffing change, holiday peak)? I want to know if we're looking at a slow-burn trend or something that started at a moment we can point to.
2. The loss-reason split being almost perfectly even across three categories makes me suspicious, not reassured. Before we size any provision off that split, I want to know whether that's a genuine pattern or an artifact of how staff pick a reason from a dropdown (e.g., defaulting or cycling through options). Can you sanity-check whether specific people, shifts, or sites are behind the "theft-suspected" tag specifically — that's the one that would actually worry legal and the auditors.
3. On the December 31st batch — I need to understand the "why," not just the "who." If this is confirmed as a real annual B2B stocktake, fine. But I want to understand why a positive correction of that size existed in the first place — were those units sitting miscounted all year, or is this a sign that we simply don't track B2B/bulk inventory day-to-day the way we do pick locations? That has different implications for the auditors than just "we run quarterly/annual stocktakes."
4. On the apparel/footwear concentration, I want specifics, not just category names. Is this spread evenly across our apparel merchants and sites, or does it cluster around particular sites, shifts, or size runs? If it's a handful of locations, that's a training/process fix I can act on this quarter; if it's spread everywhere, that's a bigger systemic question about how we handle high-variant SKUs generally.
If you can only run two this week, make it #1 and #2 — those directly affect what Tobias and I say about trend direction and provisioning, which is what the auditors will press hardest on.

Thanks — good work.

Tomasz
Fatima Zahra plans the night
The analyst · week 2, night 3, request 1 of 3
Night 2 gave me strong, consistent answers to both #3 and #4, but surfaced one claim serious enough to verify before I put it in writing, plus two follow-ups worth a cheap check before I close this out.

#3: B2B tenants' pick locations behave completely normally — routine stock_adjustments volume (14-57/year) is proportional to their (small) catalog size, same as everyone else. The Dec-31 correction (134-1,058 units per tenant) is 7-23x larger than each tenant's entire year of routine pick activity, which fits "bulk locations never get day-to-day counted, so drift accumulates for a year." But all 8 independent merchants' correction events landed at exactly 2025-12-31 22:59:59.999999 in the sample I've seen — that's not plausible as eight separate companies' organic annual stocktakes finishing at the identical microsecond. I want to confirm this is true across all 407 events, not just the top 30 I happened to see, before telling Tomasz whether this looks like a real process or a data artifact.

#4: Resolved cleanly — distinct_pick_locations is 1 for every apparel warehouse, so "per-location rate" collapses to "per-catalog-size rate," and adjustments-per-distinct-variant is a near-constant ~1.07-1.09 across every apparel site regardless of size (Atlanta 1506/1380=1.09, Reading 1058/977=1.08, down to Tampere 14/13=1.08). The product-level drill-down confirms no hot SKU/size/color (max 5 events across 2,927 variants). This is not a "few bad sites, fix with training" story — it's a flat background rate that scales with SKU count. I want to check one more thing before generalizing: is that ~1.08 ratio apparel-specific, or does it hold company-wide across verticals? If it's universal, that's a much cleaner, stronger closing statement for the whole two-week engagement.

Also noticed: several apparel merchants have a second site literally named "...Warehouse" (not "...Fulfilment Centre") with zero adjustment activity all year. Before I call that "not a problem," I want to know if those sites actually hold live inventory (a real blind spot) or are empty/inactive.

Tonight's three requests: (A) confirm or rule out the single-timestamp artifact across the full 407-event Dec-31 batch; (B) check whether the zero-activity "Warehouse" sites hold meaningful stock; (C) test whether the ~1.08 adjustments-per-variant ratio is apparel-specific or a company-wide constant.
Is the December 31 correction one single batch event, or genuinely independent per merchant?
Question
Did the 407 stock_movements kind='adjustment' events in 2025 all fire at the exact same timestamp (suggesting one system/data event), or do different merchants' corrections have distinct timestamps (consistent with independent, organic annual stocktakes)?
One row per
distinct occurred_at timestamp value, among stock_movements where kind='adjustment' and occurred_at in 2025
Columns
- occurred_at (the exact timestamp value)
- event_count (rows sharing this exact timestamp)
- distinct_tenants
- distinct_warehouses
- total_abs_quantity

Bea writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

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

WITH t0 AS (
  SELECT DISTINCT sm0.occurred_at AS occurred_at
  FROM stock_movements sm0
  WHERE sm0.kind = 'adjustment'
    AND sm0.occurred_at >= DATE '2025-01-01'
    AND sm0.occurred_at < DATE '2026-01-01'
),
t1 AS (
  SELECT sm1.occurred_at AS occurred_at,
         COUNT(*) AS event_count
  FROM stock_movements sm1
  WHERE sm1.kind = 'adjustment'
    AND sm1.occurred_at >= DATE '2025-01-01'
    AND sm1.occurred_at < DATE '2026-01-01'
  GROUP BY sm1.occurred_at
),
t2 AS (
  SELECT sm2.occurred_at AS occurred_at,
         COUNT(DISTINCT sl2.warehouse_id) AS distinct_warehouses
  FROM stock_movements sm2
  JOIN stock_locations sl2 ON sl2.id = sm2.stock_location_id
  WHERE sm2.kind = 'adjustment'
    AND sm2.occurred_at >= DATE '2025-01-01'
    AND sm2.occurred_at < DATE '2026-01-01'
  GROUP BY sm2.occurred_at
),
t3 AS (
  SELECT sm3.occurred_at AS occurred_at,
         COUNT(DISTINCT w3.tenant_id) AS distinct_tenants
  FROM stock_movements sm3
  JOIN stock_locations sl3 ON sl3.id = sm3.stock_location_id
  JOIN warehouses w3 ON w3.id = sl3.warehouse_id
  WHERE sm3.kind = 'adjustment'
    AND sm3.occurred_at >= DATE '2025-01-01'
    AND sm3.occurred_at < DATE '2026-01-01'
  GROUP BY sm3.occurred_at
),
t4 AS (
  SELECT sm4.occurred_at AS occurred_at,
         SUM(ABS(sm4.quantity)) AS total_abs_quantity
  FROM stock_movements sm4
  WHERE sm4.kind = 'adjustment'
    AND sm4.occurred_at >= DATE '2025-01-01'
    AND sm4.occurred_at < DATE '2026-01-01'
  GROUP BY sm4.occurred_at
)
SELECT
  t0.occurred_at AS occurred_at,
  COALESCE(t1.event_count, 0) AS event_count,
  COALESCE(t3.distinct_tenants, 0) AS distinct_tenants,
  COALESCE(t2.distinct_warehouses, 0) AS distinct_warehouses,
  COALESCE(t4.total_abs_quantity, 0) AS total_abs_quantity
FROM t0
LEFT JOIN t1 ON t1.occurred_at = t0.occurred_at
LEFT JOIN t2 ON t2.occurred_at = t0.occurred_at
LEFT JOIN t3 ON t3.occurred_at = t0.occurred_at
LEFT JOIN t4 ON t4.occurred_at = t0.occurred_at
ORDER BY event_count DESC, occurred_at ASC

“Query compiles cleanly. Submitting.”

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

“Submitted. The query groups the 2025 kind='adjustment' stock movements by their exact occurred_at value, reporting event count, distinct tenants/warehouses touched, and total absolute quantity per timestamp — ordered by event count descending so the biggest clusters (and any single dominant batch timestamp) surface first.”

The report comes back overnight

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

occurred_at                | event_count | distinct_tenants | distinct_warehouses | total_abs_quantity
---------------------------+-------------+------------------+---------------------+-------------------
2025-12-31 22:59:59.999999 |         406 |               59 |                  59 |               4066
2025-05-19 09:00:00        |           1 |                1 |                   1 |                  1
(2 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q190Scale of adjustment-kind stock movements by month, 2025
- q392Do B2B wholesale merchants get routine stock counts, or only the annual catch-up?

It becomes benchmark query q395

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 (2×)

The benchmark text, against the submitted SQL

WITH t0 AS (  SELECT DISTINCT sm0.occurred_at AS occurred_at  FROM stock_movements sm0  WHERE sm0.kind = 'adjustment'    AND sm0.occurred_at >= DATE '2025-01-01'    AND sm0.occurred_at < DATE '2026-01-01'),t1 AS (  SELECT sm1.occurred_at AS occurred_at,         COUNT(*) AS event_count  FROM stock_movements sm1  WHERE sm1.kind = 'adjustment'    AND sm1.occurred_at >= DATE '2025-01-01'    AND sm1.occurred_at < DATE '2026-01-01'  GROUP BY sm1.occurred_at),t2 AS (  SELECT sm2.occurred_at AS occurred_at,         COUNT(DISTINCT sl2.warehouse_id) AS distinct_warehouses  FROM stock_movements sm2  JOIN stock_locations sl2 ON sl2.id = sm2.stock_location_id  WHERE sm2.kind = 'adjustment'    AND sm2.occurred_at >= DATE '2025-01-01'    AND sm2.occurred_at < DATE '2026-01-01'  GROUP BY sm2.occurred_at),t3 AS (  SELECT sm3.occurred_at AS occurred_at,         COUNT(DISTINCT w3.tenant_id) AS distinct_tenants  FROM stock_movements sm3  JOIN stock_locations sl3 ON sl3.id = sm3.stock_location_id  JOIN warehouses w3 ON w3.id = sl3.warehouse_id  WHERE sm3.kind = 'adjustment'    AND sm3.occurred_at >= DATE '2025-01-01'    AND sm3.occurred_at < DATE '2026-01-01'  GROUP BY sm3.occurred_at),t4 AS (  SELECT sm4.occurred_at AS occurred_at,         SUM(ABS(sm4.quantity)) AS total_abs_quantity  FROM stock_movements sm4  WHERE sm4.kind = 'adjustment'    AND sm4.occurred_at >= DATE '2025-01-01'    AND sm4.occurred_at < DATE '2026-01-01'  GROUP BY sm4.occurred_at)SELECT  t0.occurred_at AS occurred_at,  COALESCE(t1.event_count, 0) AS event_count,  COALESCE(t3.distinct_tenants, 0) AS distinct_tenants,  COALESCE(t2.distinct_warehouses, 0) AS distinct_warehouses,  COALESCE(t4.total_abs_quantity, 0) AS total_abs_quantityFROM t0LEFT JOIN t1 ON t1.occurred_at = t0.occurred_atLEFT JOIN t2 ON t2.occurred_at = t0.occurred_atLEFT JOIN t3 ON t3.occurred_at = t0.occurred_atLEFT JOIN t4 ON t4.occurred_at = t0.occurred_atORDER BY event_count DESC, occurred_at ASCORDER BY event_count DESC NULLS FIRST, occurred_at ASC NULLS LAST

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

WITH t0 AS (
  SELECT DISTINCT sm0.occurred_at AS occurred_at
  FROM stock_movements sm0
  WHERE sm0.kind = 'adjustment'
    AND sm0.occurred_at >= DATE '2025-01-01'
    AND sm0.occurred_at < DATE '2026-01-01'
),
t1 AS (
  SELECT sm1.occurred_at AS occurred_at,
         COUNT(*) AS event_count
  FROM stock_movements sm1
  WHERE sm1.kind = 'adjustment'
    AND sm1.occurred_at >= DATE '2025-01-01'
    AND sm1.occurred_at < DATE '2026-01-01'
  GROUP BY sm1.occurred_at
),
t2 AS (
  SELECT sm2.occurred_at AS occurred_at,
         COUNT(DISTINCT sl2.warehouse_id) AS distinct_warehouses
  FROM stock_movements sm2
  JOIN stock_locations sl2 ON sl2.id = sm2.stock_location_id
  WHERE sm2.kind = 'adjustment'
    AND sm2.occurred_at >= DATE '2025-01-01'
    AND sm2.occurred_at < DATE '2026-01-01'
  GROUP BY sm2.occurred_at
),
t3 AS (
  SELECT sm3.occurred_at AS occurred_at,
         COUNT(DISTINCT w3.tenant_id) AS distinct_tenants
  FROM stock_movements sm3
  JOIN stock_locations sl3 ON sl3.id = sm3.stock_location_id
  JOIN warehouses w3 ON w3.id = sl3.warehouse_id
  WHERE sm3.kind = 'adjustment'
    AND sm3.occurred_at >= DATE '2025-01-01'
    AND sm3.occurred_at < DATE '2026-01-01'
  GROUP BY sm3.occurred_at
),
t4 AS (
  SELECT sm4.occurred_at AS occurred_at,
         SUM(ABS(sm4.quantity)) AS total_abs_quantity
  FROM stock_movements sm4
  WHERE sm4.kind = 'adjustment'
    AND sm4.occurred_at >= DATE '2025-01-01'
    AND sm4.occurred_at < DATE '2026-01-01'
  GROUP BY sm4.occurred_at
)
SELECT
  t0.occurred_at AS occurred_at,
  COALESCE(t1.event_count, 0) AS event_count,
  COALESCE(t3.distinct_tenants, 0) AS distinct_tenants,
  COALESCE(t2.distinct_warehouses, 0) AS distinct_warehouses,
  COALESCE(t4.total_abs_quantity, 0) AS total_abs_quantity
FROM t0
LEFT JOIN t1 ON t1.occurred_at = t0.occurred_at
LEFT JOIN t2 ON t2.occurred_at = t0.occurred_at
LEFT JOIN t3 ON t3.occurred_at = t0.occurred_at
LEFT JOIN t4 ON t4.occurred_at = t0.occurred_at
ORDER BY event_count DESC NULLS FIRST, occurred_at ASC NULLS LAST

Scale factor 1000, c8a.24xlarge, after the last load. q395 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, 7 turns

The request, as Bea received it

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

Title: Is the December 31 correction one single batch event, or genuinely independent per merchant?
Question: Did the 407 stock_movements kind='adjustment' events in 2025 all fire at the exact same timestamp (suggesting one system/data event), or do different merchants' corrections have distinct timestamps (consistent with independent, organic annual stocktakes)?
Grain: distinct occurred_at timestamp value, among stock_movements where kind='adjustment' and occurred_at in 2025
Columns:
- occurred_at (the exact timestamp value)
- event_count (rows sharing this exact timestamp)
- distinct_tenants
- distinct_warehouses
- total_abs_quantity
Details: Source: stock_movements where kind='adjustment', occurred_at between 2025-01-01 and 2025-12-31 (half-open bound: >= 2025-01-01 and < 2026-01-01). Group by the exact occurred_at value (to the microsecond, as stored). Join stock_locations->warehouses->tenants only to count distinct tenants/warehouses per timestamp group, not to filter. Order by event_count descending, tie-break by occurred_at ascending. Expect very few distinct timestamp values (ideally this fits well under 50 rows); note the total distinct timestamp count.

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

Bea calls dry_run

WITH t0 AS (
  SELECT DISTINCT sm0.occurred_at AS occurred_at
  FROM stock_movements sm0
  WHERE sm0.kind = 'adjustment'
    AND sm0.occurred_at >= DATE '2025-01-01'
    AND sm0.occurred_at < DATE '2026-01-01'
),
t1 AS (
  SELECT sm1.occurred_at AS occurred_at,
         COUNT(*) AS event_count
  FROM stock_movements sm1
  WHERE sm1.kind = 'adjustment'
    AND sm1.occurred_at >= DATE '2025-01-01'
    AND sm1.occurred_at < DATE '2026-01-01'
  GROUP BY sm1.occurred_at
),
t2 AS (
  SELECT sm2.occurred_at AS occurred_at,
         COUNT(DISTINCT sl2.warehouse_id) AS distinct_warehouses
  FROM stock_movements sm2
  JOIN stock_locations sl2 ON sl2.id = sm2.stock_location_id
  WHERE sm2.kind = 'adjustment'
    AND sm2.occurred_at >= DATE '2025-01-01'
    AND sm2.occurred_at < DATE '2026-01-01'
  GROUP BY sm2.occurred_at
),
t3 AS (
  SELECT sm3.occurred_at AS occurred_at,
         COUNT(DISTINCT w3.tenant_id) AS distinct_tenants
  FROM stock_movements sm3
  JOIN stock_locations sl3 ON sl3.id = sm3.stock_location_id
  JOIN warehouses w3 ON w3.id = sl3.warehouse_id
  WHERE sm3.kind = 'adjustment'
    AND sm3.occurred_at >= DATE '2025-01-01'
    AND sm3.occurred_at < DATE '2026-01-01'
  GROUP BY sm3.occurred_at
),
t4 AS (
  SELECT sm4.occurred_at AS occurred_at,
         SUM(ABS(sm4.quantity)) AS total_abs_quantity
  FROM stock_movements sm4
  WHERE sm4.kind = 'adjustment'
    AND sm4.occurred_at >= DATE '2025-01-01'
    AND sm4.occurred_at < DATE '2026-01-01'
  GROUP BY sm4.occurred_at
)
SELECT
  t0.occurred_at AS occurred_at,
  COALESCE(t1.event_count, 0) AS event_count,
  COALESCE(t3.distinct_tenants, 0) AS distinct_tenants,
  COALESCE(t2.distinct_warehouses, 0) AS distinct_warehouses,
  COALESCE(t4.total_abs_quantity, 0) AS total_abs_quantity
FROM t0
LEFT JOIN t1 ON t1.occurred_at = t0.occurred_at
LEFT JOIN t2 ON t2.occurred_at = t0.occurred_at
LEFT JOIN t3 ON t3.occurred_at = t0.occurred_at
LEFT JOIN t4 ON t4.occurred_at = t0.occurred_at
ORDER BY event_count DESC, occurred_at ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: occurred_at, event_count, distinct_tenants, distinct_warehouses, total_abs_quantity

BeaQuery compiles cleanly. Submitting.

Bea calls submit

WITH t0 AS (
  SELECT DISTINCT sm0.occurred_at AS occurred_at
  FROM stock_movements sm0
  WHERE sm0.kind = 'adjustment'
    AND sm0.occurred_at >= DATE '2025-01-01'
    AND sm0.occurred_at < DATE '2026-01-01'
),
t1 AS (
  SELECT sm1.occurred_at AS occurred_at,
         COUNT(*) AS event_count
  FROM stock_movements sm1
  WHERE sm1.kind = 'adjustment'
    AND sm1.occurred_at >= DATE '2025-01-01'
    AND sm1.occurred_at < DATE '2026-01-01'
  GROUP BY sm1.occurred_at
),
t2 AS (
  SELECT sm2.occurred_at AS occurred_at,
         COUNT(DISTINCT sl2.warehouse_id) AS distinct_warehouses
  FROM stock_movements sm2
  JOIN stock_locations sl2 ON sl2.id = sm2.stock_location_id
  WHERE sm2.kind = 'adjustment'
    AND sm2.occurred_at >= DATE '2025-01-01'
    AND sm2.occurred_at < DATE '2026-01-01'
  GROUP BY sm2.occurred_at
),
t3 AS (
  SELECT sm3.occurred_at AS occurred_at,
         COUNT(DISTINCT w3.tenant_id) AS distinct_tenants
  FROM stock_movements sm3
  JOIN stock_locations sl3 ON sl3.id = sm3.stock_location_id
  JOIN warehouses w3 ON w3.id = sl3.warehouse_id
  WHERE sm3.kind = 'adjustment'
    AND sm3.occurred_at >= DATE '2025-01-01'
    AND sm3.occurred_at < DATE '2026-01-01'
  GROUP BY sm3.occurred_at
),
t4 AS (
  SELECT sm4.occurred_at AS occurred_at,
         SUM(ABS(sm4.quantity)) AS total_abs_quantity
  FROM stock_movements sm4
  WHERE sm4.kind = 'adjustment'
    AND sm4.occurred_at >= DATE '2025-01-01'
    AND sm4.occurred_at < DATE '2026-01-01'
  GROUP BY sm4.occurred_at
)
SELECT
  t0.occurred_at AS occurred_at,
  COALESCE(t1.event_count, 0) AS event_count,
  COALESCE(t3.distinct_tenants, 0) AS distinct_tenants,
  COALESCE(t2.distinct_warehouses, 0) AS distinct_warehouses,
  COALESCE(t4.total_abs_quantity, 0) AS total_abs_quantity
FROM t0
LEFT JOIN t1 ON t1.occurred_at = t0.occurred_at
LEFT JOIN t2 ON t2.occurred_at = t0.occurred_at
LEFT JOIN t3 ON t3.occurred_at = t0.occurred_at
LEFT JOIN t4 ON t4.occurred_at = t0.occurred_at
ORDER BY event_count DESC, occurred_at ASC

The warehouse answers

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

BeaSubmitted. The query groups the 2025 `kind='adjustment'` stock movements by their exact `occurred_at` value, reporting event count, distinct tenants/warehouses touched, and total absolute quantity per timestamp — ordered by event count descending so the biggest clusters (and any single dominant batch timestamp) surface first.

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 (265 KB) · Fatima Zahra's transcript (935 KB) · Tomasz Nowak's transcript (261 KB) · the night's plan (7 KB) · the night's reports (6 KB) · the week's memo (3 KB)

Is the December 31 correction one single batch event, or genuinely independent per merchant?

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 q395

Everything, as recorded