Queries · q392 · Director of Fulfilment Operations · week 2, night 2
“Do the B2B wholesale/office-supply merchants behind the December 31 correction run any routine, day-to-day stock-count activity during the year, or does their stock sit untouched in bulk storage until a single year-end true-up?”
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 2, request 1 of 3
Night 1 settled #1 and #2 confidently, so I'm moving to #3 and #4 tonight.
#1 resolved: events_per_active_location hovers in a tight 4.6-5.9 band all year with no upward drift (Jan ~5.2-5.4, Dec ~5.2-5.3) — essentially flat. The raw event count rise tracks almost exactly with new warehouses coming online (23 new warehouses opened across the year, cumulative matches the ~85→111 growth in active locations). So the "24% rise" is a scale effect from warehouse rollout, not a genuine per-location worsening, and there's no identifiable step-change moment — I'm confident in this and will write it up without more slots.
#2 resolved, and more decisively than expected: pct_theft_of_own_adjustments sits in a tight band (~15-23%) across essentially every one of the 35 staff identities and every warehouse, and — tellingly — each named staff member's total_adjustments_logged exactly equals one specific warehouse's total (Christopher Evans = 1,506 = Atlanta's total; Omar Taylor = 1,058 = Reading's total, etc.). That's a 1:1 staff-to-warehouse mapping, not real distributed rosters, and distinct_staff_logging_theft_here is never more than 1 per site. Combined with avg_hour_of_day clustering at ~11-12 (UTC) for nearly everyone regardless of which country their warehouse is in, this looks like a systematic/automated tagging pattern, not incident-by-incident human investigation. I'm flagging this as a data-provenance caveat for the memo rather than spending more slots on it.
Tonight: one report for #3 (does the B2B/bulk segment get routine counts at all, or only the Dec-31 catch-up — using the "pick-only" finding and the 8 B2B tenants identified from last week's correction report as the test group), and two reports for #4 (is the apparel concentration driven by genuine elevated rate per location or just by warehouse size, and which specific products/variants are actually driving it at the three standout sites: Atlanta/Cragsley, Reading/Marrowby & Vane, Den Haag/Studio Vrij — notably the same three merchants Support Ops flagged as generating 34% of all support tickets).
The engineer · 1 dry run on the empty schema, then the submission
WITH loc AS ( SELECT sl.id AS stock_location_id, w.tenant_id, sl.kind FROM stock_locations sl JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ), loc_agg AS ( SELECT tenant_id, count(*) AS total_stock_locations, count(*) FILTER (WHERE kind = 'bulk') AS bulk_count, count(*) FILTER (WHERE kind = 'pick') AS pick_count FROM loc GROUP BY tenant_id ), adj AS ( SELECT l.tenant_id, count(*) AS routine_stock_adjustments_2025, count(DISTINCT sa.variant_id) AS distinct_variants_with_routine_adjustment FROM stock_adjustments sa JOIN loc l ON l.stock_location_id = sa.stock_location_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY l.tenant_id ), dec31 AS ( SELECT l.tenant_id, sum(abs(sm.quantity)) AS dec31_correction_abs_units, count(DISTINCT sm.variant_id) AS dec31_correction_distinct_variants FROM stock_movements sm JOIN loc l ON l.stock_location_id = sm.stock_location_id WHERE sm.kind = 'adjustment' AND sm.occurred_at::date = DATE '2025-12-31' GROUP BY l.tenant_id ) SELECT t.name AS tenant_name, la.total_stock_locations, round(100.0 * la.bulk_count / la.total_stock_locations, 1) AS pct_locations_kind_bulk, round(100.0 * la.pick_count / la.total_stock_locations, 1) AS pct_locations_kind_pick, coalesce(adj.routine_stock_adjustments_2025, 0) AS routine_stock_adjustments_2025, coalesce(adj.distinct_variants_with_routine_adjustment, 0) AS distinct_variants_with_routine_adjustment, coalesce(dec31.dec31_correction_abs_units, 0) AS dec31_correction_abs_units, coalesce(dec31.dec31_correction_distinct_variants, 0) AS dec31_correction_distinct_variants FROM tenants t JOIN loc_agg la ON la.tenant_id = t.id LEFT JOIN adj ON adj.tenant_id = t.id LEFT JOIN dec31 ON dec31.tenant_id = t.id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ORDER BY dec31_correction_abs_units DESC, tenant_name
“Query compiles with the expected 8 columns at the tenant grain. Submitting.”
“Submitted. The report joins stock_locations → warehouses → tenants for the 8 named B2B merchants, computes location-kind mix (bulk vs. pick %), counts routine stock_adjustments through all of 2025, and separately measures the December 31 correction volume (stock_movements, kind='adjustment') in absolute units and distinct variants touched — ordered by the size of that Dec‑31 correction so the heaviest "catch-up only" merchants surface first.”
As Fatima Zahra saw it, from the small dataset the agents worked on
tenant_name | total_stock_locations | pct_locations_kind_bulk | pct_locations_kind_pick | routine_stock_adjustments_2025 | distinct_variants_with_routine_adjustmen | dec31_correction_abs_units | dec31_correction_distinct_variants ------------------------------+-----------------------+-------------------------+-------------------------+--------------------------------+------------------------------------------+----------------------------+----------------------------------- Ballycorran Industrial Supply | 6 | 33.3 | 33.3 | 57 | 51 | 1058 | 58 Castell Fasteners | 3 | 33.3 | 33.3 | 31 | 29 | 704 | 28 Hanse Industriebedarf | 3 | 33.3 | 33.3 | 27 | 23 | 458 | 20 Midland Safety Wear | 3 | 33.3 | 33.3 | 30 | 30 | 440 | 16 OfficeHub Nordic | 6 | 33.3 | 33.3 | 48 | 40 | 429 | 29 Deskline Supplies | 3 | 33.3 | 33.3 | 14 | 13 | 288 | 17 Kontor24 Bürobedarf | 6 | 33.3 | 33.3 | 28 | 28 | 285 | 25 Polder Packaging | 3 | 33.3 | 33.3 | 20 | 17 | 134 | 8 (8 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH loc AS ( SELECT sl.id AS stock_location_id, w.tenant_id, sl.kind SELECT sl.id AS stock_location_id, w.tenant_id AS tenant_id, sl.kind AS kind FROM stock_locations sl JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging')),loc_agg AS ( SELECT tenant_id, count(*) AS total_stock_locations, count(*) FILTER (WHERE kind = 'bulk') AS bulk_count, count(*) FILTER (WHERE kind = 'pick') AS pick_count FROM loc GROUP BY tenant_id),adj AS ( SELECT l.tenant_id, SELECT l.tenant_id AS tenant_id, count(*) AS routine_stock_adjustments_2025, count(DISTINCT sa.variant_id) AS distinct_variants_with_routine_adjustment FROM stock_adjustments sa JOIN loc l ON l.stock_location_id = sa.stock_location_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY l.tenant_id),dec31 AS ( SELECT l.tenant_id, SELECT l.tenant_id AS tenant_id, sum(abs(sm.quantity)) AS dec31_correction_abs_units, count(DISTINCT sm.variant_id) AS dec31_correction_distinct_variants FROM stock_movements sm JOIN loc l ON l.stock_location_id = sm.stock_location_id WHERE sm.kind = 'adjustment' AND sm.occurred_at::date = DATE '2025-12-31' WHERE sm.kind = 'adjustment' AND CAST(sm.occurred_at AS date) = DATE '2025-12-31' GROUP BY l.tenant_id)SELECT t.name AS tenant_name, la.total_stock_locations, round(100.0 * la.bulk_count / la.total_stock_locations, 1) AS pct_locations_kind_bulk, round(100.0 * la.pick_count / la.total_stock_locations, 1) AS pct_locations_kind_pick, la.total_stock_locations AS total_stock_locations, 100.0 * la.bulk_count / la.total_stock_locations AS pct_locations_kind_bulk, 100.0 * la.pick_count / la.total_stock_locations AS pct_locations_kind_pick, coalesce(adj.routine_stock_adjustments_2025, 0) AS routine_stock_adjustments_2025, coalesce(adj.distinct_variants_with_routine_adjustment, 0) AS distinct_variants_with_routine_adjustment, coalesce(dec31.dec31_correction_abs_units, 0) AS dec31_correction_abs_units, coalesce(dec31.dec31_correction_distinct_variants, 0) AS dec31_correction_distinct_variantsFROM tenants tJOIN loc_agg la ON la.tenant_id = t.idLEFT JOIN adj ON adj.tenant_id = t.idLEFT JOIN dec31 ON dec31.tenant_id = t.idWHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging')ORDER BY dec31_correction_abs_units DESC, tenant_nameORDER BY dec31_correction_abs_units DESC NULLS FIRST, tenant_name NULLS LAST, t.id NULLS LAST
WITH loc AS ( SELECT sl.id AS stock_location_id, w.tenant_id AS tenant_id, sl.kind AS kind FROM stock_locations sl JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ), loc_agg AS ( SELECT tenant_id, count(*) AS total_stock_locations, count(*) FILTER (WHERE kind = 'bulk') AS bulk_count, count(*) FILTER (WHERE kind = 'pick') AS pick_count FROM loc GROUP BY tenant_id ), adj AS ( SELECT l.tenant_id AS tenant_id, count(*) AS routine_stock_adjustments_2025, count(DISTINCT sa.variant_id) AS distinct_variants_with_routine_adjustment FROM stock_adjustments sa JOIN loc l ON l.stock_location_id = sa.stock_location_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY l.tenant_id ), dec31 AS ( SELECT l.tenant_id AS tenant_id, sum(abs(sm.quantity)) AS dec31_correction_abs_units, count(DISTINCT sm.variant_id) AS dec31_correction_distinct_variants FROM stock_movements sm JOIN loc l ON l.stock_location_id = sm.stock_location_id WHERE sm.kind = 'adjustment' AND CAST(sm.occurred_at AS date) = DATE '2025-12-31' GROUP BY l.tenant_id ) SELECT t.name AS tenant_name, la.total_stock_locations AS total_stock_locations, 100.0 * la.bulk_count / la.total_stock_locations AS pct_locations_kind_bulk, 100.0 * la.pick_count / la.total_stock_locations AS pct_locations_kind_pick, coalesce(adj.routine_stock_adjustments_2025, 0) AS routine_stock_adjustments_2025, coalesce(adj.distinct_variants_with_routine_adjustment, 0) AS distinct_variants_with_routine_adjustment, coalesce(dec31.dec31_correction_abs_units, 0) AS dec31_correction_abs_units, coalesce(dec31.dec31_correction_distinct_variants, 0) AS dec31_correction_distinct_variants FROM tenants t JOIN loc_agg la ON la.tenant_id = t.id LEFT JOIN adj ON adj.tenant_id = t.id LEFT JOIN dec31 ON dec31.tenant_id = t.id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ORDER BY dec31_correction_abs_units DESC NULLS FIRST, tenant_name NULLS LAST, t.id 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: Do B2B wholesale merchants get routine stock counts, or only the annual catch-up? Question: Do the B2B wholesale/office-supply merchants behind the December 31 correction run any routine, day-to-day stock-count activity during the year, or does their stock sit untouched in bulk storage until a single year-end true-up? Grain: tenant, restricted to the 8 merchants identified in last week's 'Which merchants drove the December 31 correction' report (Deskline Supplies, OfficeHub Nordic, Kontor24 Bürobedarf, Hanse Industriebedarf, Castell Fasteners, Midland Safety Wear, Ballycorran Industrial Supply, Polder Packaging) Columns: - tenant_name - total_stock_locations (count of stock_locations across this tenant's warehouses) - pct_locations_kind_bulk (% of those locations with kind='bulk') - pct_locations_kind_pick (% of those locations with kind='pick') - routine_stock_adjustments_2025 (count of stock_adjustments rows at this tenant's locations, created_at in 2025) - distinct_variants_with_routine_adjustment - dec31_correction_abs_units (sum of abs(quantity) from stock_movements where kind='adjustment' and occurred_at::date = '2025-12-31', at this tenant's locations) - dec31_correction_distinct_variants Details: Join stock_locations to warehouses (warehouse_id) and warehouses to tenants (tenant_id), filtered to the 8 named tenants. routine_stock_adjustments_2025: stock_adjustments where created_at between 2025-01-01 and 2025-12-31 (half-open bound), joined via stock_location_id to the tenant's locations. dec31_correction columns: stock_movements where kind='adjustment' and occurred_at falls on 2025-12-31 specifically (the date of the batch identified previously), joined via stock_location_id. Order by dec31_correction_abs_units descending, tie-break by tenant_name. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH loc AS ( SELECT sl.id AS stock_location_id, w.tenant_id, sl.kind FROM stock_locations sl JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ), loc_agg AS ( SELECT tenant_id, count(*) AS total_stock_locations, count(*) FILTER (WHERE kind = 'bulk') AS bulk_count, count(*) FILTER (WHERE kind = 'pick') AS pick_count FROM loc GROUP BY tenant_id ), adj AS ( SELECT l.tenant_id, count(*) AS routine_stock_adjustments_2025, count(DISTINCT sa.variant_id) AS distinct_variants_with_routine_adjustment FROM stock_adjustments sa JOIN loc l ON l.stock_location_id = sa.stock_location_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY l.tenant_id ), dec31 AS ( SELECT l.tenant_id, sum(abs(sm.quantity)) AS dec31_correction_abs_units, count(DISTINCT sm.variant_id) AS dec31_correction_distinct_variants FROM stock_movements sm JOIN loc l ON l.stock_location_id = sm.stock_location_id WHERE sm.kind = 'adjustment' AND sm.occurred_at::date = DATE '2025-12-31' GROUP BY l.tenant_id ) SELECT t.name AS tenant_name, la.total_stock_locations, round(100.0 * la.bulk_count / la.total_stock_locations, 1) AS pct_locations_kind_bulk, round(100.0 * la.pick_count / la.total_stock_locations, 1) AS pct_locations_kind_pick, coalesce(adj.routine_stock_adjustments_2025, 0) AS routine_stock_adjustments_2025, coalesce(adj.distinct_variants_with_routine_adjustment, 0) AS distinct_variants_with_routine_adjustment, coalesce(dec31.dec31_correction_abs_units, 0) AS dec31_correction_abs_units, coalesce(dec31.dec31_correction_distinct_variants, 0) AS dec31_correction_distinct_variants FROM tenants t JOIN loc_agg la ON la.tenant_id = t.id LEFT JOIN adj ON adj.tenant_id = t.id LEFT JOIN dec31 ON dec31.tenant_id = t.id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ORDER BY dec31_correction_abs_units DESC, tenant_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, total_stock_locations, pct_locations_kind_bulk, pct_locations_kind_pick, routine_stock_adjustments_2025, distinct_variants_with_routine_adjustment, dec31_correction_abs_units, dec31_correction_distinct_variants
submitWITH loc AS ( SELECT sl.id AS stock_location_id, w.tenant_id, sl.kind FROM stock_locations sl JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ), loc_agg AS ( SELECT tenant_id, count(*) AS total_stock_locations, count(*) FILTER (WHERE kind = 'bulk') AS bulk_count, count(*) FILTER (WHERE kind = 'pick') AS pick_count FROM loc GROUP BY tenant_id ), adj AS ( SELECT l.tenant_id, count(*) AS routine_stock_adjustments_2025, count(DISTINCT sa.variant_id) AS distinct_variants_with_routine_adjustment FROM stock_adjustments sa JOIN loc l ON l.stock_location_id = sa.stock_location_id WHERE sa.created_at >= DATE '2025-01-01' AND sa.created_at < DATE '2026-01-01' GROUP BY l.tenant_id ), dec31 AS ( SELECT l.tenant_id, sum(abs(sm.quantity)) AS dec31_correction_abs_units, count(DISTINCT sm.variant_id) AS dec31_correction_distinct_variants FROM stock_movements sm JOIN loc l ON l.stock_location_id = sm.stock_location_id WHERE sm.kind = 'adjustment' AND sm.occurred_at::date = DATE '2025-12-31' GROUP BY l.tenant_id ) SELECT t.name AS tenant_name, la.total_stock_locations, round(100.0 * la.bulk_count / la.total_stock_locations, 1) AS pct_locations_kind_bulk, round(100.0 * la.pick_count / la.total_stock_locations, 1) AS pct_locations_kind_pick, coalesce(adj.routine_stock_adjustments_2025, 0) AS routine_stock_adjustments_2025, coalesce(adj.distinct_variants_with_routine_adjustment, 0) AS distinct_variants_with_routine_adjustment, coalesce(dec31.dec31_correction_abs_units, 0) AS dec31_correction_abs_units, coalesce(dec31.dec31_correction_distinct_variants, 0) AS dec31_correction_distinct_variants FROM tenants t JOIN loc_agg la ON la.tenant_id = t.id LEFT JOIN adj ON adj.tenant_id = t.id LEFT JOIN dec31 ON dec31.tenant_id = t.id WHERE t.name IN ('Deskline Supplies', 'OfficeHub Nordic', 'Kontor24 Bürobedarf', 'Hanse Industriebedarf', 'Castell Fasteners', 'Midland Safety Wear', 'Ballycorran Industrial Supply', 'Polder Packaging') ORDER BY dec31_correction_abs_units DESC, tenant_name
Submitted to tonight's batch. Your work on this request is done.