Queries · q299 · Head of Data · week 2, night 1

Order-to-payment match rate by order age

“At the portfolio level, how much of the gap between order value and money collected is just timing (recent orders not yet settled) versus a permanent shortfall that age doesn't fix?”

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 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

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 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
Yuki Tanaka plans the night
The analyst · week 2, night 1, request 1 of 3
Rosa's three questions this week are each really "is this timing, is this concentrated, and is this already solved elsewhere" - all testable with the right grain. (1) For the portfolio-level revenue gap, the order-level work showed <1-5% residual gap once refunded/partially_refunded payments are included, but that used orders aged 6+ months - I never actually tested whether that residual shrinks with age (timing) or is flat from day one (structural). An age-bucket grain since placed_at will show the ramp-up curve directly: if match rate keeps climbing past 90-180 days it's timing; if it plateaus early at e.g. 95-98% and stays there, that plateau is the real structural ceiling. (2) For refund/payout traceability, Rosa wants concentration AND trend together - I can get both from one grain (tenant) by comparing H1 vs H2 2025 late-refund euros per tenant, which also directly tests whether the problem is spreading or shrinking. (3) For the B2B sizing question, rather than rebuilding my fuzzy industry-text merchant list, I'm pulling revenue by industry vertical directly - this settles both "how big is B2B wholesale" and incidentally double-checks my prior ad hoc filter against the real industry categories. I'm holding the customer-definition-usage question (which other systems already use "ever purchased" vs "last 12 months") for tomorrow night, since it likely means digging into segments/cohorts/CRM definitions rather than a straightforward aggregate query, and I'd rather scope that precisely after seeing tonight's results.
Order-to-payment match rate by order age
Question
At the portfolio level, how much of the gap between order value and money collected is just timing (recent orders not yet settled) versus a permanent shortfall that age doesn't fix?
One row per
order age bucket, measured as (2025-12-31 minus orders.placed_at) in days
Columns
- age_bucket
- order_count
- orders_grand_total_eur
- matched_money_collected_eur
- match_rate_pct

Bea writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

WITH order_base AS (
  SELECT
    o.id AS order_id,
    o.grand_total,
    o.currency_code,
    o.placed_at,
    (DATE '2025-12-31' - CAST(o.placed_at AS date)) AS age_days,
    CASE
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 3 THEN '0-3 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 7 THEN '4-7 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 14 THEN '8-14 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 30 THEN '15-30 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 60 THEN '31-60 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 90 THEN '61-90 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 180 THEN '91-180 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS age_bucket
  FROM orders o
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
order_count_agg AS (
  SELECT t0.age_bucket, COUNT(*) AS order_count
  FROM order_base t0
  GROUP BY t0.age_bucket
),
grand_total_agg AS (
  SELECT t1.age_bucket, SUM(t1.grand_total / t2.rate) AS orders_grand_total_eur
  FROM order_base t1
  JOIN exchange_rates t2
    ON t2.base_code = 'EUR'
    AND t2.quote_code = t1.currency_code
    AND t2.as_of = CAST(t1.placed_at AS date)
  GROUP BY t1.age_bucket
),
payment_agg AS (
  SELECT t3.age_bucket, SUM(t4.amount / t5.rate) AS matched_money_collected_eur
  FROM order_base t3
  JOIN payments t4
    ON t4.order_id = t3.order_id
    AND t4.status IN ('captured', 'refunded', 'partially_refunded')
  JOIN exchange_rates t5
    ON t5.base_code = 'EUR'
    AND t5.quote_code = t4.currency_code
    AND t5.as_of = CAST(t4.captured_at AS date)
  GROUP BY t3.age_bucket
)
SELECT
  t6.age_bucket,
  t6.order_count,
  ROUND(COALESCE(t7.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0), 2) AS matched_money_collected_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100, 1) AS match_rate_pct
