Queries · q190 · Director of Fulfilment Operations · week 1, night 3

Scale of adjustment-kind stock movements by month, 2025

“Unlike cycle_count (always exactly 1 unit), kind='adjustment' movements vary widely (avg ~10 units, max 192) — what is the monthly scale, direction (shrinkage vs gain) and estimated value of this stream, and how does it trend across 2025?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

1. 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

2. Tomasz Nowak writes to Fatima Zahra

   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:

   1. How big is the gap between what we think we have and what's actually there? Across our warehouses, how often do physical counts come back different from the stock we had on record, and how large are those differences in units (and ideally value)? I want a sense of scale before anything else.
   2. Where is it worst? Does the mismatch concentrate in particular warehouses, or particular kinds of storage locations (e.g. bulk shelving vs. pick bins)? If a handful of sites or location types account for most of the problem, that tells us where to send help first.
   3. Is it a product or a process problem? Are the discrepancies spread evenly across products, or concentrated in certain categories, high-velocity items, or items we handle in lots/serials? This will tell Tobias and me whether this is a handling issue or a tracking-method issue.
   4. Are our "soft holds" on stock reliable? When we reserve stock for an order, does that reservation actually get fulfilled as expected, or do we see a lot of stuck/expired holds that quietly overstate what's available to sell? This matters for whether we're overpromising stock to customers.
   5. Is this getting better or worse over time? A simple trend over the last several months would tell us whether recent process changes are helping or whether the problem is growing — useful context for the auditors either way.

   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

   Show the whole note
   A note from the Chief Financial Officer, Tobias Lindqvist

   Subject: Finance questions for the inventory accuracy review

   Hi Fatima,

   A few things from the finance side as you dig into this with Tomasz:

   1. Do stock discrepancies cluster around particular merchants or warehouses in a way that could point to a write-off or valuation exposure we need to flag in the annual accounts?
   2. When counts don't match recorded stock, is it skewed more toward unexplained losses (shrinkage) versus unexplained gains — and has that pattern shifted over the year? That matters for how we size a shrinkage provision.
   3. Are the mismatches concentrated in slower-moving or higher-value goods, or spread evenly? That shapes how material this is for the merchants we'd flag to auditors.

   Keep it high level for now — happy to go deeper once you have an initial read.

   Thanks, Tobias

