Queries · q310 · Head of Data · week 2, night 4

Final B2B wholesale receivables aging (corrected)

“With the full, verified 9-tenant B2B wholesale list and EUR handled correctly, what does the receivables aging actually look like for the board pack?”

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 Head of Data's desk

   Week 1; shared with Tobias Lindqvist, Chief Financial Officer

   Before the board pack goes out, Data must certify the key metrics. Do revenue, orders, customers, refunds and payouts agree wherever they are recorded, and where they do not, why not and by how much?

   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. Rosa Van den Berg writes to Yuki Tanaka

   The manager, at the start of week 2

   Subject: Good work — a few things I need pinned down before we certify

   Yuki,

   This is exactly the kind of digging I wanted — especially catching that invoices weren't a real fifth number, and that your first "20-40% gap" read was wrong and you went back to check it. That's the instinct I want on this team.

   Three things surprised me and need explaining before I can tell Tobias we're ready:

   1. Even with invoices out of the picture, order revenue, payments captured and payout gross still don't match each other (332,982 vs 323,026 vs 322,182 in your December example). You've explained orders-to-payments very well at the order level — but what explains that same gap when we look at it as one portfolio total for a month? Is it simply orders placed near month-end that haven't been paid yet, or something else?
   2. The refund/payout traceability gap worries me more than you've flagged it. Before we loop in engineering, I want a sense of scale and shape: is this refund-timing effect spread evenly across all merchants, or concentrated in a few — because if it's concentrated, "we can't point to which payout absorbed it" becomes a real merchant-facing payout-support problem, not just a reporting footnote.
   3. On customers — I don't want to just pick two numbers ourselves. I want to understand what each of those two counts is actually used for elsewhere in the business today, so we're not inventing a new definition the rest of the company doesn't already use.

   Questions for this week:

   • For the revenue gap between orders and money actually collected, at the portfolio level — can you tell me how much of it is pure timing (uncollected yet) versus something that never gets collected at all?
   • For the refund/payout gap — is it concentrated in particular merchants or spread thinly across all of them, and does the size of it grow or shrink over the past year?
   • Between the two customer counts, which one is already being used anywhere else in the business (support, billing, marketing), so we're not asking the board to adopt a brand-new definition?
   • On the B2B receivables — rather than spending a slot rebuilding the merchant list this week, can you give me a rough sense of whether wholesalers are large enough a slice of revenue that leaving that line out of Friday's pack is actually a problem?

   Keep the FX-adjusted view for USD/NOK on your list — that one's solid and the board will ask about it.

   Thanks for the pace you're keeping on this.

   Rosa

   Show the whole note

3. Yuki Tanaka plans the night

   The analyst · week 2, night 4, request 3 of 3

   Tonight's diagnosis solved the B2B mystery and it's bigger than expected. The native-currency report shows all 6 "zero" tenants have real, substantial activity (e.g. Ballycorran Industrial Supply: EUR, 136 real deals worth EUR575,400, 344 orders, 329 captured payments in 2025) - and the one thing all 6 broken tenants share is currency_code = EUR, while the 3 tenants that worked fine two nights ago are NOK, USD and GBP. That is conclusive: the prior query's EUR conversion silently dropped these tenants entirely, almost certainly via a join to exchange_rates for an EUR/EUR identity row that doesn't exist (several other engineers' notes this month explicitly called out and guarded against this exact gap, so it's a known but inconsistently-handled edge case, not a one-off fluke). This means my "B2B wholesale = 38.9% of revenue" number from two nights ago is a real understatement, not just imprecise, since it's missing two-thirds of the B2B tenant list entirely. The Castell Fasteners invoice detail reconciles reasonably well with Merchant Success's figure once you separate "total open AR" (EUR14,481) from "genuinely past due" (EUR~3,450, matching my 23.8% overdue share) - the remaining gap to their EUR5,080 is most likely snapshot-timing, not a real inconsistency, and I don't think it's worth another slot. The refund-share report shows Cragsley Outfitters refunds late at 94.2% vs an 83.7% company average - elevated but in the same range as several other retail peers (Velvet Ash 94.3%, Cinder Peak 93.7%), so it reads as "big merchant, slightly-above-average rate" rather than a uniquely broken merchant - closing that question out. Tonight's three slots: (1) redo the B2B revenue/receivables numbers with EUR correctly treated as identity (rate=1.0, no join needed) to get the real materiality figure, (2) quantify how much company-wide revenue this exact bug class would silently drop if it recurred, as hard evidence for Rosa's currency-handling question, (3) produce the final, corrected B2B receivables aging table for the board pack.

   Read the whole plan
   Final B2B wholesale receivables aging (corrected)

   Question

   With the full, verified 9-tenant B2B wholesale list and EUR handled correctly, what does the receivables aging actually look like for the board pack?

   One row per

   receivables aging bucket

   Columns

   • aging_bucket
   • invoice_count
   • amount_due_eur
   • pct_of_total_amount_due
   • avg_days_past_due