FROM order_count_agg t6
LEFT JOIN grand_total_agg t7 ON t7.age_bucket = t6.age_bucket
LEFT JOIN payment_agg t8 ON t8.age_bucket = t6.age_bucket
ORDER BY
  CASE t6.age_bucket
    WHEN '0-3 days' THEN 1
    WHEN '4-7 days' THEN 2
    WHEN '8-14 days' THEN 3
    WHEN '15-30 days' THEN 4
    WHEN '31-60 days' THEN 5
    WHEN '61-90 days' THEN 6
    WHEN '91-180 days' THEN 7
    WHEN '181-365 days' THEN 8
    ELSE 9
  END

“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. The query buckets non-cancelled orders placed between 2023-01-01 and 2025-12-31 by age as of 2025-12-31, converts both order grand totals and matched payment amounts (captured/refunded/partially_refunded) to EUR using the same-day exchange rate, and reports count, both EUR totals, and the match rate per bucket — ordered youngest to oldest as requested.”

The report comes back overnight

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

age_bucket   | order_count | orders_grand_total_eur | matched_money_collected_eur | match_rate_pct
-------------+-------------+------------------------+-----------------------------+---------------
0-3 days     |         348 |               34188.23 |                    34188.23 |          100.0
4-7 days     |         298 |               23198.54 |                    23198.54 |          100.0
8-14 days    |         594 |               58990.07 |                    49213.08 |           83.4
15-30 days   |        1754 |              216605.73 |                   195981.86 |           90.5
31-60 days   |        2550 |               328682.7 |                    308288.1 |           93.8
61-90 days   |        1777 |              228982.28 |                   218550.72 |           95.4
91-180 days  |        4645 |              524129.52 |                   524439.53 |          100.1
181-365 days |        9556 |             1299647.47 |                  1293594.88 |           99.5
366+ days    |       27805 |             3711376.17 |                  3669424.92 |           98.9
(9 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q304Payment capture lag by payment method
It builds on:
- q094Order-to-payment gap re-measured with refund-inclusive payment status

It becomes benchmark query q299

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

numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
made explicita selected t.col gets AS col, its standard name made explicit (8×)
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 order_base AS (  SELECT    o.id AS order_id,    o.grand_total,    o.currency_code,    o.placed_at,    o.grand_total AS grand_total,    o.currency_code AS currency_code,    o.placed_at AS placed_at,    (DATE '2025-12-31' - CAST(o.placed_at AS date)) AS age_days,    CASE      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 3 THEN '0-3 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 7 THEN '4-7 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 14 THEN '8-14 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 30 THEN '15-30 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 60 THEN '31-60 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 90 THEN '61-90 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 180 THEN '91-180 days'      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 365 THEN '181-365 days'      ELSE '366+ days'    END AS age_bucket  FROM orders o  WHERE o.status <> 'cancelled'    AND o.placed_at >= TIMESTAMP '2023-01-01'    AND o.placed_at < TIMESTAMP '2026-01-01'),order_count_agg AS (  SELECT t0.age_bucket, COUNT(*) AS order_count  SELECT t0.age_bucket AS age_bucket, COUNT(*) AS order_count  FROM order_base t0  GROUP BY t0.age_bucket),grand_total_agg AS (  SELECT t1.age_bucket, SUM(t1.grand_total / t2.rate) AS orders_grand_total_eur  SELECT t1.age_bucket AS age_bucket, SUM(CAST(t1.grand_total / t2.rate AS NUMERIC(18,6))) AS orders_grand_total_eur  FROM order_base t1  JOIN exchange_rates t2    ON t2.base_code = 'EUR'    AND t2.quote_code = t1.currency_code    AND t2.as_of = CAST(t1.placed_at AS date)  GROUP BY t1.age_bucket),payment_agg AS (  SELECT t3.age_bucket, SUM(t4.amount / t5.rate) AS matched_money_collected_eur  SELECT t3.age_bucket AS age_bucket, SUM(CAST(t4.amount / t5.rate AS NUMERIC(18,6))) AS matched_money_collected_eur  FROM order_base t3  JOIN payments t4    ON t4.order_id = t3.order_id    AND t4.status IN ('captured', 'refunded', 'partially_refunded')  JOIN exchange_rates t5    ON t5.base_code = 'EUR'    AND t5.quote_code = t4.currency_code    AND t5.as_of = CAST(t4.captured_at AS date)  GROUP BY t3.age_bucket)SELECT  t6.age_bucket,  t6.order_count,  ROUND(COALESCE(t7.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,  ROUND(COALESCE(t8.matched_money_collected_eur, 0), 2) AS matched_money_collected_eur,  ROUND(COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100, 1) AS match_rate_pct  t6.age_bucket AS age_bucket,  t6.order_count AS order_count,  COALESCE(t7.orders_grand_total_eur, 0) AS orders_grand_total_eur,  COALESCE(t8.matched_money_collected_eur, 0) AS matched_money_collected_eur,  COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100 AS match_rate_pctFROM order_count_agg t6LEFT JOIN grand_total_agg t7 ON t7.age_bucket = t6.age_bucketLEFT JOIN payment_agg t8 ON t8.age_bucket = t6.age_bucketORDER BY  CASE t6.age_bucket    WHEN '0-3 days' THEN 1    WHEN '4-7 days' THEN 2    WHEN '8-14 days' THEN 3    WHEN '15-30 days' THEN 4    WHEN '31-60 days' THEN 5    WHEN '61-90 days' THEN 6    WHEN '91-180 days' THEN 7    WHEN '181-365 days' THEN 8    ELSE 9  END  END NULLS LAST

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

WITH order_base AS (
  SELECT
    o.id AS order_id,
    o.grand_total AS grand_total,
    o.currency_code AS currency_code,
    o.placed_at AS placed_at,
    (DATE '2025-12-31' - CAST(o.placed_at AS date)) AS age_days,
    CASE
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 3 THEN '0-3 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 7 THEN '4-7 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 14 THEN '8-14 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 30 THEN '15-30 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 60 THEN '31-60 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 90 THEN '61-90 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 180 THEN '91-180 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS age_bucket
  FROM orders o
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
order_count_agg AS (
  SELECT t0.age_bucket AS age_bucket, COUNT(*) AS order_count
  FROM order_base t0
  GROUP BY t0.age_bucket
),
grand_total_agg AS (
  SELECT t1.age_bucket AS age_bucket, SUM(CAST(t1.grand_total / t2.rate AS NUMERIC(18,6))) AS orders_grand_total_eur
  FROM order_base t1
  JOIN exchange_rates t2
    ON t2.base_code = 'EUR'
    AND t2.quote_code = t1.currency_code
    AND t2.as_of = CAST(t1.placed_at AS date)
  GROUP BY t1.age_bucket
),
payment_agg AS (
  SELECT t3.age_bucket AS age_bucket, SUM(CAST(t4.amount / t5.rate AS NUMERIC(18,6))) AS matched_money_collected_eur
  FROM order_base t3
  JOIN payments t4
    ON t4.order_id = t3.order_id
    AND t4.status IN ('captured', 'refunded', 'partially_refunded')
  JOIN exchange_rates t5
    ON t5.base_code = 'EUR'
    AND t5.quote_code = t4.currency_code
    AND t5.as_of = CAST(t4.captured_at AS date)
  GROUP BY t3.age_bucket
)
SELECT
  t6.age_bucket AS age_bucket,
  t6.order_count AS order_count,
  COALESCE(t7.orders_grand_total_eur, 0) AS orders_grand_total_eur,
  COALESCE(t8.matched_money_collected_eur, 0) AS matched_money_collected_eur,
  COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100 AS match_rate_pct
FROM order_count_agg t6
LEFT JOIN grand_total_agg t7 ON t7.age_bucket = t6.age_bucket
LEFT JOIN payment_agg t8 ON t8.age_bucket = t6.age_bucket
ORDER BY
  CASE t6.age_bucket
    WHEN '0-3 days' THEN 1
    WHEN '4-7 days' THEN 2
    WHEN '8-14 days' THEN 3
    WHEN '15-30 days' THEN 4
    WHEN '31-60 days' THEN 5
    WHEN '61-90 days' THEN 6
    WHEN '91-180 days' THEN 7
    WHEN '181-365 days' THEN 8
    ELSE 9
  END NULLS LAST

Firebolt{ "errors": [ { "description": "Line 30, Column 63: 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": "0aeb3ab7-29d5-4d8f-9732-e9a5d7ac655f", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.grand_total Decimal(14, 4) Decimal64(size = 0), __table7.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Bea's whole session, 7 turns

The request, as Bea received it

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

Title: Order-to-payment match rate by order age
Question: At the portfolio level, how much of the gap between order value and money collected is just timing (recent orders not yet settled) versus a permanent shortfall that age doesn't fix?
Grain: order age bucket, measured as (2025-12-31 minus orders.placed_at) in days
Columns:
- age_bucket
- order_count
- orders_grand_total_eur
- matched_money_collected_eur
- match_rate_pct
Details: Population: all orders with status not 'cancelled' and placed_at between 2023-01-01 and 2025-12-31. Bucket each order by its age in days as of 2025-12-31: '0-3 days', '4-7 days', '8-14 days', '15-30 days', '31-60 days', '61-90 days', '91-180 days', '181-365 days', '366+ days'. order_count = count of orders in the bucket. orders_grand_total_eur = sum of orders.grand_total converted to EUR via exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of=placed_at date). matched_money_collected_eur = sum of linked payments.amount (payments.order_id = orders.id) where payments.status in ('captured','refunded','partially_refunded'), converted to EUR via payments.currency_code and captured_at date (an order's full matched total if it has multiple such payments). match_rate_pct = matched_money_collected_eur / orders_grand_total_eur * 100, rounded to 1 decimal. Tables: orders, payments, exchange_rates. Order by age_bucket from youngest to oldest.

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

Bea calls dry_run

WITH order_base AS (
  SELECT
    o.id AS order_id,
    o.grand_total,
    o.currency_code,
    o.placed_at,
    (DATE '2025-12-31' - CAST(o.placed_at AS date)) AS age_days,
    CASE
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 3 THEN '0-3 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 7 THEN '4-7 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 14 THEN '8-14 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 30 THEN '15-30 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 60 THEN '31-60 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 90 THEN '61-90 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 180 THEN '91-180 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS age_bucket
  FROM orders o
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
order_count_agg AS (
  SELECT t0.age_bucket, COUNT(*) AS order_count
  FROM order_base t0
  GROUP BY t0.age_bucket
),
grand_total_agg AS (
  SELECT t1.age_bucket, SUM(t1.grand_total / t2.rate) AS orders_grand_total_eur
  FROM order_base t1
  JOIN exchange_rates t2
    ON t2.base_code = 'EUR'
    AND t2.quote_code = t1.currency_code
    AND t2.as_of = CAST(t1.placed_at AS date)
  GROUP BY t1.age_bucket
),
payment_agg AS (
  SELECT t3.age_bucket, SUM(t4.amount / t5.rate) AS matched_money_collected_eur
  FROM order_base t3
  JOIN payments t4
    ON t4.order_id = t3.order_id
    AND t4.status IN ('captured', 'refunded', 'partially_refunded')
  JOIN exchange_rates t5
    ON t5.base_code = 'EUR'
    AND t5.quote_code = t4.currency_code
    AND t5.as_of = CAST(t4.captured_at AS date)
  GROUP BY t3.age_bucket
)
SELECT
  t6.age_bucket,
  t6.order_count,
  ROUND(COALESCE(t7.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0), 2) AS matched_money_collected_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100, 1) AS match_rate_pct
FROM order_count_agg t6
LEFT JOIN grand_total_agg t7 ON t7.age_bucket = t6.age_bucket
LEFT JOIN payment_agg t8 ON t8.age_bucket = t6.age_bucket
ORDER BY
  CASE t6.age_bucket
    WHEN '0-3 days' THEN 1
    WHEN '4-7 days' THEN 2
    WHEN '8-14 days' THEN 3
    WHEN '15-30 days' THEN 4
    WHEN '31-60 days' THEN 5
    WHEN '61-90 days' THEN 6
    WHEN '91-180 days' THEN 7
    WHEN '181-365 days' THEN 8
    ELSE 9
  END

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: age_bucket, order_count, orders_grand_total_eur, matched_money_collected_eur, match_rate_pct

BeaQuery compiles and returns the expected columns. Submitting.

Bea calls submit

WITH order_base AS (
  SELECT
    o.id AS order_id,
    o.grand_total,
    o.currency_code,
    o.placed_at,
    (DATE '2025-12-31' - CAST(o.placed_at AS date)) AS age_days,
    CASE
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 3 THEN '0-3 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 7 THEN '4-7 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 14 THEN '8-14 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 30 THEN '15-30 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 60 THEN '31-60 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 90 THEN '61-90 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 180 THEN '91-180 days'
      WHEN (DATE '2025-12-31' - CAST(o.placed_at AS date)) <= 365 THEN '181-365 days'
      ELSE '366+ days'
    END AS age_bucket
  FROM orders o
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
order_count_agg AS (
  SELECT t0.age_bucket, COUNT(*) AS order_count
  FROM order_base t0
  GROUP BY t0.age_bucket
),
grand_total_agg AS (
  SELECT t1.age_bucket, SUM(t1.grand_total / t2.rate) AS orders_grand_total_eur
  FROM order_base t1
  JOIN exchange_rates t2
    ON t2.base_code = 'EUR'
    AND t2.quote_code = t1.currency_code
    AND t2.as_of = CAST(t1.placed_at AS date)
  GROUP BY t1.age_bucket
),
payment_agg AS (
  SELECT t3.age_bucket, SUM(t4.amount / t5.rate) AS matched_money_collected_eur
  FROM order_base t3
  JOIN payments t4
    ON t4.order_id = t3.order_id
    AND t4.status IN ('captured', 'refunded', 'partially_refunded')
  JOIN exchange_rates t5
    ON t5.base_code = 'EUR'
    AND t5.quote_code = t4.currency_code
    AND t5.as_of = CAST(t4.captured_at AS date)
  GROUP BY t3.age_bucket
)
SELECT
  t6.age_bucket,
  t6.order_count,
  ROUND(COALESCE(t7.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0), 2) AS matched_money_collected_eur,
  ROUND(COALESCE(t8.matched_money_collected_eur, 0) / NULLIF(t7.orders_grand_total_eur, 0) * 100, 1) AS match_rate_pct
FROM order_count_agg t6
LEFT JOIN grand_total_agg t7 ON t7.age_bucket = t6.age_bucket
LEFT JOIN payment_agg t8 ON t8.age_bucket = t6.age_bucket
ORDER BY
  CASE t6.age_bucket
    WHEN '0-3 days' THEN 1
    WHEN '4-7 days' THEN 2
    WHEN '8-14 days' THEN 3
    WHEN '15-30 days' THEN 4
    WHEN '31-60 days' THEN 5
    WHEN '61-90 days' THEN 6
    WHEN '91-180 days' THEN 7
    WHEN '181-365 days' THEN 8
    ELSE 9
  END

The warehouse answers

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

BeaSubmitted. The query buckets non-cancelled orders placed between 2023-01-01 and 2025-12-31 by age as of 2025-12-31, converts both order grand totals and matched payment amounts (captured/refunded/partially_refunded) to EUR using the same-day exchange rate, and reports count, both EUR totals, and the match rate per bucket — ordered youngest to oldest as requested.

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 (272 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 (7 KB) · the week's memo (3 KB)

Order-to-payment match rate by order age

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

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q299

Everything, as recorded