Queries · q097 · Head of Data · week 1, night 4

B2B wholesaler receivables aging

“For our B2B wholesaler merchants, how much of their outstanding receivables is genuinely overdue versus just within normal net terms lag, so we can present that line accurately in the annual accounts?”

Manager · asked the questionRosa Van den BergHead of Data. Took over the analytics team last week. Before the team's numbers go to the board she wants to know how far they can be trusted.

Analyst · planned the reportYuki TanakaDetail-oriented: picks specific merchants, products or days and looks at them closely.

Engineer · wrote the SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

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
Rosa Van den Berg writes to Yuki Tanaka
The manager, at the start of week 1
Subject: Certifying the key metrics before the board pack — what I need from you this week

Hi Yuki,

Tobias and I have to certify revenue, orders, customers, refunds and payouts before they go into the board pack. "Certify" means we're telling the board these numbers are reconcilable — so this week's job is to find out where the different places we record these figures agree, where they don't, and whether any gap is big enough to change the story we tell. If something doesn't reconcile, I need to know why and by how much, not just that it's "close enough." That feeds directly into the go/no-go Tobias and I give on the board pack, so please prioritize breadth across the five metrics over depth on any one of them — we can always dig deeper next week on whichever one looks shakiest.

You only have a handful of report slots a night, so here's what matters most to me, roughly in priority order:
1. If someone asks "what was our revenue last month," do we get the same answer regardless of which part of the business we ask? Where it diverges, how far apart are the answers, and is the gap explained by something sensible (timing, currency, returns) or does it look like a real inconsistency?
2. Do our order counts and our revenue tell a consistent story — i.e., is there a clean, traceable link from an order to the money that was actually collected for it, or are there orders that seem to vanish or multiply along the way?
3. Refunds and payouts: when money goes back to a customer, does it show up consistently as a reduction in revenue and in what we actually pay out to merchants? I want to know if refunds could be hiding or double-counted anywhere.
4. Customers: can we count "how many customers we have" or "how many are active" in one consistent way, or do different parts of the data give meaningfully different headcounts?
5. If you have a slot left: is there anything about how timestamps or currencies are handled that could quietly distort any of the above when we roll numbers up across time or across merchants?
Please flag anything you find inconsistent together with your best read of why, and roughly how big the discrepancy is in practice — that's what Tobias and I need to decide whether we can certify as-is, certify with a caveat, or need to hold a number back.

Thank you — I know this is a lot for one week. Let's talk before you spend your first slot tonight if you want to sanity-check the plan.

Rosa
A note from the Chief Financial Officer, Tobias Lindqvist
Subject: What I need for the revenue/payout certification

Hi Yuki,

A few things from my side as you scope this week's work:
1. Does our reported revenue tie out end to end? I want to know whether what we book as platform fees and processing fees matches what actually settles to us through payouts — and if there's a gap, roughly how large it is and whether it's timing (month-end cutoff) or something structural.
2. Do merchant payouts reconcile to the gross volume and refunds we report? I'm particularly interested in whether net payouts consistently equal gross sales minus refunds minus our fees, or whether certain merchants/currencies show persistent drift.
3. Are our B2B wholesalers' outstanding receivables consistent with what we'd show in the annual accounts? I want to understand how much is genuinely overdue versus just lagging normal payment terms, since that affects how we present them.
If anything else jumps out while you're in there — especially around currency conversion timing or refund treatment — flag it even if it's outside these three.

