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

Corrected 2025 revenue and receivables by B2B tenant

“What is the real 2025 revenue and open receivables for all 9 verified B2B wholesale tenants, now that the EUR-conversion bug that zeroed out 6 of them is fixed?”

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 1 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
   Corrected 2025 revenue and receivables by B2B tenant

   Question

   What is the real 2025 revenue and open receivables for all 9 verified B2B wholesale tenants, now that the EUR-conversion bug that zeroed out 6 of them is fixed?

   One row per

   tenant

   Columns

   • tenant_name
   • currency_code
   • total_2025_revenue_eur
   • pct_of_total_2025_revenue
   • open_amount_due_eur
   • pct_amount_due_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)

   WITH target_tenants AS (
     SELECT t0.id AS tenant_id, t0.name AS tenant_name, t0.default_currency_code AS currency_code
     FROM tenants t0
     WHERE t0.name IN (
       'Ballycorran Industrial Supply', 'Kontor24 Burobedarf', 'OfficeHub Nordic',
       'Midland Safety Wear', 'Castell Fasteners', 'Hanse Industriebedarf',
       'Polder Packaging', 'Deskline Supplies', 'Papeterie Grossiste'
     )
     AND EXISTS (SELECT 1 FROM deals d0 WHERE d0.tenant_id = t0.id)
   ),
   revenue_tenant AS (
     SELECT p0.tenant_id AS tenant_id,
       SUM(CASE WHEN p0.currency_code = 'EUR' THEN p0.amount
                ELSE p0.amount / er0.rate END) AS total_2025_revenue_eur
     FROM payments p0
     LEFT JOIN exchange_rates er0
       ON er0.base_code = 'EUR'
      AND er0.quote_code = p0.currency_code
      AND er0.as_of = CAST(p0.captured_at AS date)
     WHERE p0.status IN ('captured', 'refunded', 'partially_refunded')
       AND p0.captured_at >= DATE '2025-01-01'
       AND p0.captured_at < DATE '2026-01-01'
     GROUP BY p0.tenant_id
   ),
   revenue_all AS (
     SELECT SUM(CASE WHEN p1.currency_code = 'EUR' THEN p1.amount
                     ELSE p1.amount / er1.rate END) AS total_revenue_eur_all
     FROM payments p1
     LEFT JOIN exchange_rates er1
       ON er1.base_code = 'EUR'
      AND er1.quote_code = p1.currency_code
      AND er1.as_of = CAST(p1.captured_at AS date)
     WHERE p1.status IN ('captured', 'refunded', 'partially_refunded')
       AND p1.captured_at >= DATE '2025-01-01'
       AND p1.captured_at < DATE '2026-01-01'
   ),
   open_invoices_tenant AS (
     SELECT i0.tenant_id AS tenant_id,
       SUM(CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                ELSE i0.amount_due / er2.rate END) AS open_amount_due_eur,
       SUM(CASE WHEN i0.due_at < TIMESTAMP '2025-12-31 00:00:00'
                THEN (CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                           ELSE i0.amount_due / er2.rate END)
                ELSE 0 END) AS overdue_amount_due_eur
     FROM invoices i0
     LEFT JOIN exchange_rates er2
       ON er2.base_code = 'EUR'
      AND er2.quote_code = i0.currency_code
      AND er2.as_of = DATE '2025-12-31'
     WHERE i0.status IN ('open', 'past_due')
       AND i0.amount_due > 0
     GROUP BY i0.tenant_id
   )
   SELECT
     tt0.tenant_name AS tenant_name,
     tt0.currency_code AS currency_code,
     ROUND(COALESCE(rt0.total_2025_revenue_eur, 0), 2) AS total_2025_revenue_eur,
     ROUND(COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100, 1) AS pct_of_total_2025_revenue,
     ROUND(COALESCE(oi0.open_amount_due_eur, 0), 2) AS open_amount_due_eur,
     ROUND(
       CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL
            ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END
     , 1) AS pct_amount_due_overdue
   FROM target_tenants tt0
   LEFT JOIN revenue_tenant rt0 ON rt0.tenant_id = tt0.tenant_id
   CROSS JOIN revenue_all ra0
   LEFT JOIN open_invoices_tenant oi0 ON oi0.tenant_id = tt0.tenant_id
   ORDER BY total_2025_revenue_eur DESC, tt0.tenant_name

   “Query compiles and returns the requested columns at tenant grain. 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 computes, per tenant: 2025 revenue (EUR-converted with the EUR-passthrough fix), its share of company-wide 2025 revenue, open receivables as of 2025-12-31, and the overdue share of those receivables — ordered by revenue descending, one row per tenant.”

