Queries · q194 · Director of Fulfilment Operations · week 1, night 4
“The 407 stock_movements kind='adjustment' events (nearly all on 2025-12-31) appeared, in the top-30 detail, to be entirely B2B wholesale merchants (office equipment / industrial supplies) — is that concentration total, or do other merchants/verticals also appear further down the list?”
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 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:
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
Subject: Finance questions for the inventory accuracy review
Hi Fatima,
A few things from the finance side as you dig into this with Tomasz:
Keep it high level for now — happy to go deeper once you have an initial read.
Thanks, Tobias
The analyst · week 1, night 4, request 2 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.
The engineer · 1 dry run on the empty schema, then the submission
SELECT sub.tenant_name, sub.industry, sub.adjustment_events, sub.abs_units_variance, sub.net_units_variance, sub.estimated_value_eur, ROUND(sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER (), 2) AS share_of_total_value_pct FROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, ROUND(SUM( ABS(sm.quantity) * COALESCE( (SELECT sp.unit_cost / er.rate FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id LIMIT 1 ), 0) ), 2) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry ) sub ORDER BY sub.estimated_value_eur DESC, sub.tenant_name ASC
“Query compiles cleanly. Submitting.”
“Submitted. Summary of the approach:
- Grain: one row per tenant with ≥1 kind='adjustment' stock_movements event in 2025 (occurred_at in [2025-01-01, 2026-01-01)).
- Attribution: stock_movements → stock_locations → warehouses → tenants, with tenants.industry pulled directly, as requested.
- Measures: event count, sum of absolute quantity (abs_units_variance), signed sum (net_units_variance), and an estimated EUR value (absolute units × preferred-supplier unit cost, converted to EUR using the exchange_rates row for that day).
- Ordering: by estimated_value_eur descending, tie-broken by tenant_name, which will reveal whether the correlation with B2B wholesale is total or whether other verticals show up further down.
One caveat flagged to the analyst: I didn't have visibility into the exact costing/FX method used in prior reports, so I used preferred-supplier unit_cost converted via the exact-date EUR exchange rate as the closest reasonable match, and noted that in the submission so they can confirm or ask for an adjustment.”
As Fatima Zahra saw it, from the small dataset the agents worked on
tenant_name | industry | adjustment_events | abs_units_variance | net_units_variance | estimated_value_eur | share_of_total_value_pct ------------------------------+---------------------------+-------------------+--------------------+--------------------+---------------------+------------------------- Deskline Supplies | Office Supplies (B2B) | 17 | 288 | 288 | 30530.59 | 35.62 OfficeHub Nordic | Office Supplies (B2B) | 29 | 429 | 429 | 21346.34 | 24.9 Castell Fasteners | Industrial Supplies (B2B) | 28 | 704 | 704 | 11650.17 | 13.59 Hanse Industriebedarf | Industrial Supplies (B2B) | 20 | 458 | 458 | 10935.72 | 12.76 Midland Safety Wear | Industrial Supplies (B2B) | 16 | 440 | 440 | 6398.18 | 7.46 Ballycorran Industrial Supply | Industrial Supplies (B2B) | 58 | 1058 | 1058 | 2099.86 | 2.45 Polder Packaging | Industrial Supplies (B2B) | 8 | 134 | 134 | 580.01 | 0.68 Marrowby & Vane | Apparel & Footwear | 20 | 30 | 30 | 427.23 | 0.5 Cragsley Outfitters | Apparel & Footwear | 34 | 45 | 45 | 408.69 | 0.48 Fennel & Flint | Home & Living | 9 | 12 | 10 | 293.09 | 0.34 Kontor24 Bürobedarf | Office Supplies (B2B) | 25 | 285 | 285 | 191.03 | 0.22 Basecamp Nord | Sports & Outdoor | 2 | 2 | 2 | 106.64 | 0.12 Willow & Wren | Home & Living | 3 | 6 | 6 | 84.27 | 0.1 Grind Collective | Food & Coffee | 5 | 8 | 8 | 74.27 | 0.09 Sourdough Club | Food & Coffee | 5 | 8 | 8 | 68.56 | 0.08 Kibblewick | Pet Supplies | 3 | 5 | 5 | 48.77 | 0.06 Honeycomb Larder | Food & Coffee | 3 | 6 | 6 | 40.79 | 0.05 Maison Clair | Apparel & Footwear | 1 | 1 | 1 | 38.5 | 0.04 Purrfect Pantry | Pet Supplies | 3 | 4 | 4 | 34.02 | 0.04 Buchstabe | Books & Stationery | 3 | 4 | 4 | 31.47 | 0.04 Linden & Loom | Home & Living | 6 | 8 | 8 | 29.59 | 0.03 Saffron & Sage | Beauty | 2 | 2 | 2 | 26.01 | 0.03 Cinder Peak Sports Co. | Sports & Outdoor | 2 | 3 | 3 | 25.67 | 0.03 Brennquell Kaffee | Food & Coffee | 27 | 34 | 34 | 23.52 | 0.03 Oakhurst Tailoring | Apparel & Footwear | 2 | 2 | 2 | 22.72 | 0.03 Hus & Hem | Home & Living | 1 | 1 | 1 | 21.9 | 0.03 Rue Lafayette Studio | Apparel & Footwear | 3 | 3 | 3 | 20.37 | 0.02 Studio Vrij | Apparel & Footwear | 9 | 11 | 11 | 19.74 | 0.02 Nordic Bloom Cosmetics | Beauty | 3 | 3 | 3 | 15.35 | 0.02 Kaffeekontor Nord | Food & Coffee | 1 | 2 | 2 | 15.32 | 0.02 Wohnglück | Home & Living | 3 | 3 | 3 | 15.1 | 0.02 Juniper Apothecary | Beauty | 4 | 4 | 4 | 15.05 | 0.02 The Balm Room | Beauty | 2 | 2 | 2 | 12.38 | 0.01 Petalcourt Beauty | Beauty | 3 | 3 | 3 | 12.0 | 0.01 Kayak Corner | Sports & Outdoor | 2 | 2 | 2 | 11.76 | 0.01 Gratlinie Outdoor | Sports & Outdoor | 3 | 5 | 5 | 11.4 | 0.01 Coastline Interiors | Home & Living | 1 | 1 | 1 | 10.33 | 0.01 Feather & Fin | Pet Supplies | 1 | 1 | 1 | 6.1 | 0.01 Rosehip Lab | Beauty | 1 | 1 | 1 | 4.26 | 0.0 Velvet Ash | Apparel & Footwear | 3 | 4 | 4 | 4.15 | 0.0 Tail Wag Supply | Pet Supplies | 1 | 1 | 1 | 3.81 | 0.0 Folio & Fern | Books & Stationery | 2 | 3 | 3 | 3.37 | 0.0 Ostwind Mode | Apparel & Footwear | 2 | 2 | 2 | 3.03 | 0.0 Ampere & Co. | Consumer Electronics | 1 | 1 | 1 | 0.0 | 0.0 Ampfeld Elektronik | Consumer Electronics | 5 | 8 | 8 | 0.0 | 0.0 Atelier Nouveau | Apparel & Footwear | 1 | 1 | 1 | 0.0 | 0.0 Bijoux Lune | Jewelry & Accessories | 2 | 2 | 2 | 0.0 | 0.0 Chocolaterie Mireille | Food & Coffee | 1 | 2 | 2 | 0.0 | 0.0 Hautnah | Beauty | 1 | 1 | 1 | 0.0 | 0.0 Hound Hamper | Pet Supplies | 2 | 2 | 2 | 0.0 | 0.0 (59 rows, first 50 printed)
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
SELECT sub.tenant_name, sub.industry, sub.adjustment_events, sub.abs_units_variance, sub.net_units_variance, sub.estimated_value_eur, ROUND(sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER (), 2) AS share_of_total_value_pct sub.tenant_name AS tenant_name, sub.industry AS industry, sub.adjustment_events AS adjustment_events, sub.abs_units_variance AS abs_units_variance, sub.net_units_variance AS net_units_variance, sub.estimated_value_eur AS estimated_value_eur, sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER () AS share_of_total_value_pctFROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, ROUND(SUM( ABS(sm.quantity) * COALESCE( (SELECT sp.unit_cost / er.rate FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id LIMIT 1 ), 0) ), 2) AS estimated_value_eur SUM(ABS(sm.quantity) * COALESCE( (SELECT CAST(sp.unit_cost / er.rate AS NUMERIC(18,6)) FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id NULLS LAST {{fetch_first(1)}} ), 0)) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry) subORDER BY sub.estimated_value_eur DESC, sub.tenant_name ASCORDER BY sub.estimated_value_eur DESC NULLS FIRST, sub.tenant_name ASC NULLS LAST, sub.industry NULLS LAST, sub.adjustment_events NULLS LAST, sub.abs_units_variance NULLS LAST, sub.net_units_variance NULLS LAST
SELECT sub.tenant_name AS tenant_name, sub.industry AS industry, sub.adjustment_events AS adjustment_events, sub.abs_units_variance AS abs_units_variance, sub.net_units_variance AS net_units_variance, sub.estimated_value_eur AS estimated_value_eur, sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER () AS share_of_total_value_pct FROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, SUM( ABS(sm.quantity) * COALESCE( (SELECT CAST(sp.unit_cost / er.rate AS NUMERIC(18,6)) FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id NULLS LAST FETCH FIRST 1 ROWS ONLY ), 0) ) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry ) sub ORDER BY sub.estimated_value_eur DESC NULLS FIRST, sub.tenant_name ASC NULLS LAST, sub.industry NULLS LAST, sub.adjustment_events NULLS LAST, sub.abs_units_variance NULLS LAST, sub.net_units_variance NULLS LAST
SELECT sub.tenant_name AS tenant_name, sub.industry AS industry, sub.adjustment_events AS adjustment_events, sub.abs_units_variance AS abs_units_variance, sub.net_units_variance AS net_units_variance, sub.estimated_value_eur AS estimated_value_eur, sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER () AS share_of_total_value_pct FROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, SUM( ABS(sm.quantity) * COALESCE( (SELECT CAST(sp.unit_cost / er.rate AS NUMERIC(18,6)) FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id NULLS LAST LIMIT 1 ), 0) ) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry ) sub ORDER BY sub.estimated_value_eur DESC NULLS FIRST, sub.tenant_name ASC NULLS LAST, sub.industry NULLS LAST, sub.adjustment_events NULLS LAST, sub.abs_units_variance NULLS LAST, sub.net_units_variance 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: Which merchants drove the December 31 bulk stock correction Question: The 407 stock_movements kind='adjustment' events (nearly all on 2025-12-31) appeared, in the top-30 detail, to be entirely B2B wholesale merchants (office equipment / industrial supplies) — is that concentration total, or do other merchants/verticals also appear further down the list? Grain: tenant (one row per tenant with at least one kind='adjustment' stock_movements event in 2025) Columns: - tenant_name - industry (tenants.industry) - adjustment_events - abs_units_variance - net_units_variance (signed; positive = net gain found) - estimated_value_eur (same costing/FX method as prior reports) - share_of_total_value_pct Details: Source: stock_movements where kind='adjustment', occurred_at between 2025-01-01 and 2025-12-31 (half-open bound). Join stock_locations -> warehouses -> tenants to attribute each event to its owning tenant; pull tenants.industry directly. Group by tenant. Order by estimated_value_eur descending, tie-break by tenant_name. Expect well under 50 distinct tenants (likely 10-20) — print all of them if under 50 and note the total count either way. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT sub.tenant_name, sub.industry, sub.adjustment_events, sub.abs_units_variance, sub.net_units_variance, sub.estimated_value_eur, ROUND(sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER (), 2) AS share_of_total_value_pct FROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, ROUND(SUM( ABS(sm.quantity) * COALESCE( (SELECT sp.unit_cost / er.rate FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id LIMIT 1 ), 0) ), 2) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry ) sub ORDER BY sub.estimated_value_eur DESC, sub.tenant_name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, industry, adjustment_events, abs_units_variance, net_units_variance, estimated_value_eur, share_of_total_value_pct
submitSELECT sub.tenant_name, sub.industry, sub.adjustment_events, sub.abs_units_variance, sub.net_units_variance, sub.estimated_value_eur, ROUND(sub.estimated_value_eur * 100.0 / SUM(sub.estimated_value_eur) OVER (), 2) AS share_of_total_value_pct FROM ( SELECT t.name AS tenant_name, t.industry AS industry, COUNT(*) AS adjustment_events, SUM(ABS(sm.quantity)) AS abs_units_variance, SUM(sm.quantity) AS net_units_variance, ROUND(SUM( ABS(sm.quantity) * COALESCE( (SELECT sp.unit_cost / er.rate FROM supplier_products sp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = sp.currency_code AND er.as_of = CAST(sm.occurred_at AS date) WHERE sp.variant_id = sm.variant_id AND sp.is_preferred = true AND sp.unit_cost IS NOT NULL ORDER BY sp.id LIMIT 1 ), 0) ), 2) AS estimated_value_eur FROM stock_movements sm JOIN stock_locations sl ON sl.id = sm.stock_location_id JOIN warehouses w ON w.id = sl.warehouse_id JOIN tenants t ON t.id = w.tenant_id WHERE sm.kind = 'adjustment' AND sm.occurred_at >= DATE '2025-01-01' AND sm.occurred_at < DATE '2026-01-01' GROUP BY t.id, t.name, t.industry ) sub ORDER BY sub.estimated_value_eur DESC, sub.tenant_name ASC
Submitted to tonight's batch. Your work on this request is done.