Thanks, Tobias
Yuki Tanaka plans the night
The analyst · week 1, night 4, request 1 of 3
Night 3 closed out two big open questions. The refund-inclusive order-status report shows the original 22% fulfilled gap was almost entirely a status-filter artifact: captured payments that later moved to refunded or partially_refunded status were invisible to a captured-only filter. Once included, the real residual gap is 0.9% for fulfilled orders and 5.3% for refunded orders, and the cancelled bucket is fully explained by orders cancelled before any charge plus clean full-refund treatment (net collected is exactly 0) for the ones that were charged. That is good news: orders trace cleanly to money collected once refund statuses are included. The refund-timing report found something genuinely structural: 85.5% of refund dollars are processed after the original payment's payout already settled, 20.9 days later on average, and payout_items has a one-row-per-payment constraint, so there is no clean slot in any payout to tie that later clawback back to the original sale. That explains the earlier volatility and is a real traceability gap to flag to Tobias rather than something I can fully resolve with one more query. The customer-headcount report shows a huge range from 11,258 total accounts down to 411 with an active subscription, with status equals active nearly meaningless as an activity signal since it covers 99.3% of all accounts. Tonight I am closing out the two asks not yet touched, Tobias's B2B receivables question and Rosa's currency and timestamp distortion question, plus one more targeted test of whether gross minus fee equals net holds consistently across currencies, tested in native currency to avoid any FX conversion noise contaminating that specific check.
B2B wholesaler receivables aging
Question
For our B2B wholesaler merchants, how much of their outstanding receivables is genuinely overdue versus just within normal net terms lag, so we can present that line accurately in the annual accounts?
One row per
receivables aging bucket
Columns
- aging_bucket
- invoice_count
- amount_due_eur
- pct_of_total_amount_due
- avg_days_past_due
- avg_net_terms_days

Omar 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 scoped_tenants AS (
  SELECT id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office%'
     OR industry ILIKE '%wholesale%'
     OR industry ILIKE '%b2b%'
),
tenant_match_count AS (
  SELECT COUNT(*) AS cnt FROM scoped_tenants
),
base AS (
  SELECT
    i.id,
    i.amount_due,
    i.currency_code,
    i.due_at,
    bp.net_terms_days,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 'Not yet due'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
      ELSE '90+ days overdue'
    END AS aging_bucket,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 0
      ELSE (DATE '2025-12-31' - i.due_at::date)
    END AS days_past_due,
    er.rate AS eur_rate
  FROM invoices i
  JOIN scoped_tenants st ON st.id = i.tenant_id
  LEFT JOIN billing_profiles bp ON bp.id = i.billing_profile_id
  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.status IN ('open', 'past_due')
    AND i.amount_due > 0
),
bucketed AS (
  SELECT
    aging_bucket,
    COUNT(*) AS invoice_count,
    SUM(amount_due / eur_rate) AS amount_due_eur,
    AVG(days_past_due) AS avg_days_past_due,
    AVG(net_terms_days) AS avg_net_terms_days
  FROM base
  GROUP BY aging_bucket
),
totals AS (
  SELECT SUM(amount_due_eur) AS total_amount_due_eur FROM bucketed
)
SELECT
  b.aging_bucket,
  b.invoice_count,
  ROUND(b.amount_due_eur, 2) AS amount_due_eur,
  ROUND(b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100, 1) AS pct_of_total_amount_due,
  ROUND(b.avg_days_past_due, 1) AS avg_days_past_due,
  ROUND(b.avg_net_terms_days, 1) AS avg_net_terms_days
FROM bucketed b
CROSS JOIN totals t
WHERE (SELECT cnt FROM tenant_match_count) > 0

UNION ALL

SELECT
  di.industry AS aging_bucket,
  NULL::bigint AS invoice_count,
  NULL::numeric AS amount_due_eur,
  NULL::numeric AS pct_of_total_amount_due,
  NULL::numeric AS avg_days_past_due,
  NULL::numeric AS avg_net_terms_days
FROM (SELECT DISTINCT industry FROM tenants) di
WHERE (SELECT cnt FROM tenant_match_count) = 0

ORDER BY amount_due_eur DESC NULLS LAST, aging_bucket

“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: - Scope: tenants whose industry matches %industrial%, %office%, %wholesale%, or %b2b% (case-insensitive). - Population: their invoices with status IN ('open','past_due') and amount_due > 0 as of 2025-12-31. - Buckets: Not yet due, 1-30, 31-60, 61-90, 90+ days overdue, based on 2025-12-31 - due_at. - Measures per bucket: invoice count, amount due converted to EUR (via exchange_rates, dividing by the EUR→currency rate), share of total, average days past due, and average billing_profiles.net_terms_days for context on normal terms. - Fallback: if the industry filter matches no tenants at all, the same query instead returns the distinct tenants.industry values (other columns null) so the filter can be corrected without spending another report slot. - Ordered by amount_due_eur descending, with aging_bucket as a deterministic tie-break.”

The report comes back overnight

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

