Queries · q397 · Director of Fulfilment Operations · week 2, night 3
“Is the near-constant rate of about 1.08 routine stock-adjustments per distinct affected variant per year a quirk of the apparel/footwear vertical, or does it hold across all merchant verticals — i.e., is the whole routine discrepancy stream essentially a flat background rate of roughly one single-unit miss per SKU per year, regardless of product type?”
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 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:
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
The analyst · week 2, night 3, request 3 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.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.industry, COUNT(*) AS total_routine_adjustments_2025, COUNT(DISTINCT sa.variant_id) AS distinct_variants_with_adjustment, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT sa.variant_id), 0), 3) AS adjustments_per_variant, COUNT(DISTINCT t.id) AS distinct_tenants_in_industry FROM stock_adjustments sa JOIN stock_locations sl ON sl.id = sa.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY t.industry ORDER BY adjustments_per_variant DESC, t.industry ASC
“Query compiles and matches the requested grain and columns. Submitting.”
“Submitted. The report groups 2025 stock-adjustment activity by tenants.industry, with total adjustments, distinct affected variants, the resulting adjustments-per-variant ratio, and tenant count per industry — letting Fatima see directly whether the ~1.08 rate is apparel-specific or shows up uniformly across verticals.”
As Fatima Zahra saw it, from the small dataset the agents worked on
industry | total_routine_adjustments_2025 | distinct_variants_with_adjustment | adjustments_per_variant | distinct_tenants_in_industry --------------------------+--------------------------------+-----------------------------------+-------------------------+----------------------------- Sports & Outdoor | 1343 | 1181 | 1.137 | 13 Jewelry & Accessories | 206 | 185 | 1.114 | 6 Industrial Supplies (B2B) | 165 | 150 | 1.1 | 5 Office Supplies (B2B) | 99 | 90 | 1.1 | 4 Apparel & Footwear | 6503 | 6000 | 1.084 | 25 Pet Supplies | 225 | 209 | 1.077 | 10 Home & Living | 589 | 552 | 1.067 | 16 Consumer Electronics | 293 | 275 | 1.065 | 9 Food & Coffee | 244 | 231 | 1.056 | 11 Beauty | 410 | 389 | 1.054 | 15 Books & Stationery | 117 | 111 | 1.054 | 6 (11 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.industry,SELECT t.industry AS industry, COUNT(*) AS total_routine_adjustments_2025, COUNT(DISTINCT sa.variant_id) AS distinct_variants_with_adjustment, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT sa.variant_id), 0), 3) AS adjustments_per_variant, CAST(COUNT(*) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT sa.variant_id), 0) AS adjustments_per_variant, COUNT(DISTINCT t.id) AS distinct_tenants_in_industryFROM stock_adjustments saJOIN stock_locations sl ON sl.id = sa.stock_location_idJOIN warehouses w ON w.id = sl.warehouse_idJOIN tenants t ON t.id = w.tenant_idWHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01'GROUP BY t.industryORDER BY adjustments_per_variant DESC, t.industry ASCORDER BY adjustments_per_variant DESC NULLS FIRST, t.industry ASC NULLS LAST
SELECT t.industry AS industry, COUNT(*) AS total_routine_adjustments_2025, COUNT(DISTINCT sa.variant_id) AS distinct_variants_with_adjustment, CAST(COUNT(*) AS numeric(18,6)) / NULLIF(COUNT(DISTINCT sa.variant_id), 0) AS adjustments_per_variant, COUNT(DISTINCT t.id) AS distinct_tenants_in_industry FROM stock_adjustments sa JOIN stock_locations sl ON sl.id = sa.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY t.industry ORDER BY adjustments_per_variant DESC NULLS FIRST, t.industry ASC 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: Is the ~1.08 adjustments-per-variant ratio apparel-specific or company-wide? Question: Is the near-constant rate of about 1.08 routine stock-adjustments per distinct affected variant per year a quirk of the apparel/footwear vertical, or does it hold across all merchant verticals — i.e., is the whole routine discrepancy stream essentially a flat background rate of roughly one single-unit miss per SKU per year, regardless of product type? Grain: tenant industry (tenants.industry) Columns: - industry - total_routine_adjustments_2025 (count of stock_adjustments rows at tenants of this industry, created_at in 2025) - distinct_variants_with_adjustment - adjustments_per_variant (total_routine_adjustments_2025 / distinct_variants_with_adjustment, rounded 3dp) - distinct_tenants_in_industry Details: Source: stock_adjustments, created_at between 2025-01-01 and 2025-12-31 (half-open bound), joined via stock_location_id -> stock_locations -> warehouses -> tenants, grouped by tenants.industry. Order by adjustments_per_variant descending, tie-break by industry alphabetically. Expect well under 50 rows (one per distinct industry string). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.industry, COUNT(*) AS total_routine_adjustments_2025, COUNT(DISTINCT sa.variant_id) AS distinct_variants_with_adjustment, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT sa.variant_id), 0), 3) AS adjustments_per_variant, COUNT(DISTINCT t.id) AS distinct_tenants_in_industry FROM stock_adjustments sa JOIN stock_locations sl ON sl.id = sa.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY t.industry ORDER BY adjustments_per_variant DESC, t.industry ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: industry, total_routine_adjustments_2025, distinct_variants_with_adjustment, adjustments_per_variant, distinct_tenants_in_industry
submitSELECT t.industry, COUNT(*) AS total_routine_adjustments_2025, COUNT(DISTINCT sa.variant_id) AS distinct_variants_with_adjustment, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT sa.variant_id), 0), 3) AS adjustments_per_variant, COUNT(DISTINCT t.id) AS distinct_tenants_in_industry FROM stock_adjustments sa JOIN stock_locations sl ON sl.id = sa.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY t.industry ORDER BY adjustments_per_variant DESC, t.industry ASC
Submitted to tonight's batch. Your work on this request is done.