4. Ines 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)

   WITH target_tenants AS (
     SELECT id, name
     FROM tenants
     WHERE name IN (
       'Ballycorran Industrial Supply',
       'Kontor24 Burobedarf',
       'OfficeHub Nordic',
       'Midland Safety Wear',
       'Castell Fasteners',
       'Hanse Industriebedarf',
       'Polder Packaging',
       'Deskline Supplies',
       'Papeterie Grossiste'
     )
   ),
   rates AS (
     SELECT quote_code, rate
     FROM exchange_rates
     WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
   ),
   inv AS (
     SELECT
       i.id,
       i.amount_due,
       i.currency_code,
       i.due_at,
       CASE
         WHEN i.currency_code = 'EUR' THEN i.amount_due
         ELSE i.amount_due / r.rate
       END AS amount_due_eur,
       CASE
         WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 'Not yet due'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
         ELSE '90+ days overdue'
       END AS aging_bucket,
       CASE
         WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 0
         ELSE DATE '2025-12-31' - CAST(i.due_at AS date)
       END AS days_past_due
     FROM invoices i
     JOIN target_tenants t ON t.id = i.tenant_id
     LEFT JOIN rates r ON r.quote_code = i.currency_code
     WHERE i.status IN ('open', 'past_due')
       AND i.amount_due > 0
   ),
   agg AS (
     SELECT
       aging_bucket,
       COUNT(*) AS invoice_count,
       SUM(amount_due_eur) AS amount_due_eur,
       AVG(days_past_due) AS avg_days_past_due
     FROM inv
     GROUP BY aging_bucket
   )
   SELECT
     aging_bucket,
     invoice_count,
     ROUND(amount_due_eur, 2) AS amount_due_eur,
     ROUND(amount_due_eur / SUM(amount_due_eur) OVER () * 100, 1) AS pct_of_total_amount_due,
     ROUND(avg_days_past_due, 1) AS avg_days_past_due
   FROM agg
   ORDER BY amount_due_eur DESC, aging_bucket ASC

   “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. The report buckets open/past-due invoices (amount_due > 0) for the 9 named B2B wholesale tenants as of 2025-12-31, converts non-EUR amounts via the EUR-based exchange_rates table (EUR itself passes through at 1.0), and returns one row per aging bucket ordered by amount_due_eur descending.”

5. The report comes back overnight

   As Yuki Tanaka saw it, from the small dataset the agents worked on

   aging_bucket       | invoice_count | amount_due_eur | pct_of_total_amount_due | avg_days_past_due
   -------------------+---------------+----------------+-------------------------+------------------
   Not yet due        |            66 |       74706.09 |                    93.1 |               0.0
   1-30 days overdue  |             7 |        5140.32 |                     6.4 |               7.0
   31-60 days overdue |             1 |         358.26 |                     0.4 |              33.0
   (3 rows)

