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

Do the zero-activity 'Warehouse' sites hold real inventory?

“The apparel merchants' secondary sites named '...Warehouse' (as opposed to '...Fulfilment Centre') showed zero stock-count activity all year — are these empty/inactive shells, or do they hold meaningful live inventory that is simply never being counted (a blind spot)?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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 2 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.
Do the zero-activity 'Warehouse' sites hold real inventory?
Question
The apparel merchants' secondary sites named '...Warehouse' (as opposed to '...Fulfilment Centre') showed zero stock-count activity all year — are these empty/inactive shells, or do they hold meaningful live inventory that is simply never being counted (a blind spot)?
One row per
warehouse, restricted to the 9 zero-activity sites identified last night (Aarhus Warehouse/Oakhurst Tailoring, Boston Warehouse/Cragsley Outfitters, Brussels Warehouse/Studio Vrij, Calgary Warehouse/Cragsley Outfitters, Glasgow Warehouse/Thistlecombe, Groningen Warehouse/Studio Vrij, Limerick Warehouse/Marrowby & Vane, Preston Warehouse/Marrowby & Vane, Toulouse Warehouse/Rue Lafayette Studio, Uppsala Warehouse/Velvet Ash)
Columns
- warehouse_name
- tenant_name
- total_stock_on_hand (sum of stock_items.quantity across all stock_locations in this warehouse)
- distinct_variants_stocked (count of distinct variant_id in stock_items with quantity > 0 at this warehouse)
- pct_of_tenant_total_stock_units (this warehouse's total_stock_on_hand / that tenant's total stock_on_hand across all its warehouses combined * 100, rounded 1dp)

Felix 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)

SELECT
  w.name AS warehouse_name,
  t.name AS tenant_name,
  (SELECT COALESCE(SUM(si.quantity), 0)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id) AS total_stock_on_hand,
  (SELECT COUNT(DISTINCT si.variant_id)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id
      AND si.quantity > 0) AS distinct_variants_stocked,
  ROUND(
    (
      (SELECT COALESCE(SUM(si.quantity), 0)
         FROM stock_items si
         JOIN stock_locations sl ON sl.id = si.stock_location_id
        WHERE sl.warehouse_id = w.id)::numeric
      /
      NULLIF(
        (SELECT COALESCE(SUM(si2.quantity), 0)
           FROM stock_items si2
           JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id
           JOIN warehouses w2 ON w2.id = sl2.warehouse_id
          WHERE w2.tenant_id = w.tenant_id), 0
      ) * 100
    ), 1
  ) AS pct_of_tenant_total_stock_units
FROM warehouses w
JOIN tenants t ON t.id = w.tenant_id
WHERE (t.name, w.name) IN (
  VALUES
    ('Oakhurst Tailoring', 'Aarhus Warehouse'),
    ('Cragsley Outfitters', 'Boston Warehouse'),
    ('Studio Vrij', 'Brussels Warehouse'),
    ('Cragsley Outfitters', 'Calgary Warehouse'),
    ('Thistlecombe', 'Glasgow Warehouse'),
    ('Studio Vrij', 'Groningen Warehouse'),
    ('Marrowby & Vane', 'Limerick Warehouse'),
    ('Marrowby & Vane', 'Preston Warehouse'),
    ('Rue Lafayette Studio', 'Toulouse Warehouse'),
    ('Velvet Ash', 'Uppsala Warehouse')
)
ORDER BY total_stock_on_hand DESC, warehouse_name

“Query compiles cleanly with the expected columns. Submitting.”

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

“Submitted. One note: the request text said "9 zero-activity sites" but then listed 10 named warehouse/tenant pairs — I included all 10 as explicitly listed and flagged the discrepancy in the submission note for the analyst to confirm.”

The report comes back overnight

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