3. Fatima Zahra plans the night

   The analyst · week 1, night 3, request 1 of 3

   Night 2's magnitude check (report 1) landed a bigger finding than expected: cycle_count movements are literally always exactly ±1 unit (min=max=1, 100% of 10,193 events) — confirmed, not a query artifact, since adjustment/receipt/return/shipment all show wide, varied distributions in the same query. That means my night-1 "scale of the problem" was built entirely on a stream that, by construction, can never show a large miss. Meanwhile kind='adjustment' events are rare (407 in the year) but average ~10 units and run up to 192 units per event — a completely different animal, and likely the stream that actually matters for Tobias's write-off/valuation question. I under-covered this on night 1 and need to correct course before locking the memo.

   Also from night 2: report 2 confirms cycle-count activity is 100% in 'pick' locations (120 of 157 pick locations active, 0 of 157 bulk, 0 of 157 returns locations) — a clean, decisive answer to "which location kind" for that stream, so I don't need to spend more on that question. And report 3 shows reservations are overwhelmingly reliable (99.3% released_on_time, only 0.3% ever stuck past expiry) — Q4 looks like a non-issue, I'll give it one more sanity pass later if time allows but it's not using a slot tonight.

   Tonight I'm redirecting all three slots to properly characterize the 'adjustment' stream, since it's likely the financially material one: (1) its own monthly scale/trend, parallel to night 1's cycle_count view, so I can compare the two streams side by side in the memo; (2) the actual top individual events (only 407 total, so a top-30 detail table efficiently reveals whether size concentrates by warehouse/tenant/category/product all at once, rather than slicing a thin 407-row base three more ways); (3) a definitional cross-check against the separate stock_adjustments table, since that table is described as the human-entered correction record with a reason, and I want to confirm it's the same universe as stock_movements kind='adjustment' before I tell auditors which number is authoritative.

   Read the whole plan
   Scale of adjustment-kind stock movements by month, 2025

   Question

   Unlike cycle_count (always exactly 1 unit), kind='adjustment' movements vary widely (avg ~10 units, max 192) — what is the monthly scale, direction (shrinkage vs gain) and estimated value of this stream, and how does it trend across 2025?

   One row per

   calendar month (Jan 2025 - Dec 2025)

   Columns

   • month (YYYY-MM)
   • adjustment_events (count of stock_movements rows with kind='adjustment')
   • units_short (sum of abs(quantity) where quantity<0)
   • units_over (sum of quantity where quantity>0)
   • net_units_variance (signed sum; negative = net shrinkage)
   • abs_units_variance (sum of abs(quantity))
   • estimated_value_eur (sum of abs(quantity) * unit cost, converted to EUR — same costing/FX method as prior reports: preferred-then-cheapest supplier_products.unit_cost, converted via exchange_rates on the movement's occurred_at date)
   • avg_abs_units_per_event (rounded 1dp)

4. Ines writes the SQL

   The engineer · 2 dry runs on the empty schema, then the submission

   Dry run 1 · STATUS: REJECTED

   WITH variant_cost AS (
     SELECT sp.variant_id, sp.unit_cost, sp.currency_code,
       ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn
     FROM supplier_products sp
     WHERE sp.unit_cost IS NOT NULL
   ),
   best_cost AS (
     SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1
   ),
   movements AS (
     SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,
            CAST(sm.occurred_at AS date) AS occ_date
     FROM stock_movements sm
     WHERE sm.kind = 'adjustment'
       AND sm.occurred_at >= DATE '2025-01-01'
       AND sm.occurred_at < DATE '2026-01-01'
   ),
   valued AS (
     SELECT m.id, m.quantity,
       date_trunc('month', m.occurred_at) AS month_start,
       CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost
            WHEN bc.currency_code IS NULL THEN NULL
            ELSE bc.unit_cost / er.rate
       END AS unit_cost_eur
     FROM movements m
     LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date
   )
   SELECT
     to_char(month_start, 'YYYY-MM') AS month,
     COUNT(*) AS adjustment_events,
     SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,
     SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,
     SUM(quantity) AS net_units_variance,
     SUM(ABS(quantity)) AS abs_units_variance,
     ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,
     ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_event
   FROM valued
   GROUP BY month_start
   ORDER BY month_start

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

   WITH variant_cost AS (  SELECT sp.variant_id, sp.unit_cost, sp.currency_code,    ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn  FROM supplier_products sp  WHERE sp.unit_cost IS NOT NULL),best_cost AS (  SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1),movements AS (  SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,         CAST(sm.occurred_at AS date) AS occ_date  FROM stock_movements sm  WHERE sm.kind = 'adjustment'    AND sm.occurred_at >= DATE '2025-01-01'    AND sm.occurred_at < DATE '2026-01-01'),valued AS (  SELECT m.id, m.quantity,    date_trunc('month', m.occurred_at) AS month_start,    CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost         WHEN bc.currency_code IS NULL THEN NULL         ELSE bc.unit_cost / er.rate    END AS unit_cost_eur  FROM movements m  LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date)SELECT  to_char(month_start, 'YYYY-MM') AS month,  strftime(month_start, '%Y-%m') AS month,  COUNT(*) AS adjustment_events,  SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,  SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,  SUM(quantity) AS net_units_variance,  SUM(ABS(quantity)) AS abs_units_variance,  ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,  ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_eventFROM valuedGROUP BY month_startORDER BY month_start

   “Query compiles. Submitting.”

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

   “Submitted. Summary of approach: - Grain: one row per calendar month (2025-01 … 2025-12), restricted to stock_movements with kind='adjustment' and occurred_at in a half-open 2025 window. - Direction/scale: units_short (sum of negative quantities, made positive) and units_over (sum of positive quantities) separate shrinkage from gains; net_units_variance keeps the sign (negative = net shrinkage), abs_units_variance is the total movement scale regardless of direction. - Costing/FX: for each variant, picked the unit cost from supplier_products preferring is_preferred=true, falling back to the cheapest unit_cost otherwise (via ROW_NUMBER() OVER (... ORDER BY is_preferred DESC, unit_cost ASC)), matching the method used in the earlier stock-count-discrepancy report. Converted that cost to EUR using exchange_rates (base_code='EUR', quote_code = supplier's currency) as of the movement's own date. - estimated_value_eur = sum of abs(quantity) * unit_cost_eur; variants lacking any supplier cost contribute 0 to this value (flagged in the submit note) but are still counted in the unit/event columns. - Ordered ascending by month for trend reading.”

5. The report comes back overnight

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

   month   | adjustment_events | units_short | units_over | net_units_variance | abs_units_variance | estimated_value_eur | avg_abs_units_per_event
   --------+-------------------+-------------+------------+--------------------+--------------------+---------------------+------------------------
   2025-05 |                 1 |           1 |          0 |                 -1 |                  1 |              117.50 |                     1.0
   2025-12 |               406 |           0 |       4066 |               4066 |               4066 |           138023.65 |                    10.0
   (2 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q192Cross-check: stock_adjustments table vs stock_movements adjustment-kind ledger, 2025
   • q194Which merchants drove the December 31 bulk stock correction
   • q395Is the December 31 correction one single batch event, or genuinely independent per merchant?

   It builds on:

   • q184Scale of stock count discrepancies by month
   • q187Stock movement magnitude by kind, 2025

7. It becomes benchmark query q190

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

   • spellingcast (2×)
   • placeholderyear month
   • determinismtie-breaker: (supplier_id, variant_id) is unique in supplier_products
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit (9×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
   The benchmark text, against the submitted SQL

   WITH variant_cost AS (  SELECT sp.variant_id, sp.unit_cost, sp.currency_code,    ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn  SELECT sp.variant_id AS variant_id, sp.unit_cost AS unit_cost, sp.currency_code AS currency_code,    ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC NULLS FIRST, sp.unit_cost ASC NULLS LAST, sp.supplier_id NULLS LAST) AS rn  FROM supplier_products sp  WHERE sp.unit_cost IS NOT NULL),best_cost AS (  SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1),movements AS (  SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,  SELECT sm.id AS id, sm.variant_id AS variant_id, sm.quantity AS quantity, sm.occurred_at AS occurred_at,         CAST(sm.occurred_at AS date) AS occ_date  FROM stock_movements sm  WHERE sm.kind = 'adjustment'    AND sm.occurred_at >= DATE '2025-01-01'    AND sm.occurred_at < DATE '2026-01-01'),valued AS (  SELECT m.id, m.quantity,  SELECT m.id AS id, m.quantity AS quantity,    date_trunc('month', m.occurred_at) AS month_start,    CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost         WHEN bc.currency_code IS NULL THEN NULL         ELSE bc.unit_cost / er.rate         ELSE CAST(bc.unit_cost / er.rate AS NUMERIC(18,6))    END AS unit_cost_eur  FROM movements m  LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date)SELECT  strftime(month_start, '%Y-%m') AS month,  {{year_month(month_start)}} AS month,  COUNT(*) AS adjustment_events,  SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,  SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,  SUM(quantity) AS net_units_variance,  SUM(ABS(quantity)) AS abs_units_variance,  ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,  ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_event  SUM(ABS(quantity) * COALESCE(unit_cost_eur,0)) AS estimated_value_eur,  AVG(CAST(ABS(quantity) AS NUMERIC(18,6))) AS avg_abs_units_per_eventFROM valuedGROUP BY month_startORDER BY month_startORDER BY month_start NULLS LAST

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

   DuckDB · GizmoSQLHyper · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewCedarDBClickHouse · chDB

   WITH variant_cost AS (
     SELECT sp.variant_id AS variant_id, sp.unit_cost AS unit_cost, sp.currency_code AS currency_code,
       ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC NULLS FIRST, sp.unit_cost ASC NULLS LAST, sp.supplier_id NULLS LAST) AS rn
     FROM supplier_products sp
     WHERE sp.unit_cost IS NOT NULL
   ),
   best_cost AS (
     SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1
   ),
   movements AS (
     SELECT sm.id AS id, sm.variant_id AS variant_id, sm.quantity AS quantity, sm.occurred_at AS occurred_at,
            CAST(sm.occurred_at AS date) AS occ_date
     FROM stock_movements sm
     WHERE sm.kind = 'adjustment'
       AND sm.occurred_at >= DATE '2025-01-01'
       AND sm.occurred_at < DATE '2026-01-01'
   ),
   valued AS (
     SELECT m.id AS id, m.quantity AS quantity,
       date_trunc('month', m.occurred_at) AS month_start,
       CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost
            WHEN bc.currency_code IS NULL THEN NULL
            ELSE CAST(bc.unit_cost / er.rate AS NUMERIC(18,6))
       END AS unit_cost_eur
     FROM movements m
     LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date
   )
   SELECT
     strftime(month_start, '%Y-%m') AS month,
     COUNT(*) AS adjustment_events,
     SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,
     SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,
     SUM(quantity) AS net_units_variance,
     SUM(ABS(quantity)) AS abs_units_variance,
     SUM(ABS(quantity) * COALESCE(unit_cost_eur,0)) AS estimated_value_eur,
     AVG(CAST(ABS(quantity) AS NUMERIC(18,6))) AS avg_abs_units_per_event
   FROM valued
   GROUP BY month_start
   ORDER BY month_start NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 23, Column 33: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "ccb9f149-2196-48df-aa53-ec6c34bcf270", "query_lab
   • DataFusionrow 1: col 1: "2025-12" vs "YYYY-MM"
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.unit_cost Nullable(Decimal(14, 4)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)), __table7.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0

   Scale factor 1000, c8a.24xlarge, after the last load. q190 on the start page, with every configuration.

8. 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

   Ines's whole session, 9 turns

   • The request, as Ines received it

     Report request from Fatima Zahra (analyst for the Director of Fulfilment Operations):
     
     Title: Scale of adjustment-kind stock movements by month, 2025
     Question: Unlike cycle_count (always exactly 1 unit), kind='adjustment' movements vary widely (avg ~10 units, max 192) — what is the monthly scale, direction (shrinkage vs gain) and estimated value of this stream, and how does it trend across 2025?
     Grain: calendar month (Jan 2025 - Dec 2025)
     Columns:
     - month (YYYY-MM)
     - adjustment_events (count of stock_movements rows with kind='adjustment')
     - units_short (sum of abs(quantity) where quantity<0)
     - units_over (sum of quantity where quantity>0)
     - net_units_variance (signed sum; negative = net shrinkage)
     - abs_units_variance (sum of abs(quantity))
     - estimated_value_eur (sum of abs(quantity) * unit cost, converted to EUR — same costing/FX method as prior reports: preferred-then-cheapest supplier_products.unit_cost, converted via exchange_rates on the movement's occurred_at date)
     - avg_abs_units_per_event (rounded 1dp)
     Details: Source: stock_movements where kind='adjustment', occurred_at in 2025-01-01 to 2025-12-31 (use a half-open bound occurred_at >= 2025-01-01 and < 2026-01-01 as done previously). Same costing/FX approach as the 'Scale of stock count discrepancies by month' report from night 1. Order by month ascending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     WITH variant_cost AS (
       SELECT sp.variant_id, sp.unit_cost, sp.currency_code,
         ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn
       FROM supplier_products sp
       WHERE sp.unit_cost IS NOT NULL
     ),
     best_cost AS (
       SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1
     ),
     movements AS (
       SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,
              CAST(sm.occurred_at AS date) AS occ_date
       FROM stock_movements sm
       WHERE sm.kind = 'adjustment'
         AND sm.occurred_at >= DATE '2025-01-01'
         AND sm.occurred_at < DATE '2026-01-01'
     ),
     valued AS (
       SELECT m.id, m.quantity,
         date_trunc('month', m.occurred_at) AS month_start,
         CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost
              WHEN bc.currency_code IS NULL THEN NULL
              ELSE bc.unit_cost / er.rate
         END AS unit_cost_eur
       FROM movements m
       LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date
     )
     SELECT
       to_char(month_start, 'YYYY-MM') AS month,
       COUNT(*) AS adjustment_events,
       SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,
       SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,
       SUM(quantity) AS net_units_variance,
       SUM(ABS(quantity)) AS abs_units_variance,
       ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,
       ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_event
     FROM valued
     GROUP BY month_start
     ORDER BY month_start

   • The warehouse answers

     STATUS: REJECTED
       - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

   • Ines calls dry_run

     WITH variant_cost AS (
       SELECT sp.variant_id, sp.unit_cost, sp.currency_code,
         ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn
       FROM supplier_products sp
       WHERE sp.unit_cost IS NOT NULL
     ),
     best_cost AS (
       SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1
     ),
     movements AS (
       SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,
              CAST(sm.occurred_at AS date) AS occ_date
       FROM stock_movements sm
       WHERE sm.kind = 'adjustment'
         AND sm.occurred_at >= DATE '2025-01-01'
         AND sm.occurred_at < DATE '2026-01-01'
     ),
     valued AS (
       SELECT m.id, m.quantity,
         date_trunc('month', m.occurred_at) AS month_start,
         CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost
              WHEN bc.currency_code IS NULL THEN NULL
              ELSE bc.unit_cost / er.rate
         END AS unit_cost_eur
       FROM movements m
       LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date
     )
     SELECT
       strftime(month_start, '%Y-%m') AS month,
       COUNT(*) AS adjustment_events,
       SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,
       SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,
       SUM(quantity) AS net_units_variance,
       SUM(ABS(quantity)) AS abs_units_variance,
       ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,
       ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_event
     FROM valued
     GROUP BY month_start
     ORDER BY month_start

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: month, adjustment_events, units_short, units_over, net_units_variance, abs_units_variance, estimated_value_eur, avg_abs_units_per_event

   • InesQuery compiles. Submitting.
   • Ines calls submit

     WITH variant_cost AS (
       SELECT sp.variant_id, sp.unit_cost, sp.currency_code,
         ROW_NUMBER() OVER (PARTITION BY sp.variant_id ORDER BY sp.is_preferred DESC, sp.unit_cost ASC) AS rn
       FROM supplier_products sp
       WHERE sp.unit_cost IS NOT NULL
     ),
     best_cost AS (
       SELECT variant_id, unit_cost, currency_code FROM variant_cost WHERE rn = 1
     ),
     movements AS (
       SELECT sm.id, sm.variant_id, sm.quantity, sm.occurred_at,
              CAST(sm.occurred_at AS date) AS occ_date
       FROM stock_movements sm
       WHERE sm.kind = 'adjustment'
         AND sm.occurred_at >= DATE '2025-01-01'
         AND sm.occurred_at < DATE '2026-01-01'
     ),
     valued AS (
       SELECT m.id, m.quantity,
         date_trunc('month', m.occurred_at) AS month_start,
         CASE WHEN bc.currency_code = 'EUR' THEN bc.unit_cost
              WHEN bc.currency_code IS NULL THEN NULL
              ELSE bc.unit_cost / er.rate
         END AS unit_cost_eur
       FROM movements m
       LEFT JOIN best_cost bc ON bc.variant_id = m.variant_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR' AND er.quote_code = bc.currency_code AND er.as_of = m.occ_date
     )
     SELECT
       strftime(month_start, '%Y-%m') AS month,
       COUNT(*) AS adjustment_events,
       SUM(CASE WHEN quantity < 0 THEN -quantity ELSE 0 END) AS units_short,
       SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS units_over,
       SUM(quantity) AS net_units_variance,
       SUM(ABS(quantity)) AS abs_units_variance,
       ROUND(SUM(ABS(quantity) * COALESCE(unit_cost_eur,0))::numeric, 2) AS estimated_value_eur,
       ROUND(AVG(ABS(quantity))::numeric, 1) AS avg_abs_units_per_event
     FROM valued
     GROUP BY month_start
     ORDER BY month_start

   • The warehouse answers

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

   • InesSubmitted. Summary of approach: - **Grain:** one row per calendar month (2025-01 … 2025-12), restricted to `stock_movements` with `kind='adjustment'` and `occurred_at` in a half-open 2025 window. - **Direction/scale:** `units_short` (sum of negative quantities, made positive) and `units_over` (sum of positive quantities) separate shrinkage from gains; `net_units_variance` keeps the sign (negative = net shrinkage), `abs_units_variance` is the total movement scale regardless of direction. - **Costing/FX:** for each variant, picked the unit cost from `supplier_products` preferring `is_preferred=true`, falling back to the cheapest `unit_cost` otherwise (via `ROW_NUMBER() OVER (... ORDER BY is_preferred DESC, unit_cost ASC)`), matching the method used in the earlier stock-count-discrepancy report. Converted that cost to EUR using `exchange_rates` (`base_code='EUR'`, `quote_code` = supplier's currency) as of the movement's own date. - **estimated_value_eur** = sum of `abs(quantity) * unit_cost_eur`; variants lacking any supplier cost contribute 0 to this value (flagged in the submit note) but are still counted in the unit/event columns. - Ordered ascending by month for trend reading.

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

   Ines's transcript (277 KB) · Fatima Zahra's transcript (935 KB) · Tomasz Nowak's transcript (261 KB) · the night's plan (6 KB) · the night's reports (10 KB) · the week's memo (3 KB)