Queries · q306 · Head of Data · week 2, night 3

Castell Fasteners open invoices in detail

“What are Castell Fasteners' actual open invoices, so we can reconcile my €14,481 open-receivables figure against Merchant Success's reported €5,080 and present one consistent number to the board?”

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 3, request 2 of 3

   Last night's B2B report raised a real data-integrity flag I need to resolve before I can trust the 38.9%-of-revenue materiality number: 6 of the 9 tenants with CRM deal activity (Ballycorran Industrial Supply, Hanse Industriebedarf, Kontor24 Burobedarf, Midland Safety Wear, Papeterie Grossiste, Polder Packaging) show exactly 0.0 across contract value, 2025 revenue, AND open receivables simultaneously, despite deal counts from 6 to 136 - that pattern (three independent measures all exactly zero together) looks like a conversion or join failure, not genuine zero business activity, and I want to check the raw native-currency numbers before concluding anything. Separately, the age-bucket dip and payment-method report together close out Rosa's timing question cleanly: bank_transfer payments average 15.7 days to capture (only 61.1% within 14 days) versus instant capture for card/wallet/credit, which fully explains the mid-range dip as pure timing, not a structural hole - I'm confident enough in that to not spend another slot on it. The segments/cohorts report found no company-wide "active customer" definition already in use (these are per-merchant marketing audiences, not Tidewick's own customer count), but it did validate that a recency-based "ordered in the last N days" concept (the "Lapsed 90 days" segment) is the operational analog marketing already relies on - good enough to write up without further digging. Tonight's three slots: (1) diagnose the zero-value B2B tenant anomaly in native currency with no FX conversion, (2) pull Castell Fasteners' actual open invoices since my €14,481 figure doesn't match Merchant Success's reported €5,080 and the board pack can't show two different numbers for the same merchant, (3) check whether Cragsley Outfitters' outsized share of late refunds is just proportional to its size or a real outlier, by comparing late-refund share against each top merchant's own total refund volume.

   Read the whole plan
   Castell Fasteners open invoices in detail

   Question

   What are Castell Fasteners' actual open invoices, so we can reconcile my €14,481 open-receivables figure against Merchant Success's reported €5,080 and present one consistent number to the board?

   One row per

   invoice

   Columns

   • invoice_number
   • status
   • currency_code
   • total
   • amount_due
   • issued_at
   • due_at
   • days_overdue

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

   SELECT
     t0.invoice_number AS invoice_number,
     t0.status AS status,
     t0.currency_code AS currency_code,
     t0.total AS total,
     t0.amount_due AS amount_due,
     t0.issued_at AS issued_at,
     t0.due_at AS due_at,
     CASE
       WHEN t0.due_at IS NULL THEN 0
       ELSE GREATEST(CAST(DATE '2025-12-31' - CAST(t0.due_at AS date) AS integer), 0)
     END AS days_overdue
   FROM invoices t0
   JOIN tenants t1 ON t1.id = t0.tenant_id
   WHERE t1.name = 'Castell Fasteners'
     AND t0.status IN ('open', 'past_due')
     AND t0.amount_due > 0
   ORDER BY t0.amount_due DESC, t0.invoice_number ASC

   “Query compiles and returns the requested columns.”

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

   “Submitted. The report pulls every Castell Fasteners invoice with status open or past_due and amount_due > 0 directly from invoices joined to tenants, showing native currency, amounts, issue/due dates, and days overdue (floored at 0) as of the 2025-12-31 snapshot, ordered by amount_due descending — this should let Yuki reconcile the €14,481 figure line-by-line against Merchant Success's €5,080.”

5. The report comes back overnight

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

   invoice_number | status   | currency_code |     total | amount_due | issued_at                  | due_at                     | days_overdue
   ---------------+----------+---------------+-----------+------------+----------------------------+----------------------------+-------------
   INV-100296     | open     | USD           | 4197.8000 |  4197.8000 | 2025-12-02 22:19:09.742515 | 2026-01-31 22:19:09.742515 |            0
   INV-100287     | past_due | USD           | 2578.5700 |  2578.5700 | 2025-11-07 17:28:00        | 2025-12-07 17:28:00        |           24
   INV-100301     | open     | USD           | 2321.3800 |  2321.3800 | 2025-12-12 19:01:00        | 2026-01-11 19:01:00        |            0
   INV-100302     | open     | USD           | 2121.4200 |  2012.9400 | 2025-12-12 15:07:00        | 2026-02-10 15:07:00        |            0
   INV-100297     | open     | USD           | 1933.7900 |  1933.7900 | 2025-12-05 16:35:00        | 2026-01-04 16:35:00        |            0
   INV-100303     | past_due | USD           | 1337.2400 |  1337.2400 | 2025-12-15 16:10:00        | 2025-12-29 16:10:00        |            2
   INV-100288     | open     | USD           | 1090.6100 |  1090.6100 | 2025-11-13 01:59:27.183141 | 2026-01-12 01:59:27.183141 |            0
   INV-100304     | open     | USD           |  806.3300 |   806.3300 | 2025-12-22 20:35:00        | 2026-01-21 20:35:00        |            0
   INV-100298     | open     | USD           |  517.0800 |   517.0800 | 2025-12-08 18:54:00        | 2026-01-07 18:54:00        |            0
   INV-100299     | open     | USD           |  210.2400 |   210.2400 | 2025-12-08 23:06:04.856472 | 2026-01-07 23:06:04.856472 |            0
   INV-100300     | past_due | USD           |  178.5700 |   178.5700 | 2025-12-11 23:28:00.085125 | 2025-12-25 23:28:00.085125 |            6
   (11 rows)