warehouse_name      | tenant_name          | total_stock_on_hand | distinct_variants_stocked | pct_of_tenant_total_stock_units
--------------------+----------------------+---------------------+---------------------------+--------------------------------
Aarhus Warehouse    | Oakhurst Tailoring   |                   0 |                         0 |                             0.0
Boston Warehouse    | Cragsley Outfitters  |                   0 |                         0 |                             0.0
Brussels Warehouse  | Studio Vrij          |                   0 |                         0 |                             0.0
Calgary Warehouse   | Cragsley Outfitters  |                   0 |                         0 |                             0.0
Glasgow Warehouse   | Thistlecombe         |                   0 |                         0 |                             0.0
Groningen Warehouse | Studio Vrij          |                   0 |                         0 |                             0.0
Limerick Warehouse  | Marrowby & Vane      |                   0 |                         0 |                             0.0
Preston Warehouse   | Marrowby & Vane      |                   0 |                         0 |                             0.0
Toulouse Warehouse  | Rue Lafayette Studio |                   0 |                         0 |                             0.0
Uppsala Warehouse   | Velvet Ash           |                   0 |                         0 |                             0.0
(10 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q393Apparel/footwear discrepancies by warehouse: scale effect or genuine elevated rate?

It becomes benchmark query q396

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

spellingcast
determinismtie-breaker: one row per warehouse
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

SELECT  w.name AS warehouse_name,  t.name AS tenant_name,  (SELECT COALESCE(SUM(si.quantity), 0)     FROM stock_items si     JOIN stock_locations sl ON sl.id = si.stock_location_id    WHERE sl.warehouse_id = w.id) AS total_stock_on_hand,  (SELECT COUNT(DISTINCT si.variant_id)     FROM stock_items si     JOIN stock_locations sl ON sl.id = si.stock_location_id    WHERE sl.warehouse_id = w.id      AND si.quantity > 0) AS distinct_variants_stocked,  ROUND(    (      (SELECT COALESCE(SUM(si.quantity), 0)         FROM stock_items si         JOIN stock_locations sl ON sl.id = si.stock_location_id        WHERE sl.warehouse_id = w.id)::numeric      /      NULLIF(        (SELECT COALESCE(SUM(si2.quantity), 0)           FROM stock_items si2           JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id           JOIN warehouses w2 ON w2.id = sl2.warehouse_id          WHERE w2.tenant_id = w.tenant_id), 0      ) * 100    ), 1  ) AS pct_of_tenant_total_stock_units  ( CAST((SELECT COALESCE(SUM(si.quantity), 0)    FROM stock_items si    JOIN stock_locations sl ON sl.id = si.stock_location_id   WHERE sl.warehouse_id = w.id) AS numeric(18,6)) / NULLIF(   (SELECT COALESCE(SUM(si2.quantity), 0)      FROM stock_items si2      JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id      JOIN warehouses w2 ON w2.id = sl2.warehouse_id     WHERE w2.tenant_id = w.tenant_id), 0 ) * 100) AS pct_of_tenant_total_stock_unitsFROM warehouses wJOIN tenants t ON t.id = w.tenant_idWHERE (t.name, w.name) IN (  VALUES    ('Oakhurst Tailoring', 'Aarhus Warehouse'),    ('Cragsley Outfitters', 'Boston Warehouse'),    ('Studio Vrij', 'Brussels Warehouse'),    ('Cragsley Outfitters', 'Calgary Warehouse'),    ('Thistlecombe', 'Glasgow Warehouse'),    ('Studio Vrij', 'Groningen Warehouse'),    ('Marrowby & Vane', 'Limerick Warehouse'),    ('Marrowby & Vane', 'Preston Warehouse'),    ('Rue Lafayette Studio', 'Toulouse Warehouse'),    ('Velvet Ash', 'Uppsala Warehouse'))ORDER BY total_stock_on_hand DESC, warehouse_nameORDER BY total_stock_on_hand DESC NULLS FIRST, warehouse_name NULLS LAST, w.id NULLS LAST

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

SELECT
  w.name AS warehouse_name,
  t.name AS tenant_name,
  (SELECT COALESCE(SUM(si.quantity), 0)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id) AS total_stock_on_hand,
  (SELECT COUNT(DISTINCT si.variant_id)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id
      AND si.quantity > 0) AS distinct_variants_stocked,
  (
 CAST((SELECT COALESCE(SUM(si.quantity), 0)
    FROM stock_items si
    JOIN stock_locations sl ON sl.id = si.stock_location_id
   WHERE sl.warehouse_id = w.id) AS numeric(18,6))
 /
 NULLIF(
   (SELECT COALESCE(SUM(si2.quantity), 0)
      FROM stock_items si2
      JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id
      JOIN warehouses w2 ON w2.id = sl2.warehouse_id
     WHERE w2.tenant_id = w.tenant_id), 0
 ) * 100
) AS pct_of_tenant_total_stock_units
FROM warehouses w
JOIN tenants t ON t.id = w.tenant_id
WHERE (t.name, w.name) IN (
  VALUES
    ('Oakhurst Tailoring', 'Aarhus Warehouse'),
    ('Cragsley Outfitters', 'Boston Warehouse'),
    ('Studio Vrij', 'Brussels Warehouse'),
    ('Cragsley Outfitters', 'Calgary Warehouse'),
    ('Thistlecombe', 'Glasgow Warehouse'),
    ('Studio Vrij', 'Groningen Warehouse'),
    ('Marrowby & Vane', 'Limerick Warehouse'),
    ('Marrowby & Vane', 'Preston Warehouse'),
    ('Rue Lafayette Studio', 'Toulouse Warehouse'),
    ('Velvet Ash', 'Uppsala Warehouse')
)
ORDER BY total_stock_on_hand DESC NULLS FIRST, warehouse_name NULLS LAST, w.id NULLS LAST

Hyperquantifiers require subqueries with one output column on the right hand side
DataFusionError during planning: Too many columns! The subquery should only return one column: column1, column2
ClickHouseCode: 48. DB::Exception: Correlated subqueries in ORDER BY are not supported. (NOT_IMPLEMENTED)

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

Felix's whole session, 7 turns

The request, as Felix received it

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

Title: Do the zero-activity 'Warehouse' sites hold real inventory?
Question: The apparel merchants' secondary sites named '...Warehouse' (as opposed to '...Fulfilment Centre') showed zero stock-count activity all year — are these empty/inactive shells, or do they hold meaningful live inventory that is simply never being counted (a blind spot)?
Grain: warehouse, restricted to the 9 zero-activity sites identified last night (Aarhus Warehouse/Oakhurst Tailoring, Boston Warehouse/Cragsley Outfitters, Brussels Warehouse/Studio Vrij, Calgary Warehouse/Cragsley Outfitters, Glasgow Warehouse/Thistlecombe, Groningen Warehouse/Studio Vrij, Limerick Warehouse/Marrowby & Vane, Preston Warehouse/Marrowby & Vane, Toulouse Warehouse/Rue Lafayette Studio, Uppsala Warehouse/Velvet Ash)
Columns:
- warehouse_name
- tenant_name
- total_stock_on_hand (sum of stock_items.quantity across all stock_locations in this warehouse)
- distinct_variants_stocked (count of distinct variant_id in stock_items with quantity > 0 at this warehouse)
- pct_of_tenant_total_stock_units (this warehouse's total_stock_on_hand / that tenant's total stock_on_hand across all its warehouses combined * 100, rounded 1dp)
Details: Join stock_items to stock_locations (stock_location_id) to warehouses (warehouse_id), filtered to the 10 named warehouse+tenant pairs listed above (match by warehouse name and tenant name together, since warehouse names like 'Uppsala Warehouse' are not globally unique). For pct_of_tenant_total_stock_units, compute each tenant's grand total stock_items.quantity across ALL of its warehouses (not just the named one) as the denominator. Order by total_stock_on_hand descending, tie-break by warehouse_name.

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

Felix calls dry_run

SELECT
  w.name AS warehouse_name,
  t.name AS tenant_name,
  (SELECT COALESCE(SUM(si.quantity), 0)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id) AS total_stock_on_hand,
  (SELECT COUNT(DISTINCT si.variant_id)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id
      AND si.quantity > 0) AS distinct_variants_stocked,
  ROUND(
    (
      (SELECT COALESCE(SUM(si.quantity), 0)
         FROM stock_items si
         JOIN stock_locations sl ON sl.id = si.stock_location_id
        WHERE sl.warehouse_id = w.id)::numeric
      /
      NULLIF(
        (SELECT COALESCE(SUM(si2.quantity), 0)
           FROM stock_items si2
           JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id
           JOIN warehouses w2 ON w2.id = sl2.warehouse_id
          WHERE w2.tenant_id = w.tenant_id), 0
      ) * 100
    ), 1
  ) AS pct_of_tenant_total_stock_units
FROM warehouses w
JOIN tenants t ON t.id = w.tenant_id
WHERE (t.name, w.name) IN (
  VALUES
    ('Oakhurst Tailoring', 'Aarhus Warehouse'),
    ('Cragsley Outfitters', 'Boston Warehouse'),
    ('Studio Vrij', 'Brussels Warehouse'),
    ('Cragsley Outfitters', 'Calgary Warehouse'),
    ('Thistlecombe', 'Glasgow Warehouse'),
    ('Studio Vrij', 'Groningen Warehouse'),
    ('Marrowby & Vane', 'Limerick Warehouse'),
    ('Marrowby & Vane', 'Preston Warehouse'),
    ('Rue Lafayette Studio', 'Toulouse Warehouse'),
    ('Velvet Ash', 'Uppsala Warehouse')
)
ORDER BY total_stock_on_hand DESC, warehouse_name

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: warehouse_name, tenant_name, total_stock_on_hand, distinct_variants_stocked, pct_of_tenant_total_stock_units

FelixQuery compiles cleanly with the expected columns. Submitting.

Felix calls submit

SELECT
  w.name AS warehouse_name,
  t.name AS tenant_name,
  (SELECT COALESCE(SUM(si.quantity), 0)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id) AS total_stock_on_hand,
  (SELECT COUNT(DISTINCT si.variant_id)
     FROM stock_items si
     JOIN stock_locations sl ON sl.id = si.stock_location_id
    WHERE sl.warehouse_id = w.id
      AND si.quantity > 0) AS distinct_variants_stocked,
  ROUND(
    (
      (SELECT COALESCE(SUM(si.quantity), 0)
         FROM stock_items si
         JOIN stock_locations sl ON sl.id = si.stock_location_id
        WHERE sl.warehouse_id = w.id)::numeric
      /
      NULLIF(
        (SELECT COALESCE(SUM(si2.quantity), 0)
           FROM stock_items si2
           JOIN stock_locations sl2 ON sl2.id = si2.stock_location_id
           JOIN warehouses w2 ON w2.id = sl2.warehouse_id
          WHERE w2.tenant_id = w.tenant_id), 0
      ) * 100
    ), 1
  ) AS pct_of_tenant_total_stock_units
FROM warehouses w
JOIN tenants t ON t.id = w.tenant_id
WHERE (t.name, w.name) IN (
  VALUES
    ('Oakhurst Tailoring', 'Aarhus Warehouse'),
    ('Cragsley Outfitters', 'Boston Warehouse'),
    ('Studio Vrij', 'Brussels Warehouse'),
    ('Cragsley Outfitters', 'Calgary Warehouse'),
    ('Thistlecombe', 'Glasgow Warehouse'),
    ('Studio Vrij', 'Groningen Warehouse'),
    ('Marrowby & Vane', 'Limerick Warehouse'),
    ('Marrowby & Vane', 'Preston Warehouse'),
    ('Rue Lafayette Studio', 'Toulouse Warehouse'),
    ('Velvet Ash', 'Uppsala Warehouse')
)
ORDER BY total_stock_on_hand DESC, warehouse_name

The warehouse answers

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

FelixSubmitted. One note: the request text said "9 zero-activity sites" but then listed 10 named warehouse/tenant pairs — I included all 10 as explicitly listed and flagged the discrepancy in the submission note for the analyst to confirm.

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

Felix's transcript (266 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)

Do the zero-activity 'Warehouse' sites hold real inventory?

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

Tomasz Nowak writes to Fatima Zahra

Fatima Zahra plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q396

Everything, as recorded