6. What came of it

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

   It builds on:

   • q097B2B wholesaler receivables aging
   • q308Corrected 2025 revenue and receivables by B2B tenant

7. It becomes benchmark query q310

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

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

   WITH target_tenants AS (  SELECT id, name  FROM tenants  WHERE name IN (    'Ballycorran Industrial Supply',    'Kontor24 Burobedarf',    'OfficeHub Nordic',    'Midland Safety Wear',    'Castell Fasteners',    'Hanse Industriebedarf',    'Polder Packaging',    'Deskline Supplies',    'Papeterie Grossiste'  )),rates AS (  SELECT quote_code, rate  FROM exchange_rates  WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'),inv AS (  SELECT    i.id,    i.amount_due,    i.currency_code,    i.due_at,    i.id AS id,    i.amount_due AS amount_due,    i.currency_code AS currency_code,    i.due_at AS due_at,    CASE      WHEN i.currency_code = 'EUR' THEN i.amount_due      ELSE i.amount_due / r.rate      ELSE CAST(i.amount_due / r.rate AS NUMERIC(18,6))    END AS amount_due_eur,    CASE      WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 'Not yet due'      WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 1 AND 30 THEN '1-30 days overdue'      WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 31 AND 60 THEN '31-60 days overdue'      WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 61 AND 90 THEN '61-90 days overdue'      ELSE '90+ days overdue'    END AS aging_bucket,    CASE      WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 0      ELSE DATE '2025-12-31' - CAST(i.due_at AS date)    END AS days_past_due  FROM invoices i  JOIN target_tenants t ON t.id = i.tenant_id  LEFT JOIN rates r ON r.quote_code = i.currency_code  WHERE i.status IN ('open', 'past_due')    AND i.amount_due > 0),agg AS (  SELECT    aging_bucket,    COUNT(*) AS invoice_count,    SUM(amount_due_eur) AS amount_due_eur,    AVG(days_past_due) AS avg_days_past_due    AVG(CAST(days_past_due AS NUMERIC(18,6))) AS avg_days_past_due  FROM inv  GROUP BY aging_bucket)SELECT  aging_bucket,  invoice_count,  ROUND(amount_due_eur, 2) AS amount_due_eur,  ROUND(amount_due_eur / SUM(amount_due_eur) OVER () * 100, 1) AS pct_of_total_amount_due,  ROUND(avg_days_past_due, 1) AS avg_days_past_due  amount_due_eur AS amount_due_eur,  amount_due_eur / SUM(amount_due_eur) OVER () * 100 AS pct_of_total_amount_due,  avg_days_past_due AS avg_days_past_dueFROM aggORDER BY amount_due_eur DESC, aging_bucket ASCORDER BY amount_due_eur DESC NULLS FIRST, aging_bucket ASC NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH target_tenants AS (
     SELECT id, name
     FROM tenants
     WHERE name IN (
       'Ballycorran Industrial Supply',
       'Kontor24 Burobedarf',
       'OfficeHub Nordic',
       'Midland Safety Wear',
       'Castell Fasteners',
       'Hanse Industriebedarf',
       'Polder Packaging',
       'Deskline Supplies',
       'Papeterie Grossiste'
     )
   ),
   rates AS (
     SELECT quote_code, rate
     FROM exchange_rates
     WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
   ),
   inv AS (
     SELECT
       i.id AS id,
       i.amount_due AS amount_due,
       i.currency_code AS currency_code,
       i.due_at AS due_at,
       CASE
         WHEN i.currency_code = 'EUR' THEN i.amount_due
         ELSE CAST(i.amount_due / r.rate AS NUMERIC(18,6))
       END AS amount_due_eur,
       CASE
         WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 'Not yet due'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
         WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
         ELSE '90+ days overdue'
       END AS aging_bucket,
       CASE
         WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 0
         ELSE DATE '2025-12-31' - CAST(i.due_at AS date)
       END AS days_past_due
     FROM invoices i
     JOIN target_tenants t ON t.id = i.tenant_id
     LEFT JOIN rates r ON r.quote_code = i.currency_code
     WHERE i.status IN ('open', 'past_due')
       AND i.amount_due > 0
   ),
   agg AS (
     SELECT
       aging_bucket,
       COUNT(*) AS invoice_count,
       SUM(amount_due_eur) AS amount_due_eur,
       AVG(CAST(days_past_due AS NUMERIC(18,6))) AS avg_days_past_due
     FROM inv
     GROUP BY aging_bucket
   )
   SELECT
     aging_bucket,
     invoice_count,
     amount_due_eur AS amount_due_eur,
     amount_due_eur / SUM(amount_due_eur) OVER () * 100 AS pct_of_total_amount_due,
     avg_days_past_due AS avg_days_past_due
   FROM agg
   ORDER BY amount_due_eur DESC NULLS FIRST, aging_bucket ASC NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 29, Column 30: 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": "b139c765-1c95-47e0-8a53-a107c33a95e0", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.amount_due Decimal(14, 4) Decimal64(size = 0), __table6.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

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

   • The request, as Ines received it

     Report request from Yuki Tanaka (analyst for the Head of Data):
     
     Title: Final B2B wholesale receivables aging (corrected)
     Question: With the full, verified 9-tenant B2B wholesale list and EUR handled correctly, what does the receivables aging actually look like for the board pack?
     Grain: receivables aging bucket
     Columns:
     - aging_bucket
     - invoice_count
     - amount_due_eur
     - pct_of_total_amount_due
     - avg_days_past_due
     Details: Population: invoices belonging to the 9 verified B2B wholesale tenants (Ballycorran Industrial Supply, Kontor24 Burobedarf, OfficeHub Nordic, Midland Safety Wear, Castell Fasteners, Hanse Industriebedarf, Polder Packaging, Deskline Supplies, Papeterie Grossiste - the full list with at least one deals row), with status in ('open','past_due') and amount_due > 0, as of snapshot 2025-12-31. IMPORTANT: use correct EUR handling as in tonight's other requests - EUR amounts pass through at rate 1.0, never requiring a self-referential exchange_rates row. Bucket each invoice by days overdue (2025-12-31 minus due_at): 'Not yet due' (due_at is null or on/after 2025-12-31), '1-30 days overdue', '31-60 days overdue', '61-90 days overdue', '90+ days overdue'. invoice_count = count in bucket. amount_due_eur = sum of invoices.amount_due converted to EUR per the correct rule, using invoices.currency_code and as_of=2025-12-31. pct_of_total_amount_due = amount_due_eur / total across all buckets * 100, rounded to 1 decimal. avg_days_past_due = average (2025-12-31 minus due_at) in days for the bucket, rounded to 1 decimal, 0 for 'Not yet due'. Tables: invoices, tenants, exchange_rates. Order by amount_due_eur descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     WITH target_tenants AS (
       SELECT id, name
       FROM tenants
       WHERE name IN (
         'Ballycorran Industrial Supply',
         'Kontor24 Burobedarf',
         'OfficeHub Nordic',
         'Midland Safety Wear',
         'Castell Fasteners',
         'Hanse Industriebedarf',
         'Polder Packaging',
         'Deskline Supplies',
         'Papeterie Grossiste'
       )
     ),
     rates AS (
       SELECT quote_code, rate
       FROM exchange_rates
       WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
     ),
     inv AS (
       SELECT
         i.id,
         i.amount_due,
         i.currency_code,
         i.due_at,
         CASE
           WHEN i.currency_code = 'EUR' THEN i.amount_due
           ELSE i.amount_due / r.rate
         END AS amount_due_eur,
         CASE
           WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 'Not yet due'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
           ELSE '90+ days overdue'
         END AS aging_bucket,
         CASE
           WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 0
           ELSE DATE '2025-12-31' - CAST(i.due_at AS date)
         END AS days_past_due
       FROM invoices i
       JOIN target_tenants t ON t.id = i.tenant_id
       LEFT JOIN rates r ON r.quote_code = i.currency_code
       WHERE i.status IN ('open', 'past_due')
         AND i.amount_due > 0
     ),
     agg AS (
       SELECT
         aging_bucket,
         COUNT(*) AS invoice_count,
         SUM(amount_due_eur) AS amount_due_eur,
         AVG(days_past_due) AS avg_days_past_due
       FROM inv
       GROUP BY aging_bucket
     )
     SELECT
       aging_bucket,
       invoice_count,
       ROUND(amount_due_eur, 2) AS amount_due_eur,
       ROUND(amount_due_eur / SUM(amount_due_eur) OVER () * 100, 1) AS pct_of_total_amount_due,
       ROUND(avg_days_past_due, 1) AS avg_days_past_due
     FROM agg
     ORDER BY amount_due_eur DESC, aging_bucket ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: aging_bucket, invoice_count, amount_due_eur, pct_of_total_amount_due, avg_days_past_due

   • InesQuery compiles. Submitting.
   • Ines calls submit

     WITH target_tenants AS (
       SELECT id, name
       FROM tenants
       WHERE name IN (
         'Ballycorran Industrial Supply',
         'Kontor24 Burobedarf',
         'OfficeHub Nordic',
         'Midland Safety Wear',
         'Castell Fasteners',
         'Hanse Industriebedarf',
         'Polder Packaging',
         'Deskline Supplies',
         'Papeterie Grossiste'
       )
     ),
     rates AS (
       SELECT quote_code, rate
       FROM exchange_rates
       WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
     ),
     inv AS (
       SELECT
         i.id,
         i.amount_due,
         i.currency_code,
         i.due_at,
         CASE
           WHEN i.currency_code = 'EUR' THEN i.amount_due
           ELSE i.amount_due / r.rate
         END AS amount_due_eur,
         CASE
           WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 'Not yet due'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
           WHEN DATE '2025-12-31' - CAST(i.due_at AS date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
           ELSE '90+ days overdue'
         END AS aging_bucket,
         CASE
           WHEN i.due_at IS NULL OR i.due_at >= DATE '2025-12-31' THEN 0
           ELSE DATE '2025-12-31' - CAST(i.due_at AS date)
         END AS days_past_due
       FROM invoices i
       JOIN target_tenants t ON t.id = i.tenant_id
       LEFT JOIN rates r ON r.quote_code = i.currency_code
       WHERE i.status IN ('open', 'past_due')
         AND i.amount_due > 0
     ),
     agg AS (
       SELECT
         aging_bucket,
         COUNT(*) AS invoice_count,
         SUM(amount_due_eur) AS amount_due_eur,
         AVG(days_past_due) AS avg_days_past_due
       FROM inv
       GROUP BY aging_bucket
     )
     SELECT
       aging_bucket,
       invoice_count,
       ROUND(amount_due_eur, 2) AS amount_due_eur,
       ROUND(amount_due_eur / SUM(amount_due_eur) OVER () * 100, 1) AS pct_of_total_amount_due,
       ROUND(avg_days_past_due, 1) AS avg_days_past_due
     FROM agg
     ORDER BY amount_due_eur DESC, aging_bucket ASC

   • The warehouse answers

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

   • InesSubmitted. The report buckets open/past-due invoices (amount_due > 0) for the 9 named B2B wholesale tenants as of 2025-12-31, converts non-EUR amounts via the EUR-based exchange_rates table (EUR itself passes through at 1.0), and returns one row per aging bucket ordered by amount_due_eur descending.

   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 (265 KB) · Yuki Tanaka's transcript (954 KB) · Rosa Van den Berg's transcript (262 KB) · the night's plan (7 KB) · the night's reports (4 KB) · the week's memo (3 KB)