6. What came of it

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

   It builds on:

   • q302B2B wholesale tenants identified by CRM deal activity, with revenue and receivables

7. It becomes benchmark query q306

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

   • 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

   SELECT  t0.invoice_number AS invoice_number,  t0.status AS status,  t0.currency_code AS currency_code,  t0.total AS total,  t0.amount_due AS amount_due,  t0.issued_at AS issued_at,  t0.due_at AS due_at,  CASE    WHEN t0.due_at IS NULL THEN 0    ELSE GREATEST(CAST(DATE '2025-12-31' - CAST(t0.due_at AS date) AS integer), 0)  END AS days_overdueFROM invoices t0JOIN tenants t1 ON t1.id = t0.tenant_idWHERE t1.name = 'Castell Fasteners'  AND t0.status IN ('open', 'past_due')  AND t0.amount_due > 0ORDER BY t0.amount_due DESC, t0.invoice_number ASCORDER BY t0.amount_due DESC NULLS FIRST, t0.invoice_number 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

   SELECT
     t0.invoice_number AS invoice_number,
     t0.status AS status,
     t0.currency_code AS currency_code,
     t0.total AS total,
     t0.amount_due AS amount_due,
     t0.issued_at AS issued_at,
     t0.due_at AS due_at,
     CASE
       WHEN t0.due_at IS NULL THEN 0
       ELSE GREATEST(CAST(DATE '2025-12-31' - CAST(t0.due_at AS date) AS integer), 0)
     END AS days_overdue
   FROM invoices t0
   JOIN tenants t1 ON t1.id = t0.tenant_id
   WHERE t1.name = 'Castell Fasteners'
     AND t0.status IN ('open', 'past_due')
     AND t0.amount_due > 0
   ORDER BY t0.amount_due DESC NULLS FIRST, t0.invoice_number ASC NULLS LAST

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

   Bea's whole session, 7 turns

   • The request, as Bea received it

     Report request from Yuki Tanaka (analyst for the Head of Data):
     
     Title: Castell Fasteners open invoices in detail
     Question: What are Castell Fasteners' actual open invoices, so we can reconcile my €14,481 open-receivables figure against Merchant Success's reported €5,080 and present one consistent number to the board?
     Grain: invoice
     Columns:
     - invoice_number
     - status
     - currency_code
     - total
     - amount_due
     - issued_at
     - due_at
     - days_overdue
     Details: Population: all invoices belonging to the tenant 'Castell Fasteners' (join via billing_profiles or invoices.tenant_id directly to tenants where tenants.name = 'Castell Fasteners') with status in ('open','past_due') and amount_due > 0, as of the snapshot date 2025-12-31. Show the native currency_code and amounts as stored (no EUR conversion needed since this is a single-tenant, single-currency drill-down - but include currency_code so I can confirm). days_overdue = 2025-12-31 minus due_at, in days (0 or negative shown as 0 if not yet due). Tables: invoices, tenants (and billing_profiles if needed for the tenant join). Order by amount_due descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Bea calls dry_run

     SELECT
       t0.invoice_number AS invoice_number,
       t0.status AS status,
       t0.currency_code AS currency_code,
       t0.total AS total,
       t0.amount_due AS amount_due,
       t0.issued_at AS issued_at,
       t0.due_at AS due_at,
       CASE
         WHEN t0.due_at IS NULL THEN 0
         ELSE GREATEST(CAST(DATE '2025-12-31' - CAST(t0.due_at AS date) AS integer), 0)
       END AS days_overdue
     FROM invoices t0
     JOIN tenants t1 ON t1.id = t0.tenant_id
     WHERE t1.name = 'Castell Fasteners'
       AND t0.status IN ('open', 'past_due')
       AND t0.amount_due > 0
     ORDER BY t0.amount_due DESC, t0.invoice_number ASC

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: invoice_number, status, currency_code, total, amount_due, issued_at, due_at, days_overdue

   • BeaQuery compiles and returns the requested columns.
   • Bea calls submit

     SELECT
       t0.invoice_number AS invoice_number,
       t0.status AS status,
       t0.currency_code AS currency_code,
       t0.total AS total,
       t0.amount_due AS amount_due,
       t0.issued_at AS issued_at,
       t0.due_at AS due_at,
       CASE
         WHEN t0.due_at IS NULL THEN 0
         ELSE GREATEST(CAST(DATE '2025-12-31' - CAST(t0.due_at AS date) AS integer), 0)
       END AS days_overdue
     FROM invoices t0
     JOIN tenants t1 ON t1.id = t0.tenant_id
     WHERE t1.name = 'Castell Fasteners'
       AND t0.status IN ('open', 'past_due')
       AND t0.amount_due > 0
     ORDER BY t0.amount_due DESC, t0.invoice_number ASC

   • The warehouse answers

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

   • BeaSubmitted. The report pulls every Castell Fasteners invoice with status `open` or `past_due` and `amount_due > 0` directly from `invoices` joined to `tenants`, showing native currency, amounts, issue/due dates, and days overdue (floored at 0) as of the 2025-12-31 snapshot, ordered by amount_due descending — this should let Yuki reconcile the €14,481 figure line-by-line against Merchant Success's €5,080.

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

   Bea's transcript (259 KB) · Yuki Tanaka's transcript (954 KB) · Rosa Van den Berg's transcript (262 KB) · the night's plan (6 KB) · the night's reports (8 KB) · the week's memo (3 KB)