Queries · q302 · Head of Data · week 2, night 2

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

“Which tenants are actually operating as B2B wholesalers (by using the deals/CRM module, not just by industry label), how much 2025 revenue do they represent, and what does their receivables picture look like on the corrected tenant list?”

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

   Three things from last night change my plan. (1) The age-bucket report is reassuring but not a clean monotonic ramp: match rate is 100% at 0-7 days, dips to 83.4% at 8-14 days, climbs through the 90s, then settles around 98.9-100.1% from 91 days onward - so the true permanent "never collected" floor looks like just ~1-2%, but that mid-range dip needs an explanation before I tell Rosa this is purely timing; my best guess is a payment method with inherent capture lag (bank transfer/wire), which I want to test directly. (2) The refund-concentration report confirms Rosa's worry: the top 4 merchants (Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane, Studio Vrij) account for 70.8% of all late-processed refunds in 2025, with Cragsley alone at 36.2% and still growing (+36.2% H1-to-H2) - this is genuinely concentrated, not spread thin, and it sits on our single biggest merchant. (3) The industry-revenue report was the big surprise: B2B wholesale (Office Supplies + Industrial Supplies) is 38.9% of 2025 collected revenue across just 3 tenants - far too material to leave out of the board pack, which changes the stakes on getting that receivables number right. But it also only found 3 B2B tenants by industry label, while Daniel Kim's team (last week's channel) referenced 5 B2B wholesale merchants - that gap is worth resolving properly now, by identifying B2B tenants operationally (via actual CRM deal activity) rather than by industry-text label, and recomputing their receivables on the corrected set. I'm also starting on Rosa's customer-definition-usage question tonight by surveying what audience/segment definitions already exist and are actively used elsewhere in the business.

   Read the whole plan
   B2B wholesale tenants identified by CRM deal activity, with revenue and receivables

   Question

   Which tenants are actually operating as B2B wholesalers (by using the deals/CRM module, not just by industry label), how much 2025 revenue do they represent, and what does their receivables picture look like on the corrected tenant list?

   One row per

   tenant

   Columns

   • tenant_name
   • industry
   • deal_count
   • total_contract_value_eur
   • total_2025_revenue_eur
   • pct_of_total_2025_revenue
   • open_amount_due_eur
   • pct_amount_due_overdue

4. Felix 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
     tenant_agg.tenant_name,
     tenant_agg.industry,
     tenant_agg.deal_count,
     ROUND(tenant_agg.total_contract_value_eur, 2) AS total_contract_value_eur,
     ROUND(tenant_agg.total_2025_revenue_eur, 2) AS total_2025_revenue_eur,
     ROUND(tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100, 1) AS pct_of_total_2025_revenue,
     ROUND(tenant_agg.open_amount_due_eur, 2) AS open_amount_due_eur,
     tenant_agg.pct_amount_due_overdue
   FROM (
     SELECT
       t.id,
       t.name AS tenant_name,
       t.industry,
       (SELECT COUNT(*) FROM deals d WHERE d.tenant_id = t.id) AS deal_count,
       (SELECT COALESCE(SUM(d.contract_value / er.rate), 0)
          FROM deals d
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = d.currency_code
           AND er.as_of = COALESCE(d.signed_at, CAST(d.created_at AS date))
         WHERE d.tenant_id = t.id
       ) AS total_contract_value_eur,
       (SELECT COALESCE(SUM(p.amount / er.rate), 0)
          FROM payments p
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = p.currency_code
           AND er.as_of = CAST(p.captured_at AS date)
         WHERE p.tenant_id = t.id
           AND p.status IN ('captured', 'refunded', 'partially_refunded')
           AND p.captured_at >= DATE '2025-01-01'
           AND p.captured_at < DATE '2026-01-01'
       ) AS total_2025_revenue_eur,
       (SELECT COALESCE(SUM(i.amount_due / er.rate), 0)
          FROM invoices i
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = i.currency_code
           AND er.as_of = DATE '2025-12-31'
         WHERE i.tenant_id = t.id
           AND i.status IN ('open', 'past_due')
           AND i.amount_due > 0
       ) AS open_amount_due_eur,
       (SELECT CASE
                 WHEN SUM(i.amount_due / er.rate) = 0 THEN NULL
                 ELSE ROUND(
                        SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN i.amount_due / er.rate ELSE 0 END)
                        / SUM(i.amount_due / er.rate) * 100, 1)
               END
          FROM invoices i
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = i.currency_code
           AND er.as_of = DATE '2025-12-31'
         WHERE i.tenant_id = t.id
           AND i.status IN ('open', 'past_due')
           AND i.amount_due > 0
       ) AS pct_amount_due_overdue
     FROM tenants t
     WHERE EXISTS (SELECT 1 FROM deals d WHERE d.tenant_id = t.id)
   ) AS tenant_agg
   CROSS JOIN (
     SELECT COALESCE(SUM(p2.amount / er2.rate), 0) AS total
     FROM payments p2
     JOIN exchange_rates er2
       ON er2.base_code = 'EUR'
      AND er2.quote_code = p2.currency_code
      AND er2.as_of = CAST(p2.captured_at AS date)
     WHERE p2.status IN ('captured', 'refunded', 'partially_refunded')
       AND p2.captured_at >= DATE '2025-01-01'
       AND p2.captured_at < DATE '2026-01-01'
   ) AS company_total
   ORDER BY tenant_agg.total_2025_revenue_eur DESC, tenant_agg.tenant_name

   “Query compiles and returns the expected columns. 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 what the report does: - Population: tenants with ≥1 row in deals (per the briefing, this is the module used by B2B wholesale merchants, regardless of their stored industry label). - deal_count / total_contract_value_eur: counted and summed from deals, converted to EUR using the EUR→currency rate as of signed_at (or created_at date if signed_at is null). - total_2025_revenue_eur: sum of payments.amount for status in captured/refunded/partially_refunded captured in 2025, EUR-converted by captured_at date. - pct_of_total_2025_revenue: each tenant's 2025 revenue divided by the company-wide 2025 revenue total (all tenants, not just this list). - open_amount_due_eur: invoices.amount_due for status open/past_due with amount_due > 0, as of the 2025-12-31 snapshot rate. - pct_amount_due_overdue: share of that open balance whose due_at was before 2025-12-31; left NULL where open_amount_due_eur is zero (undefined ratio). Ordered by 2025 revenue descending, one row per tenant, 8 columns — within the page limits.”