5. The report comes back overnight

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

   tenant_name                   | currency_code | total_2025_revenue_eur | pct_of_total_2025_revenue | open_amount_due_eur | pct_amount_due_overdue
   ------------------------------+---------------+------------------------+---------------------------+---------------------+-----------------------
   Ballycorran Industrial Supply | EUR           |              554871.06 |                       9.4 |            22415.24 |                    6.5
   OfficeHub Nordic              | NOK           |              426010.27 |                       7.2 |            14348.57 |                    2.5
   Castell Fasteners             | USD           |              386487.62 |                       6.5 |            14481.02 |                   23.8
   Midland Safety Wear           | EUR           |              297398.75 |                       5.0 |            10049.47 |                    1.7
   Deskline Supplies             | GBP           |              281736.43 |                       4.8 |            12749.42 |                    0.0
   Hanse Industriebedarf         | EUR           |              111754.28 |                       1.9 |              864.14 |                    0.0
   Polder Packaging              | EUR           |               90872.65 |                       1.5 |              5296.8 |                    1.2
   Papeterie Grossiste           | EUR           |               40352.64 |                       0.7 |                 0.0 |                   NULL
   (8 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:

   • q309Scope of the EUR exchange-rate join bug on 2025 revenue
   • q310Final B2B wholesale receivables aging (corrected)

   It builds on:

   • q302B2B wholesale tenants identified by CRM deal activity, with revenue and receivables
   • q305Diagnose zero-value B2B deal tenants in native currency

7. It becomes benchmark query q308

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

   • determinismtie-breaker: tenant names are not unique
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (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 (3×)
   The benchmark text, against the submitted SQL

   WITH target_tenants AS (  SELECT t0.id AS tenant_id, t0.name AS tenant_name, t0.default_currency_code AS currency_code  FROM tenants t0  WHERE t0.name IN (    'Ballycorran Industrial Supply', 'Kontor24 Burobedarf', 'OfficeHub Nordic',    'Midland Safety Wear', 'Castell Fasteners', 'Hanse Industriebedarf',    'Polder Packaging', 'Deskline Supplies', 'Papeterie Grossiste'  )  AND EXISTS (SELECT 1 FROM deals d0 WHERE d0.tenant_id = t0.id)),revenue_tenant AS (  SELECT p0.tenant_id AS tenant_id,    SUM(CASE WHEN p0.currency_code = 'EUR' THEN p0.amount             ELSE p0.amount / er0.rate END) AS total_2025_revenue_eur             ELSE CAST(p0.amount / er0.rate AS NUMERIC(18,6)) END) AS total_2025_revenue_eur  FROM payments p0  LEFT JOIN exchange_rates er0    ON er0.base_code = 'EUR'   AND er0.quote_code = p0.currency_code   AND er0.as_of = CAST(p0.captured_at AS date)  WHERE p0.status IN ('captured', 'refunded', 'partially_refunded')    AND p0.captured_at >= DATE '2025-01-01'    AND p0.captured_at < DATE '2026-01-01'  GROUP BY p0.tenant_id),revenue_all AS (  SELECT SUM(CASE WHEN p1.currency_code = 'EUR' THEN p1.amount                  ELSE p1.amount / er1.rate END) AS total_revenue_eur_all                  ELSE CAST(p1.amount / er1.rate AS NUMERIC(18,6)) END) AS total_revenue_eur_all  FROM payments p1  LEFT JOIN exchange_rates er1    ON er1.base_code = 'EUR'   AND er1.quote_code = p1.currency_code   AND er1.as_of = CAST(p1.captured_at AS date)  WHERE p1.status IN ('captured', 'refunded', 'partially_refunded')    AND p1.captured_at >= DATE '2025-01-01'    AND p1.captured_at < DATE '2026-01-01'),open_invoices_tenant AS (  SELECT i0.tenant_id AS tenant_id,    SUM(CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due             ELSE i0.amount_due / er2.rate END) AS open_amount_due_eur,             ELSE CAST(i0.amount_due / er2.rate AS NUMERIC(18,6)) END) AS open_amount_due_eur,    SUM(CASE WHEN i0.due_at < TIMESTAMP '2025-12-31 00:00:00'             THEN (CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due                        ELSE i0.amount_due / er2.rate END)                        ELSE CAST(i0.amount_due / er2.rate AS NUMERIC(18,6)) END)             ELSE 0 END) AS overdue_amount_due_eur  FROM invoices i0  LEFT JOIN exchange_rates er2    ON er2.base_code = 'EUR'   AND er2.quote_code = i0.currency_code   AND er2.as_of = DATE '2025-12-31'  WHERE i0.status IN ('open', 'past_due')    AND i0.amount_due > 0  GROUP BY i0.tenant_id)SELECT  tt0.tenant_name AS tenant_name,  tt0.currency_code AS currency_code,  ROUND(COALESCE(rt0.total_2025_revenue_eur, 0), 2) AS total_2025_revenue_eur,  ROUND(COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100, 1) AS pct_of_total_2025_revenue,  ROUND(COALESCE(oi0.open_amount_due_eur, 0), 2) AS open_amount_due_eur,  ROUND(    CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL         ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END  , 1) AS pct_amount_due_overdue  COALESCE(rt0.total_2025_revenue_eur, 0) AS total_2025_revenue_eur,  COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100 AS pct_of_total_2025_revenue,  COALESCE(oi0.open_amount_due_eur, 0) AS open_amount_due_eur,  CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL       ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END AS pct_amount_due_overdueFROM target_tenants tt0LEFT JOIN revenue_tenant rt0 ON rt0.tenant_id = tt0.tenant_idCROSS JOIN revenue_all ra0LEFT JOIN open_invoices_tenant oi0 ON oi0.tenant_id = tt0.tenant_idORDER BY total_2025_revenue_eur DESC, tt0.tenant_nameORDER BY total_2025_revenue_eur DESC NULLS FIRST, tt0.tenant_name NULLS LAST, tt0.tenant_id 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 t0.id AS tenant_id, t0.name AS tenant_name, t0.default_currency_code AS currency_code
     FROM tenants t0
     WHERE t0.name IN (
       'Ballycorran Industrial Supply', 'Kontor24 Burobedarf', 'OfficeHub Nordic',
       'Midland Safety Wear', 'Castell Fasteners', 'Hanse Industriebedarf',
       'Polder Packaging', 'Deskline Supplies', 'Papeterie Grossiste'
     )
     AND EXISTS (SELECT 1 FROM deals d0 WHERE d0.tenant_id = t0.id)
   ),
   revenue_tenant AS (
     SELECT p0.tenant_id AS tenant_id,
       SUM(CASE WHEN p0.currency_code = 'EUR' THEN p0.amount
                ELSE CAST(p0.amount / er0.rate AS NUMERIC(18,6)) END) AS total_2025_revenue_eur
     FROM payments p0
     LEFT JOIN exchange_rates er0
       ON er0.base_code = 'EUR'
      AND er0.quote_code = p0.currency_code
      AND er0.as_of = CAST(p0.captured_at AS date)
     WHERE p0.status IN ('captured', 'refunded', 'partially_refunded')
       AND p0.captured_at >= DATE '2025-01-01'
       AND p0.captured_at < DATE '2026-01-01'
     GROUP BY p0.tenant_id
   ),
   revenue_all AS (
     SELECT SUM(CASE WHEN p1.currency_code = 'EUR' THEN p1.amount
                     ELSE CAST(p1.amount / er1.rate AS NUMERIC(18,6)) END) AS total_revenue_eur_all
     FROM payments p1
     LEFT JOIN exchange_rates er1
       ON er1.base_code = 'EUR'
      AND er1.quote_code = p1.currency_code
      AND er1.as_of = CAST(p1.captured_at AS date)
     WHERE p1.status IN ('captured', 'refunded', 'partially_refunded')
       AND p1.captured_at >= DATE '2025-01-01'
       AND p1.captured_at < DATE '2026-01-01'
   ),
   open_invoices_tenant AS (
     SELECT i0.tenant_id AS tenant_id,
       SUM(CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                ELSE CAST(i0.amount_due / er2.rate AS NUMERIC(18,6)) END) AS open_amount_due_eur,
       SUM(CASE WHEN i0.due_at < TIMESTAMP '2025-12-31 00:00:00'
                THEN (CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                           ELSE CAST(i0.amount_due / er2.rate AS NUMERIC(18,6)) END)
                ELSE 0 END) AS overdue_amount_due_eur
     FROM invoices i0
     LEFT JOIN exchange_rates er2
       ON er2.base_code = 'EUR'
      AND er2.quote_code = i0.currency_code
      AND er2.as_of = DATE '2025-12-31'
     WHERE i0.status IN ('open', 'past_due')
       AND i0.amount_due > 0
     GROUP BY i0.tenant_id
   )
   SELECT
     tt0.tenant_name AS tenant_name,
     tt0.currency_code AS currency_code,
     COALESCE(rt0.total_2025_revenue_eur, 0) AS total_2025_revenue_eur,
     COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100 AS pct_of_total_2025_revenue,
     COALESCE(oi0.open_amount_due_eur, 0) AS open_amount_due_eur,
     CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL
          ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END AS pct_amount_due_overdue
   FROM target_tenants tt0
   LEFT JOIN revenue_tenant rt0 ON rt0.tenant_id = tt0.tenant_id
   CROSS JOIN revenue_all ra0
   LEFT JOIN open_invoices_tenant oi0 ON oi0.tenant_id = tt0.tenant_id
   ORDER BY total_2025_revenue_eur DESC NULLS FIRST, tt0.tenant_name NULLS LAST, tt0.tenant_id NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 14, Column 34: 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": "8c55b21c-fe33-4d54-b14f-8367fb5f9ce6", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table7.amount Decimal(14, 4) Decimal64(size = 0), __table8.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. q308 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: Corrected 2025 revenue and receivables by B2B tenant
     Question: What is the real 2025 revenue and open receivables for all 9 verified B2B wholesale tenants, now that the EUR-conversion bug that zeroed out 6 of them is fixed?
     Grain: tenant
     Columns:
     - tenant_name
     - currency_code
     - total_2025_revenue_eur
     - pct_of_total_2025_revenue
     - open_amount_due_eur
     - pct_amount_due_overdue
     Details: Population: the same 9 tenants with at least one deals row (Ballycorran Industrial Supply, Kontor24 Burobedarf, OfficeHub Nordic, Midland Safety Wear, Castell Fasteners, Hanse Industriebedarf, Polder Packaging, Deskline Supplies, Papeterie Grossiste). IMPORTANT currency handling to avoid repeating a prior bug: when converting an amount whose currency_code is 'EUR', do NOT join exchange_rates for quote_code='EUR' (no such self-referential identity row may exist) - instead treat the EUR rate as 1.0 directly (e.g. CASE WHEN currency_code = 'EUR' THEN amount ELSE amount / rate END, with the exchange_rates join being a LEFT JOIN only consulted for non-EUR currencies, so EUR rows are never dropped). total_2025_revenue_eur = sum of this tenant's payments.amount where status in ('captured','refunded','partially_refunded') and captured_at in 2025, converted per the above rule using payments.currency_code and captured_at date. pct_of_total_2025_revenue = total_2025_revenue_eur / (sum of total_2025_revenue_eur across ALL tenants company-wide, computed with the same correct EUR-handling rule) * 100, rounded to 1 decimal. open_amount_due_eur = sum of invoices.amount_due for this tenant where status in ('open','past_due') and amount_due > 0, as of snapshot 2025-12-31, converted per the same EUR rule using invoices.currency_code and as_of=2025-12-31. pct_amount_due_overdue = (sum of amount_due where due_at < 2025-12-31) / open_amount_due_eur * 100, rounded to 1 decimal (NULL if open_amount_due_eur is 0). Tables: deals, tenants, payments, invoices, exchange_rates. Order by total_2025_revenue_eur descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Bea calls dry_run

     WITH target_tenants AS (
       SELECT t0.id AS tenant_id, t0.name AS tenant_name, t0.default_currency_code AS currency_code
       FROM tenants t0
       WHERE t0.name IN (
         'Ballycorran Industrial Supply', 'Kontor24 Burobedarf', 'OfficeHub Nordic',
         'Midland Safety Wear', 'Castell Fasteners', 'Hanse Industriebedarf',
         'Polder Packaging', 'Deskline Supplies', 'Papeterie Grossiste'
       )
       AND EXISTS (SELECT 1 FROM deals d0 WHERE d0.tenant_id = t0.id)
     ),
     revenue_tenant AS (
       SELECT p0.tenant_id AS tenant_id,
         SUM(CASE WHEN p0.currency_code = 'EUR' THEN p0.amount
                  ELSE p0.amount / er0.rate END) AS total_2025_revenue_eur
       FROM payments p0
       LEFT JOIN exchange_rates er0
         ON er0.base_code = 'EUR'
        AND er0.quote_code = p0.currency_code
        AND er0.as_of = CAST(p0.captured_at AS date)
       WHERE p0.status IN ('captured', 'refunded', 'partially_refunded')
         AND p0.captured_at >= DATE '2025-01-01'
         AND p0.captured_at < DATE '2026-01-01'
       GROUP BY p0.tenant_id
     ),
     revenue_all AS (
       SELECT SUM(CASE WHEN p1.currency_code = 'EUR' THEN p1.amount
                       ELSE p1.amount / er1.rate END) AS total_revenue_eur_all
       FROM payments p1
       LEFT JOIN exchange_rates er1
         ON er1.base_code = 'EUR'
        AND er1.quote_code = p1.currency_code
        AND er1.as_of = CAST(p1.captured_at AS date)
       WHERE p1.status IN ('captured', 'refunded', 'partially_refunded')
         AND p1.captured_at >= DATE '2025-01-01'
         AND p1.captured_at < DATE '2026-01-01'
     ),
     open_invoices_tenant AS (
       SELECT i0.tenant_id AS tenant_id,
         SUM(CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                  ELSE i0.amount_due / er2.rate END) AS open_amount_due_eur,
         SUM(CASE WHEN i0.due_at < TIMESTAMP '2025-12-31 00:00:00'
                  THEN (CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                             ELSE i0.amount_due / er2.rate END)
                  ELSE 0 END) AS overdue_amount_due_eur
       FROM invoices i0
       LEFT JOIN exchange_rates er2
         ON er2.base_code = 'EUR'
        AND er2.quote_code = i0.currency_code
        AND er2.as_of = DATE '2025-12-31'
       WHERE i0.status IN ('open', 'past_due')
         AND i0.amount_due > 0
       GROUP BY i0.tenant_id
     )
     SELECT
       tt0.tenant_name AS tenant_name,
       tt0.currency_code AS currency_code,
       ROUND(COALESCE(rt0.total_2025_revenue_eur, 0), 2) AS total_2025_revenue_eur,
       ROUND(COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100, 1) AS pct_of_total_2025_revenue,
       ROUND(COALESCE(oi0.open_amount_due_eur, 0), 2) AS open_amount_due_eur,
       ROUND(
         CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL
              ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END
       , 1) AS pct_amount_due_overdue
     FROM target_tenants tt0
     LEFT JOIN revenue_tenant rt0 ON rt0.tenant_id = tt0.tenant_id
     CROSS JOIN revenue_all ra0
     LEFT JOIN open_invoices_tenant oi0 ON oi0.tenant_id = tt0.tenant_id
     ORDER BY total_2025_revenue_eur DESC, tt0.tenant_name

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, currency_code, total_2025_revenue_eur, pct_of_total_2025_revenue, open_amount_due_eur, pct_amount_due_overdue

   • BeaQuery compiles and returns the requested columns at tenant grain. Submitting.
   • Bea calls submit

     WITH target_tenants AS (
       SELECT t0.id AS tenant_id, t0.name AS tenant_name, t0.default_currency_code AS currency_code
       FROM tenants t0
       WHERE t0.name IN (
         'Ballycorran Industrial Supply', 'Kontor24 Burobedarf', 'OfficeHub Nordic',
         'Midland Safety Wear', 'Castell Fasteners', 'Hanse Industriebedarf',
         'Polder Packaging', 'Deskline Supplies', 'Papeterie Grossiste'
       )
       AND EXISTS (SELECT 1 FROM deals d0 WHERE d0.tenant_id = t0.id)
     ),
     revenue_tenant AS (
       SELECT p0.tenant_id AS tenant_id,
         SUM(CASE WHEN p0.currency_code = 'EUR' THEN p0.amount
                  ELSE p0.amount / er0.rate END) AS total_2025_revenue_eur
       FROM payments p0
       LEFT JOIN exchange_rates er0
         ON er0.base_code = 'EUR'
        AND er0.quote_code = p0.currency_code
        AND er0.as_of = CAST(p0.captured_at AS date)
       WHERE p0.status IN ('captured', 'refunded', 'partially_refunded')
         AND p0.captured_at >= DATE '2025-01-01'
         AND p0.captured_at < DATE '2026-01-01'
       GROUP BY p0.tenant_id
     ),
     revenue_all AS (
       SELECT SUM(CASE WHEN p1.currency_code = 'EUR' THEN p1.amount
                       ELSE p1.amount / er1.rate END) AS total_revenue_eur_all
       FROM payments p1
       LEFT JOIN exchange_rates er1
         ON er1.base_code = 'EUR'
        AND er1.quote_code = p1.currency_code
        AND er1.as_of = CAST(p1.captured_at AS date)
       WHERE p1.status IN ('captured', 'refunded', 'partially_refunded')
         AND p1.captured_at >= DATE '2025-01-01'
         AND p1.captured_at < DATE '2026-01-01'
     ),
     open_invoices_tenant AS (
       SELECT i0.tenant_id AS tenant_id,
         SUM(CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                  ELSE i0.amount_due / er2.rate END) AS open_amount_due_eur,
         SUM(CASE WHEN i0.due_at < TIMESTAMP '2025-12-31 00:00:00'
                  THEN (CASE WHEN i0.currency_code = 'EUR' THEN i0.amount_due
                             ELSE i0.amount_due / er2.rate END)
                  ELSE 0 END) AS overdue_amount_due_eur
       FROM invoices i0
       LEFT JOIN exchange_rates er2
         ON er2.base_code = 'EUR'
        AND er2.quote_code = i0.currency_code
        AND er2.as_of = DATE '2025-12-31'
       WHERE i0.status IN ('open', 'past_due')
         AND i0.amount_due > 0
       GROUP BY i0.tenant_id
     )
     SELECT
       tt0.tenant_name AS tenant_name,
       tt0.currency_code AS currency_code,
       ROUND(COALESCE(rt0.total_2025_revenue_eur, 0), 2) AS total_2025_revenue_eur,
       ROUND(COALESCE(rt0.total_2025_revenue_eur, 0) / NULLIF(ra0.total_revenue_eur_all, 0) * 100, 1) AS pct_of_total_2025_revenue,
       ROUND(COALESCE(oi0.open_amount_due_eur, 0), 2) AS open_amount_due_eur,
       ROUND(
         CASE WHEN COALESCE(oi0.open_amount_due_eur, 0) = 0 THEN NULL
              ELSE oi0.overdue_amount_due_eur / oi0.open_amount_due_eur * 100 END
       , 1) AS pct_amount_due_overdue
     FROM target_tenants tt0
     LEFT JOIN revenue_tenant rt0 ON rt0.tenant_id = tt0.tenant_id
     CROSS JOIN revenue_all ra0
     LEFT JOIN open_invoices_tenant oi0 ON oi0.tenant_id = tt0.tenant_id
     ORDER BY total_2025_revenue_eur DESC, tt0.tenant_name

   • The warehouse answers

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

   • BeaSubmitted. The report computes, per tenant: 2025 revenue (EUR-converted with the EUR-passthrough fix), its share of company-wide 2025 revenue, open receivables as of 2025-12-31, and the overdue share of those receivables — ordered by revenue descending, one row per tenant.

   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 (275 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)