DB Olympics

Queries · q212 · Chief Executive Officer · week 2, night 2

Receivables aging: who owes us and for how long

“Which merchants are slowest to pay their invoices, how large and how overdue are the balances, and is this concentrated in B2B wholesale?”

Manager · asked the questionMaren OkaforChief Executive Officer. Joined two weeks ago from a European payments company. Board meeting at the end of January.
Analyst · planned the reportHannah BrooksMethodical: starts with an overview, then drills into whatever looks unusual.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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 Chief Executive Officer's desk

    Week 2; shared with Tobias Lindqvist, Chief Financial Officer

    The board meets at the end of January and wants a clear view of unit economics: per merchant, vertical and country, what Tidewick earns and what it costs us to serve them. Which merchants are worth growing, and which cost more than they bring?

    One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events

  2. Maren Okafor writes to Hannah Brooks

    The manager, at the start of week 2

    Subject: Great first week — here's where I want to go next

    Hannah,

    This was exactly the kind of grounding I needed — thank you. A few things surprised me and one big new ask from the board that I need us to lead together with Tobias.

    What surprised me / needs explaining:

    • Your fee-revenue figure (€53k trailing twelve months) and Tobias's (€93.6k for calendar 2025) don't match, and neither does your "72% from top 5, stable" concentration story line up cleanly with his "65% from top 10, declining as we add merchants." Before this goes near the board I need these reconciled into one number and one trend — I don't want finance and analytics telling different stories about the same business.
    • The refund story looks more nuanced than "refunds are creeping up broadly." Tobias's team says apparel's ~20% refund rate is just normal for the category, and the real spike this quarter came from a few smaller non-apparel merchants. I'd like your root-cause work to confirm or correct that before we flag it as a trend versus a one-off.
    • Operations found that delivery performance is almost entirely a carrier issue, not a merchant or warehouse issue — the same merchant looks great on one carrier and bad on another, and one carrier partner has clearly been underperforming since November. That changes how I think about "merchant health" — some of what looks like a merchant problem may really be a carrier problem.

    The new board ask — unit economics by merchant, vertical and country:

    The board wants to know, plainly, what we earn from serving each merchant/vertical/country and what it costs us — so we can say which parts of the business are worth leaning into and which are quietly expensive. This is bigger than one report, so here's what I think it needs, and I want you and Tobias to scope it jointly this week rather than me prescribing the method:

    • On the earnings side, we seem to have good data (processing fees, volume). Platform-plan fees are apparently missing from the warehouse — Tobias, can your team supply that separately so we're not missing a real revenue line?
    • On the cost side, I don't think we've ever assembled this: fulfillment/carrier cost, refund and return handling, support load, and the engineering reliability issues (webhook failures, the cart-reminder bug) that generate extra support tickets. Some of this may not be cleanly attributable per merchant in the data — I want an honest read on what's knowable now versus what needs to be modeled or estimated.
    • The deliverable I need by month-end: a short ranked view of which merchants/verticals/countries are clearly profitable to grow, which are marginal, and which likely cost more to serve than they return — even if the first pass is directional rather than precise.

    My questions for this week:

    1. Can you and Tobias reconcile the revenue and concentration numbers into a single, agreed figure and trend I can quote to the board without contradiction?
    2. What would it take to build even a rough cost-to-serve picture per merchant/vertical/country — and what's your honest confidence in it, given the data gaps you've already flagged (platform fees, refund-to-payout matching, carrier cost attribution)?
    3. On refunds — is the increase really concentrated in a few merchants/verticals as Tobias's team suggests, or is it broader? I want to know whether this is a few accounts to manage or a trend to worry about.
    4. Separately, flag for me which of our top accounts (like Castell Fasteners, with its traffic drop and unpaid invoices) look at risk — I want a short "accounts to watch" list alongside the unit economics work, not buried inside it.

    Let's talk briefly before you lock in this week's requests so we spend the slots on the unit economics scoping rather than re-running what we already mostly know.

    Thanks, Maren

    A note from the Chief Financial Officer, Tobias Lindqvist

    Note to Hannah Brooks

    Hannah — a few things from the finance side as you scope the board unit-economics piece:

    1. What does each merchant actually net us, after processing costs? We only see the economics clearly once payouts are broken into gross, fee, and net — a merchant with huge volume but thin margin on our fee side could look better on revenue than it really is on contribution.
    2. How exposed are we on currency? We earn and hold balances in many merchant currencies against a EUR view for the board — I want to know where FX swings could meaningfully move the 2025 numbers or the 2026 budget.
    3. Who's slow to pay us, and where? For the B2B wholesalers especially, I'd like a sense of outstanding receivables and how long they're taking to settle — that's a cost of serving them that doesn't show up in the headline revenue.

    If there's room, a rough read on how refund patterns differ by vertical or country would help round out the "which merchants are worth growing" question.

    Happy to talk through before Friday's memo.

    — Tobias

  3. Hannah Brooks plans the night

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

    Night 1 results: the currency breakdown report confirms my €53,186 figure to the penny (sums exactly), with no currency-specific anomaly — solid evidence that Finance's €93,583 has a methodology bug on their side, which I'll bring to a joint session with Tobias's team rather than keep guessing blindly at their query. On refunds: my Q4 2025 merchant breakdown shows our two biggest Apparel merchants (Cragsley, Marrowby & Vane) actually improved slightly, and the spike is driven by small long-tail merchants (Fell & Fjell, Bytefield, Brick & Bobbin) with big % jumps but tiny order counts — consistent with Kofi's "concentrated, not broad" read. But Kofi specifically named Q3 2025 and OfficeHub Nordic, and my data shows OfficeHub Nordic at 0% refund rate in Q4 — so I need the Q3-specific breakdown to confirm or correct his named examples before this goes to Maren as settled. Separately, the risk snapshot surfaced a much bigger receivables problem than Castell Fasteners: Kontor24 Bürobedarf has €116.7k unpaid (8x Castell's), and the biggest unpaid balances are all B2B wholesalers — directly matching Tobias's receivables question. Tonight: (1) redo the refund check on Kofi's actual Q3 window to nail the root-cause question, (2) a receivables-aging report to answer Tobias's "who's slow to pay" question and deepen the watch-list, (3) start the unit-economics groundwork with a cost-driver volume inventory per merchant (shipping, returns, refunds, tickets, webhook failures) — the measurable building blocks Tobias and I will need to assign real costs to.

    Receivables aging: who owes us and for how long
    Question
    Which merchants are slowest to pay their invoices, how large and how overdue are the balances, and is this concentrated in B2B wholesale?
    One row per
    merchant (tenant) with a nonzero unpaid invoice balance
    Columns
    • merchant name
    • vertical
    • total unpaid balance (EUR, sum of amount_due on open/past_due invoices)
    • count of open/past_due invoices
    • days overdue on the oldest unpaid invoice (as of 2026-01-05, using due_at)
    • largest single unpaid invoice (EUR)
    • net payment terms (days, from billing_profiles.net_terms_days)

  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.name AS merchant_name,
       COALESCE(t0.industry, 'Unknown') AS vertical,
       ROUND(COALESCE(m1.total_unpaid_eur,0),2) AS total_unpaid_balance_eur,
       COALESCE(m2.open_invoice_count,0) AS open_invoice_count,
       COALESCE(m3.days_overdue_oldest,0) AS days_overdue_oldest,
       ROUND(COALESCE(m4.largest_unpaid_eur,0),2) AS largest_unpaid_invoice_eur,
       m5.net_terms_days AS net_terms_days
FROM tenants t0
INNER JOIN (
    SELECT i0.tenant_id,
           SUM(i0.amount_due / CASE WHEN i0.currency_code = 'EUR' THEN 1 ELSE fx0.rate END) AS total_unpaid_eur
    FROM invoices i0
    LEFT JOIN (
        SELECT er0.quote_code AS currency_code, er0.rate
        FROM exchange_rates er0
        WHERE er0.base_code = 'EUR'
          AND er0.as_of = (
              SELECT MAX(er1.as_of)
              FROM exchange_rates er1
              WHERE er1.base_code = 'EUR'
                AND er1.quote_code = er0.quote_code
                AND er1.as_of <= DATE '2025-12-31'
          )
    ) fx0 ON fx0.currency_code = i0.currency_code
    WHERE i0.status IN ('open','past_due') AND i0.amount_due > 0
    GROUP BY i0.tenant_id
) m1 ON m1.tenant_id = t0.id
LEFT JOIN (
    SELECT i2.tenant_id, COUNT(*) AS open_invoice_count
    FROM invoices i2
    WHERE i2.status IN ('open','past_due') AND i2.amount_due > 0
    GROUP BY i2.tenant_id
) m2 ON m2.tenant_id = t0.id
LEFT JOIN (
    SELECT i3.tenant_id,
           GREATEST(0, CAST(DATE '2026-01-05' - CAST(MIN(i3.due_at) AS DATE) AS INTEGER)) AS days_overdue_oldest
    FROM invoices i3
    WHERE i3.status IN ('open','past_due') AND i3.amount_due > 0
    GROUP BY i3.tenant_id
) m3 ON m3.tenant_id = t0.id
LEFT JOIN (
    SELECT i4.tenant_id,
           MAX(i4.amount_due / CASE WHEN i4.currency_code='EUR' THEN 1 ELSE fx4.rate END) AS largest_unpaid_eur
    FROM invoices i4
    LEFT JOIN (
        SELECT er2.quote_code AS currency_code, er2.rate
        FROM exchange_rates er2
        WHERE er2.base_code='EUR'
          AND er2.as_of = (
              SELECT MAX(er3.as_of) FROM exchange_rates er3
              WHERE er3.base_code='EUR' AND er3.quote_code=er2.quote_code AND er3.as_of <= DATE '2025-12-31'
          )
    ) fx4 ON fx4.currency_code = i4.currency_code
    WHERE i4.status IN ('open','past_due') AND i4.amount_due > 0
    GROUP BY i4.tenant_id
) m4 ON m4.tenant_id = t0.id
LEFT JOIN (
    SELECT bp0.tenant_id, MAX(bp0.net_terms_days) AS net_terms_days
    FROM billing_profiles bp0
    WHERE bp0.is_default = true AND bp0.deleted_at IS NULL
    GROUP BY bp0.tenant_id
) m5 ON m5.tenant_id = t0.id
WHERE COALESCE(m1.total_unpaid_eur,0) <> 0
ORDER BY total_unpaid_balance_eur DESC, days_overdue_oldest DESC, t0.id ASC
LIMIT 50

    “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 query does: - Grain: one row per tenant with a nonzero unpaid invoice balance. - Filter: invoices with status IN ('open','past_due') and amount_due > 0. - FX: converts each invoice's amount_due to EUR using the most recent exchange_rates row (base_code = 'EUR') with as_of <= 2025-12-31 for that currency — a single snapshot rate per currency, as requested. - Measures (each built as its own subquery joined back on tenant_id, per house style): total unpaid EUR, count of open/past-due invoices, days overdue on the oldest unpaid invoice (floored, clamped to 0), largest single unpaid invoice in EUR, and net payment terms from the tenant's default billing_profiles row. - Ordering: total unpaid balance EUR descending, then days overdue descending, with tenant id as a final deterministic tie-break. - Limit: top 50 rows, as specified; the batch report will show the full matching row count. Noted in the submission: the net-terms join assumes one default billing profile per tenant (took the max if duplicates existed), and the FX conversion is a single year-end snapshot rate rather than a per-invoice historical rate, consistent with the prior risk-snapshot report.”

  5. The report comes back overnight

    As Hannah Brooks saw it, from the small dataset the agents worked on

    merchant_name                 | vertical                  | total_unpaid_balance_eur | open_invoice_count | days_overdue_oldest | largest_unpaid_invoice_eur | net_terms_days
------------------------------+---------------------------+--------------------------+--------------------+---------------------+----------------------------+---------------
Kontor24 Bürobedarf           | Office Supplies (B2B)     |                116652.86 |                 16 |                  13 |                   49396.29 |           NULL
Ballycorran Industrial Supply | Industrial Supplies (B2B) |                 22415.24 |                 29 |                  10 |                    4097.99 |           NULL
Castell Fasteners             | Industrial Supplies (B2B) |                 14481.02 |                 11 |                  29 |                    3537.39 |           NULL
OfficeHub Nordic              | Office Supplies (B2B)     |                 14348.57 |                 13 |                  38 |                    3070.98 |           NULL
Deskline Supplies             | Office Supplies (B2B)     |                 12749.42 |                  6 |                   3 |                    5000.42 |           NULL
Midland Safety Wear           | Industrial Supplies (B2B) |                 10049.47 |                  9 |                   7 |                    4437.03 |           NULL
Polder Packaging              | Industrial Supplies (B2B) |                   5296.8 |                  5 |                  12 |                    2697.74 |           NULL
Hanse Industriebedarf         | Industrial Supplies (B2B) |                   864.14 |                  1 |                   0 |                     864.14 |           NULL
Brennquell Kaffee             | Food & Coffee             |                    41.84 |                  1 |                   6 |                      41.84 |           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:

    It builds on:

  7. It becomes benchmark query q212

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

    • spellingfetch first
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit (7×)
    • 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×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    SELECT t0.name AS merchant_name,       COALESCE(t0.industry, 'Unknown') AS vertical,       ROUND(COALESCE(m1.total_unpaid_eur,0),2) AS total_unpaid_balance_eur,       COALESCE(m1.total_unpaid_eur,0) AS total_unpaid_balance_eur,       COALESCE(m2.open_invoice_count,0) AS open_invoice_count,       COALESCE(m3.days_overdue_oldest,0) AS days_overdue_oldest,       ROUND(COALESCE(m4.largest_unpaid_eur,0),2) AS largest_unpaid_invoice_eur,       COALESCE(m4.largest_unpaid_eur,0) AS largest_unpaid_invoice_eur,       m5.net_terms_days AS net_terms_daysFROM tenants t0INNER JOIN (    SELECT i0.tenant_id,    SELECT i0.tenant_id AS tenant_id,           SUM(i0.amount_due / CASE WHEN i0.currency_code = 'EUR' THEN 1 ELSE fx0.rate END) AS total_unpaid_eur    FROM invoices i0    LEFT JOIN (        SELECT er0.quote_code AS currency_code, er0.rate        SELECT er0.quote_code AS currency_code, er0.rate AS rate        FROM exchange_rates er0        WHERE er0.base_code = 'EUR'          AND er0.as_of = (              SELECT MAX(er1.as_of)              FROM exchange_rates er1              WHERE er1.base_code = 'EUR'                AND er1.quote_code = er0.quote_code                AND er1.as_of <= DATE '2025-12-31'          )    ) fx0 ON fx0.currency_code = i0.currency_code    WHERE i0.status IN ('open','past_due') AND i0.amount_due > 0    GROUP BY i0.tenant_id) m1 ON m1.tenant_id = t0.idLEFT JOIN (    SELECT i2.tenant_id, COUNT(*) AS open_invoice_count    SELECT i2.tenant_id AS tenant_id, COUNT(*) AS open_invoice_count    FROM invoices i2    WHERE i2.status IN ('open','past_due') AND i2.amount_due > 0    GROUP BY i2.tenant_id) m2 ON m2.tenant_id = t0.idLEFT JOIN (    SELECT i3.tenant_id,    SELECT i3.tenant_id AS tenant_id,           GREATEST(0, CAST(DATE '2026-01-05' - CAST(MIN(i3.due_at) AS DATE) AS INTEGER)) AS days_overdue_oldest    FROM invoices i3    WHERE i3.status IN ('open','past_due') AND i3.amount_due > 0    GROUP BY i3.tenant_id) m3 ON m3.tenant_id = t0.idLEFT JOIN (    SELECT i4.tenant_id,    SELECT i4.tenant_id AS tenant_id,           MAX(i4.amount_due / CASE WHEN i4.currency_code='EUR' THEN 1 ELSE fx4.rate END) AS largest_unpaid_eur    FROM invoices i4    LEFT JOIN (        SELECT er2.quote_code AS currency_code, er2.rate        SELECT er2.quote_code AS currency_code, er2.rate AS rate        FROM exchange_rates er2        WHERE er2.base_code='EUR'          AND er2.as_of = (              SELECT MAX(er3.as_of) FROM exchange_rates er3              WHERE er3.base_code='EUR' AND er3.quote_code=er2.quote_code AND er3.as_of <= DATE '2025-12-31'          )    ) fx4 ON fx4.currency_code = i4.currency_code    WHERE i4.status IN ('open','past_due') AND i4.amount_due > 0    GROUP BY i4.tenant_id) m4 ON m4.tenant_id = t0.idLEFT JOIN (    SELECT bp0.tenant_id, MAX(bp0.net_terms_days) AS net_terms_days    SELECT bp0.tenant_id AS tenant_id, MAX(bp0.net_terms_days) AS net_terms_days    FROM billing_profiles bp0    WHERE bp0.is_default = true AND bp0.deleted_at IS NULL    GROUP BY bp0.tenant_id) m5 ON m5.tenant_id = t0.idWHERE COALESCE(m1.total_unpaid_eur,0) <> 0ORDER BY total_unpaid_balance_eur DESC, days_overdue_oldest DESC, t0.id ASCLIMIT 50ORDER BY total_unpaid_balance_eur DESC NULLS FIRST, days_overdue_oldest DESC NULLS FIRST, t0.id ASC NULLS LAST{{fetch_first(50)}}

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

    SELECT t0.name AS merchant_name,
       COALESCE(t0.industry, 'Unknown') AS vertical,
       COALESCE(m1.total_unpaid_eur,0) AS total_unpaid_balance_eur,
       COALESCE(m2.open_invoice_count,0) AS open_invoice_count,
       COALESCE(m3.days_overdue_oldest,0) AS days_overdue_oldest,
       COALESCE(m4.largest_unpaid_eur,0) AS largest_unpaid_invoice_eur,
       m5.net_terms_days AS net_terms_days
FROM tenants t0
INNER JOIN (
    SELECT i0.tenant_id AS tenant_id,
           SUM(i0.amount_due / CASE WHEN i0.currency_code = 'EUR' THEN 1 ELSE fx0.rate END) AS total_unpaid_eur
    FROM invoices i0
    LEFT JOIN (
        SELECT er0.quote_code AS currency_code, er0.rate AS rate
        FROM exchange_rates er0
        WHERE er0.base_code = 'EUR'
          AND er0.as_of = (
              SELECT MAX(er1.as_of)
              FROM exchange_rates er1
              WHERE er1.base_code = 'EUR'
                AND er1.quote_code = er0.quote_code
                AND er1.as_of <= DATE '2025-12-31'
          )
    ) fx0 ON fx0.currency_code = i0.currency_code
    WHERE i0.status IN ('open','past_due') AND i0.amount_due > 0
    GROUP BY i0.tenant_id
) m1 ON m1.tenant_id = t0.id
LEFT JOIN (
    SELECT i2.tenant_id AS tenant_id, COUNT(*) AS open_invoice_count
    FROM invoices i2
    WHERE i2.status IN ('open','past_due') AND i2.amount_due > 0
    GROUP BY i2.tenant_id
) m2 ON m2.tenant_id = t0.id
LEFT JOIN (
    SELECT i3.tenant_id AS tenant_id,
           GREATEST(0, CAST(DATE '2026-01-05' - CAST(MIN(i3.due_at) AS DATE) AS INTEGER)) AS days_overdue_oldest
    FROM invoices i3
    WHERE i3.status IN ('open','past_due') AND i3.amount_due > 0
    GROUP BY i3.tenant_id
) m3 ON m3.tenant_id = t0.id
LEFT JOIN (
    SELECT i4.tenant_id AS tenant_id,
           MAX(i4.amount_due / CASE WHEN i4.currency_code='EUR' THEN 1 ELSE fx4.rate END) AS largest_unpaid_eur
    FROM invoices i4
    LEFT JOIN (
        SELECT er2.quote_code AS currency_code, er2.rate AS rate
        FROM exchange_rates er2
        WHERE er2.base_code='EUR'
          AND er2.as_of = (
              SELECT MAX(er3.as_of) FROM exchange_rates er3
              WHERE er3.base_code='EUR' AND er3.quote_code=er2.quote_code AND er3.as_of <= DATE '2025-12-31'
          )
    ) fx4 ON fx4.currency_code = i4.currency_code
    WHERE i4.status IN ('open','past_due') AND i4.amount_due > 0
    GROUP BY i4.tenant_id
) m4 ON m4.tenant_id = t0.id
LEFT JOIN (
    SELECT bp0.tenant_id AS tenant_id, MAX(bp0.net_terms_days) AS net_terms_days
    FROM billing_profiles bp0
    WHERE bp0.is_default = true AND bp0.deleted_at IS NULL
    GROUP BY bp0.tenant_id
) m5 ON m5.tenant_id = t0.id
WHERE COALESCE(m1.total_unpaid_eur,0) <> 0
ORDER BY total_unpaid_balance_eur DESC NULLS FIRST, days_overdue_oldest DESC NULLS FIRST, t0.id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY
    10 ms100 ms1 sHyper21.3 msDuckDB12.6 msGizmoSQL20.6 msclickhouse_brew178 msFirebolterrorDataFusion117 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 11, 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": "14c21c0d-f67c-42b7-9828-61f1aad0c83e", "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), if(equals(__table3.currency_code, 'EUR'_String), 1_UInt8, __table4.rate) Nullable(Decimal(18, 6)) Nullable(size = 0, Decim

    Scale factor 1000, c8a.24xlarge, after the last load. q212 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 Hannah Brooks (analyst for the Chief Executive Officer):

Title: Receivables aging: who owes us and for how long
Question: Which merchants are slowest to pay their invoices, how large and how overdue are the balances, and is this concentrated in B2B wholesale?
Grain: one row per merchant (tenant) with a nonzero unpaid invoice balance
Columns:
- merchant name
- vertical
- total unpaid balance (EUR, sum of amount_due on open/past_due invoices)
- count of open/past_due invoices
- days overdue on the oldest unpaid invoice (as of 2026-01-05, using due_at)
- largest single unpaid invoice (EUR)
- net payment terms (days, from billing_profiles.net_terms_days)
Details: Source: invoices (status in 'open','past_due', amount_due > 0) joined to tenants for vertical and billing_profiles for net_terms_days, converting amount_due to EUR using the most recent exchange_rates as_of date on or before 2025-12-31 per currency (a single snapshot rate, consistent with last night's risk-snapshot report). Days overdue = 2026-01-05 minus due_at for the invoice with the earliest due_at among that merchant's open/past_due invoices (floor to whole days, 0 if due_at is in the future or null). Include every merchant with a nonzero unpaid balance, not just B2B — I want to see whether B2B wholesalers (Industrial Supplies, Office Supplies) actually dominate the top of the list, or whether that pattern breaks down further out. Order by total unpaid balance EUR descending, tie-break by days overdue descending. Print top 50 and note the total number of merchants with a nonzero balance.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      SELECT t0.name AS merchant_name,
       COALESCE(t0.industry, 'Unknown') AS vertical,
       ROUND(COALESCE(m1.total_unpaid_eur,0),2) AS total_unpaid_balance_eur,
       COALESCE(m2.open_invoice_count,0) AS open_invoice_count,
       COALESCE(m3.days_overdue_oldest,0) AS days_overdue_oldest,
       ROUND(COALESCE(m4.largest_unpaid_eur,0),2) AS largest_unpaid_invoice_eur,
       m5.net_terms_days AS net_terms_days
FROM tenants t0
INNER JOIN (
    SELECT i0.tenant_id,
           SUM(i0.amount_due / CASE WHEN i0.currency_code = 'EUR' THEN 1 ELSE fx0.rate END) AS total_unpaid_eur
    FROM invoices i0
    LEFT JOIN (
        SELECT er0.quote_code AS currency_code, er0.rate
        FROM exchange_rates er0
        WHERE er0.base_code = 'EUR'
          AND er0.as_of = (
              SELECT MAX(er1.as_of)
              FROM exchange_rates er1
              WHERE er1.base_code = 'EUR'
                AND er1.quote_code = er0.quote_code
                AND er1.as_of <= DATE '2025-12-31'
          )
    ) fx0 ON fx0.currency_code = i0.currency_code
    WHERE i0.status IN ('open','past_due') AND i0.amount_due > 0
    GROUP BY i0.tenant_id
) m1 ON m1.tenant_id = t0.id
LEFT JOIN (
    SELECT i2.tenant_id, COUNT(*) AS open_invoice_count
    FROM invoices i2
    WHERE i2.status IN ('open','past_due') AND i2.amount_due > 0
    GROUP BY i2.tenant_id
) m2 ON m2.tenant_id = t0.id
LEFT JOIN (
    SELECT i3.tenant_id,
           GREATEST(0, CAST(DATE '2026-01-05' - CAST(MIN(i3.due_at) AS DATE) AS INTEGER)) AS days_overdue_oldest
    FROM invoices i3
    WHERE i3.status IN ('open','past_due') AND i3.amount_due > 0
    GROUP BY i3.tenant_id
) m3 ON m3.tenant_id = t0.id
LEFT JOIN (
    SELECT i4.tenant_id,
           MAX(i4.amount_due / CASE WHEN i4.currency_code='EUR' THEN 1 ELSE fx4.rate END) AS largest_unpaid_eur
    FROM invoices i4
    LEFT JOIN (
        SELECT er2.quote_code AS currency_code, er2.rate
        FROM exchange_rates er2
        WHERE er2.base_code='EUR'
          AND er2.as_of = (
              SELECT MAX(er3.as_of) FROM exchange_rates er3
              WHERE er3.base_code='EUR' AND er3.quote_code=er2.quote_code AND er3.as_of <= DATE '2025-12-31'
          )
    ) fx4 ON fx4.currency_code = i4.currency_code
    WHERE i4.status IN ('open','past_due') AND i4.amount_due > 0
    GROUP BY i4.tenant_id
) m4 ON m4.tenant_id = t0.id
LEFT JOIN (
    SELECT bp0.tenant_id, MAX(bp0.net_terms_days) AS net_terms_days
    FROM billing_profiles bp0
    WHERE bp0.is_default = true AND bp0.deleted_at IS NULL
    GROUP BY bp0.tenant_id
) m5 ON m5.tenant_id = t0.id
WHERE COALESCE(m1.total_unpaid_eur,0) <> 0
ORDER BY total_unpaid_balance_eur DESC, days_overdue_oldest DESC, t0.id ASC
LIMIT 50
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, vertical, total_unpaid_balance_eur, open_invoice_count, days_overdue_oldest, largest_unpaid_invoice_eur, net_terms_days
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      SELECT t0.name AS merchant_name,
       COALESCE(t0.industry, 'Unknown') AS vertical,
       ROUND(COALESCE(m1.total_unpaid_eur,0),2) AS total_unpaid_balance_eur,
       COALESCE(m2.open_invoice_count,0) AS open_invoice_count,
       COALESCE(m3.days_overdue_oldest,0) AS days_overdue_oldest,
       ROUND(COALESCE(m4.largest_unpaid_eur,0),2) AS largest_unpaid_invoice_eur,
       m5.net_terms_days AS net_terms_days
FROM tenants t0
INNER JOIN (
    SELECT i0.tenant_id,
           SUM(i0.amount_due / CASE WHEN i0.currency_code = 'EUR' THEN 1 ELSE fx0.rate END) AS total_unpaid_eur
    FROM invoices i0
    LEFT JOIN (
        SELECT er0.quote_code AS currency_code, er0.rate
        FROM exchange_rates er0
        WHERE er0.base_code = 'EUR'
          AND er0.as_of = (
              SELECT MAX(er1.as_of)
              FROM exchange_rates er1
              WHERE er1.base_code = 'EUR'
                AND er1.quote_code = er0.quote_code
                AND er1.as_of <= DATE '2025-12-31'
          )
    ) fx0 ON fx0.currency_code = i0.currency_code
    WHERE i0.status IN ('open','past_due') AND i0.amount_due > 0
    GROUP BY i0.tenant_id
) m1 ON m1.tenant_id = t0.id
LEFT JOIN (
    SELECT i2.tenant_id, COUNT(*) AS open_invoice_count
    FROM invoices i2
    WHERE i2.status IN ('open','past_due') AND i2.amount_due > 0
    GROUP BY i2.tenant_id
) m2 ON m2.tenant_id = t0.id
LEFT JOIN (
    SELECT i3.tenant_id,
           GREATEST(0, CAST(DATE '2026-01-05' - CAST(MIN(i3.due_at) AS DATE) AS INTEGER)) AS days_overdue_oldest
    FROM invoices i3
    WHERE i3.status IN ('open','past_due') AND i3.amount_due > 0
    GROUP BY i3.tenant_id
) m3 ON m3.tenant_id = t0.id
LEFT JOIN (
    SELECT i4.tenant_id,
           MAX(i4.amount_due / CASE WHEN i4.currency_code='EUR' THEN 1 ELSE fx4.rate END) AS largest_unpaid_eur
    FROM invoices i4
    LEFT JOIN (
        SELECT er2.quote_code AS currency_code, er2.rate
        FROM exchange_rates er2
        WHERE er2.base_code='EUR'
          AND er2.as_of = (
              SELECT MAX(er3.as_of) FROM exchange_rates er3
              WHERE er3.base_code='EUR' AND er3.quote_code=er2.quote_code AND er3.as_of <= DATE '2025-12-31'
          )
    ) fx4 ON fx4.currency_code = i4.currency_code
    WHERE i4.status IN ('open','past_due') AND i4.amount_due > 0
    GROUP BY i4.tenant_id
) m4 ON m4.tenant_id = t0.id
LEFT JOIN (
    SELECT bp0.tenant_id, MAX(bp0.net_terms_days) AS net_terms_days
    FROM billing_profiles bp0
    WHERE bp0.is_default = true AND bp0.deleted_at IS NULL
    GROUP BY bp0.tenant_id
) m5 ON m5.tenant_id = t0.id
WHERE COALESCE(m1.total_unpaid_eur,0) <> 0
ORDER BY total_unpaid_balance_eur DESC, days_overdue_oldest DESC, t0.id ASC
LIMIT 50
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of what the query does: - **Grain:** one row per tenant with a nonzero unpaid invoice balance. - **Filter:** `invoices` with `status IN ('open','past_due')` and `amount_due > 0`. - **FX:** converts each invoice's `amount_due` to EUR using the most recent `exchange_rates` row (`base_code = 'EUR'`) with `as_of <= 2025-12-31` for that currency — a single snapshot rate per currency, as requested. - **Measures** (each built as its own subquery joined back on `tenant_id`, per house style): total unpaid EUR, count of open/past-due invoices, days overdue on the oldest unpaid invoice (floored, clamped to 0), largest single unpaid invoice in EUR, and net payment terms from the tenant's default `billing_profiles` row. - **Ordering:** total unpaid balance EUR descending, then days overdue descending, with tenant id as a final deterministic tie-break. - **Limit:** top 50 rows, as specified; the batch report will show the full matching row count. Noted in the submission: the net-terms join assumes one default billing profile per tenant (took the max if duplicates existed), and the FX conversion is a single year-end snapshot rate rather than a per-invoice historical rate, consistent with the prior risk-snapshot report.

    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 (285 KB) · Hannah Brooks's transcript (872 KB) · Maren Okafor's transcript (278 KB) · the night's plan (6 KB) · the night's reports (22 KB) · the week's memo (3 KB)