5. The report comes back overnight

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

   tenant_name                   | industry                  | deal_count | total_contract_value_eur | total_2025_revenue_eur | pct_of_total_2025_revenue | open_amount_due_eur | pct_amount_due_overdue
   ------------------------------+---------------------------+------------+--------------------------+------------------------+---------------------------+---------------------+-----------------------
   OfficeHub Nordic              | Office Supplies (B2B)     |         78 |                582407.88 |              426010.27 |                      15.2 |            14348.57 |                    2.5
   Castell Fasteners             | Industrial Supplies (B2B) |         68 |                 519795.7 |              386487.62 |                      13.7 |            14481.02 |                   23.8
   Deskline Supplies             | Office Supplies (B2B)     |         11 |                 92786.27 |              281736.43 |                      10.0 |            12749.42 |                    0.0
   Ballycorran Industrial Supply | Industrial Supplies (B2B) |        136 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   Hanse Industriebedarf         | Industrial Supplies (B2B) |         32 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   Kontor24 Bürobedarf           | Office Supplies (B2B)     |        114 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   Midland Safety Wear           | Industrial Supplies (B2B) |         77 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   Papeterie Grossiste           | Office Supplies (B2B)     |          6 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   Polder Packaging              | Industrial Supplies (B2B) |         21 |                      0.0 |                    0.0 |                       0.0 |                 0.0 |                   NULL
   (9 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:

   • q305Diagnose zero-value B2B deal tenants in native currency
   • q306Castell Fasteners open invoices in detail
   • q308Corrected 2025 revenue and receivables by B2B tenant

   It builds on:

   • q3012025 revenue share by industry vertical
   • q097B2B wholesaler receivables aging

7. It becomes benchmark query q302

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

   • determinismtie-breaker: tenants primary key
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (7×)
   • made explicita selected t.col gets AS col, its standard name made explicit (6×)
   • 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

   SELECT  tenant_agg.tenant_name,  tenant_agg.industry,  tenant_agg.deal_count,  ROUND(tenant_agg.total_contract_value_eur, 2) AS total_contract_value_eur,  ROUND(tenant_agg.total_2025_revenue_eur, 2) AS total_2025_revenue_eur,  ROUND(tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100, 1) AS pct_of_total_2025_revenue,  ROUND(tenant_agg.open_amount_due_eur, 2) AS open_amount_due_eur,  tenant_agg.pct_amount_due_overdue  tenant_agg.tenant_name AS tenant_name,  tenant_agg.industry AS industry,  tenant_agg.deal_count AS deal_count,  tenant_agg.total_contract_value_eur AS total_contract_value_eur,  tenant_agg.total_2025_revenue_eur AS total_2025_revenue_eur,  tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100 AS pct_of_total_2025_revenue,  tenant_agg.open_amount_due_eur AS open_amount_due_eur,  tenant_agg.pct_amount_due_overdue AS pct_amount_due_overdueFROM (  SELECT    t.id,    t.id AS id,    t.name AS tenant_name,    t.industry,    t.industry AS industry,    (SELECT COUNT(*) FROM deals d WHERE d.tenant_id = t.id) AS deal_count,    (SELECT COALESCE(SUM(d.contract_value / er.rate), 0)    (SELECT COALESCE(SUM(CAST(d.contract_value / er.rate AS NUMERIC(18,6))), 0)       FROM deals d       JOIN exchange_rates er         ON er.base_code = 'EUR'        AND er.quote_code = d.currency_code        AND er.as_of = COALESCE(d.signed_at, CAST(d.created_at AS date))      WHERE d.tenant_id = t.id    ) AS total_contract_value_eur,    (SELECT COALESCE(SUM(p.amount / er.rate), 0)    (SELECT COALESCE(SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))), 0)       FROM payments p       JOIN exchange_rates er         ON er.base_code = 'EUR'        AND er.quote_code = p.currency_code        AND er.as_of = CAST(p.captured_at AS date)      WHERE p.tenant_id = t.id        AND p.status IN ('captured', 'refunded', 'partially_refunded')        AND p.captured_at >= DATE '2025-01-01'        AND p.captured_at < DATE '2026-01-01'    ) AS total_2025_revenue_eur,    (SELECT COALESCE(SUM(i.amount_due / er.rate), 0)    (SELECT COALESCE(SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))), 0)       FROM invoices i       JOIN exchange_rates er         ON er.base_code = 'EUR'        AND er.quote_code = i.currency_code        AND er.as_of = DATE '2025-12-31'      WHERE i.tenant_id = t.id        AND i.status IN ('open', 'past_due')        AND i.amount_due > 0    ) AS open_amount_due_eur,    (SELECT CASE              WHEN SUM(i.amount_due / er.rate) = 0 THEN NULL              ELSE ROUND(                     SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN i.amount_due / er.rate ELSE 0 END)                     / SUM(i.amount_due / er.rate) * 100, 1)              WHEN SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))) = 0 THEN NULL              ELSE SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN CAST(i.amount_due / er.rate AS NUMERIC(18,6)) ELSE 0 END)                   / SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))) * 100            END       FROM invoices i       JOIN exchange_rates er         ON er.base_code = 'EUR'        AND er.quote_code = i.currency_code        AND er.as_of = DATE '2025-12-31'      WHERE i.tenant_id = t.id        AND i.status IN ('open', 'past_due')        AND i.amount_due > 0    ) AS pct_amount_due_overdue  FROM tenants t  WHERE EXISTS (SELECT 1 FROM deals d WHERE d.tenant_id = t.id)) AS tenant_aggCROSS JOIN (  SELECT COALESCE(SUM(p2.amount / er2.rate), 0) AS total  SELECT COALESCE(SUM(CAST(p2.amount / er2.rate AS NUMERIC(18,6))), 0) AS total  FROM payments p2  JOIN exchange_rates er2    ON er2.base_code = 'EUR'   AND er2.quote_code = p2.currency_code   AND er2.as_of = CAST(p2.captured_at AS date)  WHERE p2.status IN ('captured', 'refunded', 'partially_refunded')    AND p2.captured_at >= DATE '2025-01-01'    AND p2.captured_at < DATE '2026-01-01') AS company_totalORDER BY tenant_agg.total_2025_revenue_eur DESC, tenant_agg.tenant_nameORDER BY tenant_agg.total_2025_revenue_eur DESC NULLS FIRST, tenant_agg.tenant_name NULLS LAST, tenant_agg.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

   SELECT
     tenant_agg.tenant_name AS tenant_name,
     tenant_agg.industry AS industry,
     tenant_agg.deal_count AS deal_count,
     tenant_agg.total_contract_value_eur AS total_contract_value_eur,
     tenant_agg.total_2025_revenue_eur AS total_2025_revenue_eur,
     tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100 AS pct_of_total_2025_revenue,
     tenant_agg.open_amount_due_eur AS open_amount_due_eur,
     tenant_agg.pct_amount_due_overdue AS pct_amount_due_overdue
   FROM (
     SELECT
       t.id AS id,
       t.name AS tenant_name,
       t.industry AS industry,
       (SELECT COUNT(*) FROM deals d WHERE d.tenant_id = t.id) AS deal_count,
       (SELECT COALESCE(SUM(CAST(d.contract_value / er.rate AS NUMERIC(18,6))), 0)
          FROM deals d
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = d.currency_code
           AND er.as_of = COALESCE(d.signed_at, CAST(d.created_at AS date))
         WHERE d.tenant_id = t.id
       ) AS total_contract_value_eur,
       (SELECT COALESCE(SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))), 0)
          FROM payments p
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = p.currency_code
           AND er.as_of = CAST(p.captured_at AS date)
         WHERE p.tenant_id = t.id
           AND p.status IN ('captured', 'refunded', 'partially_refunded')
           AND p.captured_at >= DATE '2025-01-01'
           AND p.captured_at < DATE '2026-01-01'
       ) AS total_2025_revenue_eur,
       (SELECT COALESCE(SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))), 0)
          FROM invoices i
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = i.currency_code
           AND er.as_of = DATE '2025-12-31'
         WHERE i.tenant_id = t.id
           AND i.status IN ('open', 'past_due')
           AND i.amount_due > 0
       ) AS open_amount_due_eur,
       (SELECT CASE
                 WHEN SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))) = 0 THEN NULL
                 ELSE SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN CAST(i.amount_due / er.rate AS NUMERIC(18,6)) ELSE 0 END)
                      / SUM(CAST(i.amount_due / er.rate AS NUMERIC(18,6))) * 100
               END
          FROM invoices i
          JOIN exchange_rates er
            ON er.base_code = 'EUR'
           AND er.quote_code = i.currency_code
           AND er.as_of = DATE '2025-12-31'
         WHERE i.tenant_id = t.id
           AND i.status IN ('open', 'past_due')
           AND i.amount_due > 0
       ) AS pct_amount_due_overdue
     FROM tenants t
     WHERE EXISTS (SELECT 1 FROM deals d WHERE d.tenant_id = t.id)
   ) AS tenant_agg
   CROSS JOIN (
     SELECT COALESCE(SUM(CAST(p2.amount / er2.rate AS NUMERIC(18,6))), 0) AS total
     FROM payments p2
     JOIN exchange_rates er2
       ON er2.base_code = 'EUR'
      AND er2.quote_code = p2.currency_code
      AND er2.as_of = CAST(p2.captured_at AS date)
     WHERE p2.status IN ('captured', 'refunded', 'partially_refunded')
       AND p2.captured_at >= DATE '2025-01-01'
       AND p2.captured_at < DATE '2026-01-01'
   ) AS company_total
   ORDER BY tenant_agg.total_2025_revenue_eur DESC NULLS FIRST, tenant_agg.tenant_name NULLS LAST, tenant_agg.id NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 16, Column 48: 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": "0df94151-e8df-434d-8f59-d8fd26493e16", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.contract_value Decimal(14, 4) Decimal64(size = 0), __table6.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

   Felix's whole session, 7 turns

   • The request, as Felix received it

     Report request from Yuki Tanaka (analyst for the Head of Data):
     
     Title: B2B wholesale tenants identified by CRM deal activity, with revenue and receivables
     Question: Which tenants are actually operating as B2B wholesalers (by using the deals/CRM module, not just by industry label), how much 2025 revenue do they represent, and what does their receivables picture look like on the corrected tenant list?
     Grain: tenant
     Columns:
     - tenant_name
     - industry
     - deal_count
     - total_contract_value_eur
     - total_2025_revenue_eur
     - pct_of_total_2025_revenue
     - open_amount_due_eur
     - pct_amount_due_overdue
     Details: Population: every tenant that has at least one row in the deals table (closed-won B2B contracts), which per the company briefing is the module used by B2B wholesale merchants. For each such tenant: industry = tenants.industry (as stored, for cross-reference against last night's industry report). deal_count = count of deals rows. total_contract_value_eur = sum of deals.contract_value converted to EUR via exchange_rates (base_code='EUR', quote_code=deals.currency_code, as_of=signed_at date, or created_at date if signed_at is null). total_2025_revenue_eur = sum of this tenant's payments.amount where status in ('captured','refunded','partially_refunded') and captured_at in 2025, converted to EUR via 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, not just this list) * 100, rounded to 1 decimal. open_amount_due_eur = sum of invoices.amount_due for this tenant where invoices.status in ('open','past_due') and amount_due > 0, as of snapshot 2025-12-31, converted to EUR via 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. 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.

   • Felix calls dry_run

     SELECT
       tenant_agg.tenant_name,
       tenant_agg.industry,
       tenant_agg.deal_count,
       ROUND(tenant_agg.total_contract_value_eur, 2) AS total_contract_value_eur,
       ROUND(tenant_agg.total_2025_revenue_eur, 2) AS total_2025_revenue_eur,
       ROUND(tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100, 1) AS pct_of_total_2025_revenue,
       ROUND(tenant_agg.open_amount_due_eur, 2) AS open_amount_due_eur,
       tenant_agg.pct_amount_due_overdue
     FROM (
       SELECT
         t.id,
         t.name AS tenant_name,
         t.industry,
         (SELECT COUNT(*) FROM deals d WHERE d.tenant_id = t.id) AS deal_count,
         (SELECT COALESCE(SUM(d.contract_value / er.rate), 0)
            FROM deals d
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = d.currency_code
             AND er.as_of = COALESCE(d.signed_at, CAST(d.created_at AS date))
           WHERE d.tenant_id = t.id
         ) AS total_contract_value_eur,
         (SELECT COALESCE(SUM(p.amount / er.rate), 0)
            FROM payments p
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = p.currency_code
             AND er.as_of = CAST(p.captured_at AS date)
           WHERE p.tenant_id = t.id
             AND p.status IN ('captured', 'refunded', 'partially_refunded')
             AND p.captured_at >= DATE '2025-01-01'
             AND p.captured_at < DATE '2026-01-01'
         ) AS total_2025_revenue_eur,
         (SELECT COALESCE(SUM(i.amount_due / er.rate), 0)
            FROM invoices i
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = i.currency_code
             AND er.as_of = DATE '2025-12-31'
           WHERE i.tenant_id = t.id
             AND i.status IN ('open', 'past_due')
             AND i.amount_due > 0
         ) AS open_amount_due_eur,
         (SELECT CASE
                   WHEN SUM(i.amount_due / er.rate) = 0 THEN NULL
                   ELSE ROUND(
                          SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN i.amount_due / er.rate ELSE 0 END)
                          / SUM(i.amount_due / er.rate) * 100, 1)
                 END
            FROM invoices i
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = i.currency_code
             AND er.as_of = DATE '2025-12-31'
           WHERE i.tenant_id = t.id
             AND i.status IN ('open', 'past_due')
             AND i.amount_due > 0
         ) AS pct_amount_due_overdue
       FROM tenants t
       WHERE EXISTS (SELECT 1 FROM deals d WHERE d.tenant_id = t.id)
     ) AS tenant_agg
     CROSS JOIN (
       SELECT COALESCE(SUM(p2.amount / er2.rate), 0) AS total
       FROM payments p2
       JOIN exchange_rates er2
         ON er2.base_code = 'EUR'
        AND er2.quote_code = p2.currency_code
        AND er2.as_of = CAST(p2.captured_at AS date)
       WHERE p2.status IN ('captured', 'refunded', 'partially_refunded')
         AND p2.captured_at >= DATE '2025-01-01'
         AND p2.captured_at < DATE '2026-01-01'
     ) AS company_total
     ORDER BY tenant_agg.total_2025_revenue_eur DESC, tenant_agg.tenant_name

   • The warehouse answers

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

   • FelixQuery compiles and returns the expected columns. Submitting.
   • Felix calls submit

     SELECT
       tenant_agg.tenant_name,
       tenant_agg.industry,
       tenant_agg.deal_count,
       ROUND(tenant_agg.total_contract_value_eur, 2) AS total_contract_value_eur,
       ROUND(tenant_agg.total_2025_revenue_eur, 2) AS total_2025_revenue_eur,
       ROUND(tenant_agg.total_2025_revenue_eur / NULLIF(company_total.total, 0) * 100, 1) AS pct_of_total_2025_revenue,
       ROUND(tenant_agg.open_amount_due_eur, 2) AS open_amount_due_eur,
       tenant_agg.pct_amount_due_overdue
     FROM (
       SELECT
         t.id,
         t.name AS tenant_name,
         t.industry,
         (SELECT COUNT(*) FROM deals d WHERE d.tenant_id = t.id) AS deal_count,
         (SELECT COALESCE(SUM(d.contract_value / er.rate), 0)
            FROM deals d
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = d.currency_code
             AND er.as_of = COALESCE(d.signed_at, CAST(d.created_at AS date))
           WHERE d.tenant_id = t.id
         ) AS total_contract_value_eur,
         (SELECT COALESCE(SUM(p.amount / er.rate), 0)
            FROM payments p
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = p.currency_code
             AND er.as_of = CAST(p.captured_at AS date)
           WHERE p.tenant_id = t.id
             AND p.status IN ('captured', 'refunded', 'partially_refunded')
             AND p.captured_at >= DATE '2025-01-01'
             AND p.captured_at < DATE '2026-01-01'
         ) AS total_2025_revenue_eur,
         (SELECT COALESCE(SUM(i.amount_due / er.rate), 0)
            FROM invoices i
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = i.currency_code
             AND er.as_of = DATE '2025-12-31'
           WHERE i.tenant_id = t.id
             AND i.status IN ('open', 'past_due')
             AND i.amount_due > 0
         ) AS open_amount_due_eur,
         (SELECT CASE
                   WHEN SUM(i.amount_due / er.rate) = 0 THEN NULL
                   ELSE ROUND(
                          SUM(CASE WHEN i.due_at < TIMESTAMP '2025-12-31 00:00:00' THEN i.amount_due / er.rate ELSE 0 END)
                          / SUM(i.amount_due / er.rate) * 100, 1)
                 END
            FROM invoices i
            JOIN exchange_rates er
              ON er.base_code = 'EUR'
             AND er.quote_code = i.currency_code
             AND er.as_of = DATE '2025-12-31'
           WHERE i.tenant_id = t.id
             AND i.status IN ('open', 'past_due')
             AND i.amount_due > 0
         ) AS pct_amount_due_overdue
       FROM tenants t
       WHERE EXISTS (SELECT 1 FROM deals d WHERE d.tenant_id = t.id)
     ) AS tenant_agg
     CROSS JOIN (
       SELECT COALESCE(SUM(p2.amount / er2.rate), 0) AS total
       FROM payments p2
       JOIN exchange_rates er2
         ON er2.base_code = 'EUR'
        AND er2.quote_code = p2.currency_code
        AND er2.as_of = CAST(p2.captured_at AS date)
       WHERE p2.status IN ('captured', 'refunded', 'partially_refunded')
         AND p2.captured_at >= DATE '2025-01-01'
         AND p2.captured_at < DATE '2026-01-01'
     ) AS company_total
     ORDER BY tenant_agg.total_2025_revenue_eur DESC, tenant_agg.tenant_name

   • The warehouse answers

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

   • FelixSubmitted. Summary of what the report does: - **Population**: tenants with ≥1 row in `deals` (per the briefing, this is the module used by B2B wholesale merchants, regardless of their stored `industry` label). - **deal_count / total_contract_value_eur**: counted and summed from `deals`, converted to EUR using the EUR→currency rate as of `signed_at` (or `created_at` date if `signed_at` is null). - **total_2025_revenue_eur**: sum of `payments.amount` for status in `captured/refunded/partially_refunded` captured in 2025, EUR-converted by `captured_at` date. - **pct_of_total_2025_revenue**: each tenant's 2025 revenue divided by the company-wide 2025 revenue total (all tenants, not just this list). - **open_amount_due_eur**: `invoices.amount_due` for status `open`/`past_due` with `amount_due > 0`, as of the 2025-12-31 snapshot rate. - **pct_amount_due_overdue**: share of that open balance whose `due_at` was before 2025-12-31; left NULL where open_amount_due_eur is zero (undefined ratio). Ordered by 2025 revenue descending, one row per tenant, 8 columns — within the page limits.

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

   Felix's transcript (281 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 (13 KB) · the week's memo (3 KB)