aging_bucket       | invoice_count | amount_due_eur | pct_of_total_amount_due | avg_days_past_due | avg_net_terms_days
-------------------+---------------+----------------+-------------------------+-------------------+-------------------
Not yet due        |            26 |       37770.52 |                    90.8 |               0.0 |               39.2
1-30 days overdue  |             3 |        3450.24 |                     8.3 |              10.7 |               19.3
31-60 days overdue |             1 |         358.26 |                     0.9 |              33.0 |               14.0
(3 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Yuki Tanaka's memo to Rosa Van den Berg at the end of the week:
- Tobias's B2B receivables: (B2B wholesaler receivables aging) Only 30 open invoices matched, €41,579 due, 90.8% not yet due, 9.2% overdue (max bucket 31-60 days, none beyond). Low confidence — 30 invoices feels thin for our wholesaler count; the industry-text filter may be missing tenants. Don't certify this line yet.
Later queries that build on this report:
- q302B2B wholesale tenants identified by CRM deal activity, with revenue and receivables
- q310Final B2B wholesale receivables aging (corrected)

It becomes benchmark query q097

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

spellingcast (9×)
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
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

The benchmark text, against the submitted SQL

WITH scoped_tenants AS (  SELECT id  FROM tenants  WHERE industry ILIKE '%industrial%'     OR industry ILIKE '%office%'     OR industry ILIKE '%wholesale%'     OR industry ILIKE '%b2b%'),tenant_match_count AS (  SELECT COUNT(*) AS cnt FROM scoped_tenants),base AS (  SELECT    i.id,    i.amount_due,    i.currency_code,    i.due_at,    bp.net_terms_days,    i.id AS id,    i.amount_due AS amount_due,    i.currency_code AS currency_code,    i.due_at AS due_at,    bp.net_terms_days AS net_terms_days,    CASE      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 'Not yet due'      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 1 AND 30 THEN '1-30 days overdue'      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 31 AND 60 THEN '31-60 days overdue'      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 61 AND 90 THEN '61-90 days overdue'      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 1 AND 30 THEN '1-30 days overdue'      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 31 AND 60 THEN '31-60 days overdue'      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 61 AND 90 THEN '61-90 days overdue'      ELSE '90+ days overdue'    END AS aging_bucket,    CASE      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 0      ELSE (DATE '2025-12-31' - i.due_at::date)      ELSE (DATE '2025-12-31' - CAST(i.due_at AS date))    END AS days_past_due,    er.rate AS eur_rate  FROM invoices i  JOIN scoped_tenants st ON st.id = i.tenant_id  LEFT JOIN billing_profiles bp ON bp.id = i.billing_profile_id  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.status IN ('open', 'past_due')    AND i.amount_due > 0),bucketed AS (  SELECT    aging_bucket,    COUNT(*) AS invoice_count,    SUM(amount_due / eur_rate) AS amount_due_eur,    AVG(days_past_due) AS avg_days_past_due,    AVG(net_terms_days) AS avg_net_terms_days    SUM(CAST(amount_due / eur_rate AS NUMERIC(18,6))) AS amount_due_eur,    AVG(CAST(days_past_due AS NUMERIC(18,6))) AS avg_days_past_due,    AVG(CAST(net_terms_days AS NUMERIC(18,6))) AS avg_net_terms_days  FROM base  GROUP BY aging_bucket),totals AS (  SELECT SUM(amount_due_eur) AS total_amount_due_eur FROM bucketed)SELECT  b.aging_bucket,  b.invoice_count,  ROUND(b.amount_due_eur, 2) AS amount_due_eur,  ROUND(b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100, 1) AS pct_of_total_amount_due,  ROUND(b.avg_days_past_due, 1) AS avg_days_past_due,  ROUND(b.avg_net_terms_days, 1) AS avg_net_terms_days  b.aging_bucket AS aging_bucket,  b.invoice_count AS invoice_count,  b.amount_due_eur AS amount_due_eur,  b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100 AS pct_of_total_amount_due,  b.avg_days_past_due AS avg_days_past_due,  b.avg_net_terms_days AS avg_net_terms_daysFROM bucketed bCROSS JOIN totals tWHERE (SELECT cnt FROM tenant_match_count) > 0UNION ALLSELECT  di.industry AS aging_bucket,  NULL::bigint AS invoice_count,  NULL::numeric AS amount_due_eur,  NULL::numeric AS pct_of_total_amount_due,  NULL::numeric AS avg_days_past_due,  NULL::numeric AS avg_net_terms_days  CAST(NULL AS bigint) AS invoice_count,  CAST(NULL AS numeric(18,6)) AS amount_due_eur,  CAST(NULL AS numeric(18,6)) AS pct_of_total_amount_due,  CAST(NULL AS numeric(18,6)) AS avg_days_past_due,  CAST(NULL AS numeric(18,6)) AS avg_net_terms_daysFROM (SELECT DISTINCT industry FROM tenants) diWHERE (SELECT cnt FROM tenant_match_count) = 0ORDER BY amount_due_eur DESC NULLS LAST, aging_bucketORDER BY amount_due_eur DESC NULLS LAST, aging_bucket NULLS LAST

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

WITH scoped_tenants AS (
  SELECT id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office%'
     OR industry ILIKE '%wholesale%'
     OR industry ILIKE '%b2b%'
),
tenant_match_count AS (
  SELECT COUNT(*) AS cnt FROM scoped_tenants
),
base AS (
  SELECT
    i.id AS id,
    i.amount_due AS amount_due,
    i.currency_code AS currency_code,
    i.due_at AS due_at,
    bp.net_terms_days AS net_terms_days,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 'Not yet due'
      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 1 AND 30 THEN '1-30 days overdue'
      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 31 AND 60 THEN '31-60 days overdue'
      WHEN (DATE '2025-12-31' - CAST(i.due_at AS date)) BETWEEN 61 AND 90 THEN '61-90 days overdue'
      ELSE '90+ days overdue'
    END AS aging_bucket,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 0
      ELSE (DATE '2025-12-31' - CAST(i.due_at AS date))
    END AS days_past_due,
    er.rate AS eur_rate
  FROM invoices i
  JOIN scoped_tenants st ON st.id = i.tenant_id
  LEFT JOIN billing_profiles bp ON bp.id = i.billing_profile_id
  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.status IN ('open', 'past_due')
    AND i.amount_due > 0
),
bucketed AS (
  SELECT
    aging_bucket,
    COUNT(*) AS invoice_count,
    SUM(CAST(amount_due / eur_rate AS NUMERIC(18,6))) AS amount_due_eur,
    AVG(CAST(days_past_due AS NUMERIC(18,6))) AS avg_days_past_due,
    AVG(CAST(net_terms_days AS NUMERIC(18,6))) AS avg_net_terms_days
  FROM base
  GROUP BY aging_bucket
),
totals AS (
  SELECT SUM(amount_due_eur) AS total_amount_due_eur FROM bucketed
)
SELECT
  b.aging_bucket AS aging_bucket,
  b.invoice_count AS invoice_count,
  b.amount_due_eur AS amount_due_eur,
  b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100 AS pct_of_total_amount_due,
  b.avg_days_past_due AS avg_days_past_due,
  b.avg_net_terms_days AS avg_net_terms_days
FROM bucketed b
CROSS JOIN totals t
WHERE (SELECT cnt FROM tenant_match_count) > 0

UNION ALL

SELECT
  di.industry AS aging_bucket,
  CAST(NULL AS bigint) AS invoice_count,
  CAST(NULL AS numeric(18,6)) AS amount_due_eur,
  CAST(NULL AS numeric(18,6)) AS pct_of_total_amount_due,
  CAST(NULL AS numeric(18,6)) AS avg_days_past_due,
  CAST(NULL AS numeric(18,6)) AS avg_net_terms_days
FROM (SELECT DISTINCT industry FROM tenants) di
WHERE (SELECT cnt FROM tenant_match_count) = 0

ORDER BY amount_due_eur DESC NULLS LAST, aging_bucket NULLS LAST

Firebolt{ "errors": [ { "description": "Line 45, Column 25: 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": "a75930c3-05ae-4579-81dc-8ab9a9f303f6", "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), __table3.eur_rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

Scale factor 1000, c8a.24xlarge, after the last load. q097 on the start page, with every configuration.

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

Omar's whole session, 7 turns

The request, as Omar received it

Report request from Yuki Tanaka (analyst for the Head of Data):

Title: B2B wholesaler receivables aging
Question: For our B2B wholesaler merchants, how much of their outstanding receivables is genuinely overdue versus just within normal net terms lag, so we can present that line accurately in the annual accounts?
Grain: receivables aging bucket
Columns:
- aging_bucket
- invoice_count
- amount_due_eur
- pct_of_total_amount_due
- avg_days_past_due
- avg_net_terms_days
Details: Scope: tenants whose industry indicates a B2B wholesale vertical such as industrial supplies or office equipment, matched via tenants.industry ILIKE patterns for industrial, office, wholesale, or B2B; if none match, instead return the distinct tenants.industry values present so the right filter can be identified. Population: invoices for those tenants with status in open or past_due and amount_due greater than 0, as of the snapshot date 2025-12-31. Bucket each invoice by days overdue, computed as 2025-12-31 minus invoices.due_at: Not yet due when due_at is null or due_at is on or after 2025-12-31, then 1 to 30 days overdue, 31 to 60 days overdue, 61 to 90 days overdue, and 90 plus days overdue. invoice_count is the count of invoices in the bucket. amount_due_eur is the sum of invoices.amount_due converted to EUR via exchange_rates using base_code EUR, quote_code equal to the invoice currency_code, and as_of 2025-12-31. pct_of_total_amount_due is amount_due_eur divided by the total amount_due_eur across all buckets, times 100, rounded to 1 decimal. avg_days_past_due is the average of 2025-12-31 minus due_at in days for the bucket, rounded to 1 decimal, with 0 for Not yet due. avg_net_terms_days is the average of billing_profiles.net_terms_days for the billing profile tied to each invoice, rounded to 1 decimal, for context on what normal lag looks like. Tables: tenants, invoices, billing_profiles, exchange_rates. Order by amount_due_eur descending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Omar calls dry_run

WITH scoped_tenants AS (
  SELECT id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office%'
     OR industry ILIKE '%wholesale%'
     OR industry ILIKE '%b2b%'
),
tenant_match_count AS (
  SELECT COUNT(*) AS cnt FROM scoped_tenants
),
base AS (
  SELECT
    i.id,
    i.amount_due,
    i.currency_code,
    i.due_at,
    bp.net_terms_days,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 'Not yet due'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
      ELSE '90+ days overdue'
    END AS aging_bucket,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 0
      ELSE (DATE '2025-12-31' - i.due_at::date)
    END AS days_past_due,
    er.rate AS eur_rate
  FROM invoices i
  JOIN scoped_tenants st ON st.id = i.tenant_id
  LEFT JOIN billing_profiles bp ON bp.id = i.billing_profile_id
  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.status IN ('open', 'past_due')
    AND i.amount_due > 0
),
bucketed AS (
  SELECT
    aging_bucket,
    COUNT(*) AS invoice_count,
    SUM(amount_due / eur_rate) AS amount_due_eur,
    AVG(days_past_due) AS avg_days_past_due,
    AVG(net_terms_days) AS avg_net_terms_days
  FROM base
  GROUP BY aging_bucket
),
totals AS (
  SELECT SUM(amount_due_eur) AS total_amount_due_eur FROM bucketed
)
SELECT
  b.aging_bucket,
  b.invoice_count,
  ROUND(b.amount_due_eur, 2) AS amount_due_eur,
  ROUND(b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100, 1) AS pct_of_total_amount_due,
  ROUND(b.avg_days_past_due, 1) AS avg_days_past_due,
  ROUND(b.avg_net_terms_days, 1) AS avg_net_terms_days
FROM bucketed b
CROSS JOIN totals t
WHERE (SELECT cnt FROM tenant_match_count) > 0

UNION ALL

SELECT
  di.industry AS aging_bucket,
  NULL::bigint AS invoice_count,
  NULL::numeric AS amount_due_eur,
  NULL::numeric AS pct_of_total_amount_due,
  NULL::numeric AS avg_days_past_due,
  NULL::numeric AS avg_net_terms_days
FROM (SELECT DISTINCT industry FROM tenants) di
WHERE (SELECT cnt FROM tenant_match_count) = 0

ORDER BY amount_due_eur DESC NULLS LAST, aging_bucket

The warehouse answers

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

OmarQuery compiles and returns the expected columns. Submitting.

Omar calls submit

WITH scoped_tenants AS (
  SELECT id
  FROM tenants
  WHERE industry ILIKE '%industrial%'
     OR industry ILIKE '%office%'
     OR industry ILIKE '%wholesale%'
     OR industry ILIKE '%b2b%'
),
tenant_match_count AS (
  SELECT COUNT(*) AS cnt FROM scoped_tenants
),
base AS (
  SELECT
    i.id,
    i.amount_due,
    i.currency_code,
    i.due_at,
    bp.net_terms_days,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 'Not yet due'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 1 AND 30 THEN '1-30 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 31 AND 60 THEN '31-60 days overdue'
      WHEN (DATE '2025-12-31' - i.due_at::date) BETWEEN 61 AND 90 THEN '61-90 days overdue'
      ELSE '90+ days overdue'
    END AS aging_bucket,
    CASE
      WHEN i.due_at IS NULL OR i.due_at >= TIMESTAMP '2025-12-31' THEN 0
      ELSE (DATE '2025-12-31' - i.due_at::date)
    END AS days_past_due,
    er.rate AS eur_rate
  FROM invoices i
  JOIN scoped_tenants st ON st.id = i.tenant_id
  LEFT JOIN billing_profiles bp ON bp.id = i.billing_profile_id
  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.status IN ('open', 'past_due')
    AND i.amount_due > 0
),
bucketed AS (
  SELECT
    aging_bucket,
    COUNT(*) AS invoice_count,
    SUM(amount_due / eur_rate) AS amount_due_eur,
    AVG(days_past_due) AS avg_days_past_due,
    AVG(net_terms_days) AS avg_net_terms_days
  FROM base
  GROUP BY aging_bucket
),
totals AS (
  SELECT SUM(amount_due_eur) AS total_amount_due_eur FROM bucketed
)
SELECT
  b.aging_bucket,
  b.invoice_count,
  ROUND(b.amount_due_eur, 2) AS amount_due_eur,
  ROUND(b.amount_due_eur / NULLIF(t.total_amount_due_eur, 0) * 100, 1) AS pct_of_total_amount_due,
  ROUND(b.avg_days_past_due, 1) AS avg_days_past_due,
  ROUND(b.avg_net_terms_days, 1) AS avg_net_terms_days
FROM bucketed b
CROSS JOIN totals t
WHERE (SELECT cnt FROM tenant_match_count) > 0

UNION ALL

SELECT
  di.industry AS aging_bucket,
  NULL::bigint AS invoice_count,
  NULL::numeric AS amount_due_eur,
  NULL::numeric AS pct_of_total_amount_due,
  NULL::numeric AS avg_days_past_due,
  NULL::numeric AS avg_net_terms_days
FROM (SELECT DISTINCT industry FROM tenants) di
WHERE (SELECT cnt FROM tenant_match_count) = 0

ORDER BY amount_due_eur DESC NULLS LAST, aging_bucket

The warehouse answers

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

OmarSubmitted. Summary of what the query does: - **Scope**: tenants whose `industry` matches `%industrial%`, `%office%`, `%wholesale%`, or `%b2b%` (case-insensitive). - **Population**: their invoices with `status IN ('open','past_due')` and `amount_due > 0` as of 2025-12-31. - **Buckets**: Not yet due, 1-30, 31-60, 61-90, 90+ days overdue, based on `2025-12-31 - due_at`. - **Measures per bucket**: invoice count, amount due converted to EUR (via `exchange_rates`, dividing by the EUR→currency rate), share of total, average days past due, and average `billing_profiles.net_terms_days` for context on normal terms. - **Fallback**: if the industry filter matches no tenants at all, the same query instead returns the distinct `tenants.industry` values (other columns null) so the filter can be corrected without spending another report slot. - Ordered by `amount_due_eur` descending, with `aging_bucket` as a deterministic tie-break.

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

Omar's transcript (277 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 (4 KB) · the week's memo (3 KB)

B2B wholesaler receivables aging

A business event lands on the Head of Data's desk

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q097

Everything, as recorded