Queries · q089 · Head of Data · week 1, night 1

Orders traced into captured payments

“Is there a clean, traceable link from an order to the money actually collected for it, or do orders vanish (no captured payment) or multiply (captured payments exceeding order value) along the way?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 1, request 2 of 3
Rosa wants breadth first across all five metrics before depth on any one, and Tobias's top two asks (revenue tie-out, payout reconciliation) sit inside that same breadth pass. So tonight I'm building the global, month-grain scaffolding that lets us see, in one glance, whether revenue/orders/refunds/payouts agree with each other at the top level, before drilling into any single tenant, currency, or the B2B receivables question (saving those for night 2+ once I know where the gaps are). All three reports are global (all tenants, EUR-converted) monthly time series over the full history (2023-01 through 2025-12) so we can also see whether any gap is new/worsening or has been there all along. I convert every local-currency amount to EUR via exchange_rates (EUR is base_code, so EUR amount = local amount divided by rate) using the date of the relevant event, consistently across all three reports so discrepancies aren't an artifact of inconsistent conversion. Report 1 answers whether different parts of the business agree on revenue. Report 2 traces orders forward into captured payments. Report 3 checks payouts against gross, fees and refunds. I'm holding back tenant-level and currency-timing drill-downs, and the B2B receivables question, for later nights.
Orders traced into captured payments
Question
Is there a clean, traceable link from an order to the money actually collected for it, or do orders vanish (no captured payment) or multiply (captured payments exceeding order value) along the way?
One row per
calendar month, 2023-01 through 2025-12, based on order placed_at month
Columns
- month
- orders_count
- orders_grand_total_eur
- orders_with_captured_payment_count
- captured_payment_total_eur
- orders_without_captured_payment_count
- gap_eur
- gap_pct

Ines writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: REJECTED

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,
         to_char(o.placed_at, 'YYYY-MM') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id, ofl.month_label,
         ofl.grand_total / er.rate AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id, pc.order_id, ofl.month_label,
         pc.amount / er.rate AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label

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

WITH months AS (  SELECT DISTINCT month_label, month_start  FROM calendar_days  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'),orders_filtered AS (  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,         to_char(o.placed_at, 'YYYY-MM') AS month_label         strftime(o.placed_at, '%Y-%m') AS month_label  FROM orders o  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'),orders_eur AS (  SELECT ofl.order_id, ofl.month_label,         ofl.grand_total / er.rate AS grand_total_eur  FROM orders_filtered ofl  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code    AND er.as_of = CAST(ofl.placed_at AS date)),payments_captured AS (  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at  FROM payments p  WHERE p.status = 'captured' AND p.order_id IS NOT NULL),payments_linked AS (  SELECT pc.payment_id, pc.order_id, ofl.month_label,         pc.amount / er.rate AS amount_eur  FROM payments_captured pc  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)),order_agg AS (  SELECT month_label,         COUNT(*) AS orders_count,         SUM(grand_total_eur) AS orders_grand_total_eur  FROM orders_eur  GROUP BY month_label),order_with_payment AS (  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count  FROM payments_linked  GROUP BY month_label),payment_agg AS (  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur  FROM payments_linked  GROUP BY month_label)SELECT  m.month_label AS month,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)  END AS gap_pctFROM months mLEFT JOIN order_agg oa ON oa.month_label = m.month_labelLEFT JOIN order_with_payment owp ON owp.month_label = m.month_labelLEFT JOIN payment_agg pa ON pa.month_label = m.month_labelORDER BY m.month_label

“Query compiles and produces the requested 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 calendar month, 2023-01 through 2025-12 (full spine from calendar_days, so no month is skipped even if empty). - Order cohort: non-cancelled orders grouped by the month of placed_at, with grand_total converted to EUR using the EUR→currency rate as of the placed date. - Captured payments: payments rows with status='captured' joined to the same order cohort via order_id, converted to EUR using the rate as of captured_at (or placed_at if captured_at is missing). - Measures: orders_count, orders_grand_total_eur, distinct orders with ≥1 captured payment, total captured payment EUR, orders with no captured payment (count), and the resulting gap in EUR and as a percentage of order value. One judgment call noted in the submit note: payment amounts are converted using the payment's own captured date (not the order's placed date), since that's the economically correct event date; I flagged this in case the analyst expected a single conversion date per order.”

The report comes back overnight

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

month   | orders_count | orders_grand_total_eur | orders_with_captured_payment_count | captured_payment_total_eur | orders_without_captured_payment_count |   gap_eur | gap_pct
--------+--------------+------------------------+------------------------------------+----------------------------+---------------------------------------+-----------+--------
2023-01 |          275 |                91008.3 |                                186 |                   63230.42 |                                    89 |  27777.88 |    30.5
2023-02 |          265 |               83045.67 |                                195 |                   58918.04 |                                    70 |  24127.63 |    29.1
2023-03 |          314 |              119694.55 |                                224 |                  100212.61 |                                    90 |  19481.95 |    16.3
2023-04 |          331 |               98534.83 |                                231 |                   59987.32 |                                   100 |  38547.51 |    39.1
2023-05 |          402 |              135971.02 |                                286 |                  106163.21 |                                   116 |  29807.81 |    21.9
2023-06 |          362 |               78438.84 |                                255 |                   60036.91 |                                   107 |  18401.93 |    23.5
2023-07 |          392 |              113754.47 |                                268 |                   89999.97 |                                   124 |   23754.5 |    20.9
2023-08 |          370 |              106351.86 |                                268 |                   84115.43 |                                   102 |  22236.44 |    20.9
2023-09 |          415 |               95570.89 |                                292 |                    69220.7 |                                   123 |  26350.19 |    27.6
2023-10 |          528 |              126934.07 |                                382 |                   96716.31 |                                   146 |  30217.76 |    23.8
2023-11 |         1037 |              249227.23 |                                685 |                  161336.47 |                                   352 |  87890.75 |    35.3
2023-12 |          868 |              188375.92 |                                590 |                  124844.76 |                                   278 |  63531.16 |    33.7
2024-01 |          653 |              169303.39 |                                440 |                   123958.2 |                                   213 |  45345.19 |    26.8
2024-02 |          516 |              135473.31 |                                371 |                  107539.67 |                                   145 |  27933.64 |    20.6
2024-03 |          548 |              137254.12 |                                361 |                  100203.56 |                                   187 |  37050.57 |    27.0
2024-04 |          587 |              161281.28 |                                401 |                  132497.95 |                                   186 |  28783.33 |    17.8
2024-05 |          663 |              185763.86 |                                450 |                  149006.24 |                                   213 |  36757.63 |    19.8
2024-06 |          659 |              130547.67 |                                436 |                   95064.72 |                                   223 |  35482.96 |    27.2
2024-07 |          694 |              211861.18 |                                434 |                  177898.77 |                                   260 |  33962.41 |    16.0
2024-08 |          626 |              154838.72 |                                410 |                  122632.42 |                                   216 |   32206.3 |    20.8
2024-09 |          656 |              165908.69 |                                430 |                  128225.01 |                                   226 |  37683.68 |    22.7
2024-10 |          767 |              201478.08 |                                517 |                  160116.65 |                                   250 |  41361.43 |    20.5
2024-11 |         1177 |              268367.57 |                                779 |                  185993.69 |                                   398 |  82373.88 |    30.7
2024-12 |         1517 |              309476.98 |                               1035 |                  211290.01 |                                   482 |  98186.97 |    31.7
2025-01 |          900 |              346981.34 |                                600 |                  293029.21 |                                   300 |  53952.14 |    15.5
2025-02 |          706 |               174721.1 |                                489 |                  144461.25 |                                   217 |  30259.85 |    17.3
2025-03 |          811 |              199394.65 |                                543 |                  160832.69 |                                   268 |  38561.96 |    19.3
2025-04 |          777 |              232332.56 |                                494 |                  188455.63 |                                   283 |  43876.92 |    18.9
2025-05 |          865 |              152855.42 |                                567 |                  112335.77 |                                   298 |  40519.65 |    26.5
2025-06 |          782 |              164239.26 |                                508 |                  124764.35 |                                   274 |  39474.91 |    24.0
2025-07 |          894 |              180052.83 |                                608 |                  148728.79 |                                   286 |  31324.05 |    17.4
2025-08 |          781 |              171818.78 |                                503 |                  140972.86 |                                   278 |  30845.92 |    18.0
2025-09 |          882 |              190535.95 |                                571 |                  148873.74 |                                   311 |  41662.21 |    21.9
2025-10 |          986 |              232741.04 |                                636 |                  166776.96 |                                   350 |  65964.08 |    28.3
2025-11 |         1517 |               328682.7 |                               1001 |                  216334.52 |                                   516 | 112348.17 |    34.2
2025-12 |         1756 |              332982.57 |                               1421 |                  252419.74 |                                   335 |  80562.83 |    24.2
(36 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:
- 2. Orders do trace cleanly to money collected — my first read overstated the gap. (Orders traced into captured payments; Order-to-payment gap by status, both passes) A captured-only filter showed 20-40% of order value with no matching payment. Re-running with refunded/partially_refunded payments included (orders aged 6+ months, so timing can't explain it) dropped the gap to 0.9% for fulfilled orders and 5.3% for refunded orders. Cancelled orders' apparent 70.4% gap is fully explained: only 259 of 788 were ever charged, and those were refunded to exactly €0 net. Confident: orders reconcile to collected cash; any future query on this must include refunded/partially_refunded statuses, not just captured.
Later queries that build on this report:
- q091Order-to-payment gap by order status, aged cohort

It becomes benchmark query q089

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

placeholderyear month
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
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) (2×)
made explicita selected t.col gets AS col, its standard name made explicit (13×)
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 months AS (  SELECT DISTINCT month_label, month_start  FROM calendar_days  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'),orders_filtered AS (  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,         strftime(o.placed_at, '%Y-%m') AS month_label  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at,         {{year_month(o.placed_at)}} AS month_label  FROM orders o  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'),orders_eur AS (  SELECT ofl.order_id, ofl.month_label,         ofl.grand_total / er.rate AS grand_total_eur  SELECT ofl.order_id AS order_id, ofl.month_label AS month_label,         CAST(ofl.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur  FROM orders_filtered ofl  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code    AND er.as_of = CAST(ofl.placed_at AS date)),payments_captured AS (  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at  SELECT p.id AS payment_id, p.order_id AS order_id, p.amount AS amount, p.currency_code AS currency_code, p.captured_at AS captured_at  FROM payments p  WHERE p.status = 'captured' AND p.order_id IS NOT NULL),payments_linked AS (  SELECT pc.payment_id, pc.order_id, ofl.month_label,         pc.amount / er.rate AS amount_eur  SELECT pc.payment_id AS payment_id, pc.order_id AS order_id, ofl.month_label AS month_label,         CAST(pc.amount / er.rate AS NUMERIC(18,6)) AS amount_eur  FROM payments_captured pc  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)),order_agg AS (  SELECT month_label,         COUNT(*) AS orders_count,         SUM(grand_total_eur) AS orders_grand_total_eur  FROM orders_eur  GROUP BY month_label),order_with_payment AS (  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count  FROM payments_linked  GROUP BY month_label),payment_agg AS (  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur  FROM payments_linked  GROUP BY month_label)SELECT  m.month_label AS month,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,  COALESCE(oa.orders_grand_total_eur, 0) AS orders_grand_total_eur,  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,  COALESCE(pa.captured_payment_total_eur, 0) AS captured_payment_total_eur,  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,  COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0) AS gap_eur,  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)       ELSE (COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100  END AS gap_pctFROM months mLEFT JOIN order_agg oa ON oa.month_label = m.month_labelLEFT JOIN order_with_payment owp ON owp.month_label = m.month_labelLEFT JOIN payment_agg pa ON pa.month_label = m.month_labelORDER BY m.month_labelORDER BY m.month_label NULLS LAST

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

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at,
         strftime(o.placed_at, '%Y-%m') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id AS order_id, ofl.month_label AS month_label,
         CAST(ofl.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id AS order_id, p.amount AS amount, p.currency_code AS currency_code, p.captured_at AS captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id AS payment_id, pc.order_id AS order_id, ofl.month_label AS month_label,
         CAST(pc.amount / er.rate AS NUMERIC(18,6)) AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_grand_total_eur, 0) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  COALESCE(pa.captured_payment_total_eur, 0) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE (COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label NULLS LAST

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at,
         to_char(o.placed_at, 'YYYY-MM') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id AS order_id, ofl.month_label AS month_label,
         CAST(ofl.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id AS order_id, p.amount AS amount, p.currency_code AS currency_code, p.captured_at AS captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id AS payment_id, pc.order_id AS order_id, ofl.month_label AS month_label,
         CAST(pc.amount / er.rate AS NUMERIC(18,6)) AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_grand_total_eur, 0) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  COALESCE(pa.captured_payment_total_eur, 0) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE (COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label NULLS LAST

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at,
         to_char(CAST(o.placed_at AS timestamp), 'YYYY-MM') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id AS order_id, ofl.month_label AS month_label,
         CAST(ofl.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id AS order_id, p.amount AS amount, p.currency_code AS currency_code, p.captured_at AS captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id AS payment_id, pc.order_id AS order_id, ofl.month_label AS month_label,
         CAST(pc.amount / er.rate AS NUMERIC(18,6)) AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_grand_total_eur, 0) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  COALESCE(pa.captured_payment_total_eur, 0) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE (COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label NULLS LAST

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at,
         formatDateTime(o.placed_at, '%Y-%m') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id AS order_id, ofl.month_label AS month_label,
         CAST(ofl.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id AS order_id, p.amount AS amount, p.currency_code AS currency_code, p.captured_at AS captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id AS payment_id, pc.order_id AS order_id, ofl.month_label AS month_label,
         CAST(pc.amount / er.rate AS NUMERIC(18,6)) AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_grand_total_eur, 0) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  COALESCE(pa.captured_payment_total_eur, 0) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE (COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label NULLS LAST

Firebolt{ "errors": [ { "description": "Line 15, Column 31: 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": "be19c53d-1834-4297-8d2b-add649cdfb61", "query_lab
DataFusionrow 1: col 2: "275065" vs "0"; col 3: "95156218.716086" vs "0.000000"; col 4: "170165" vs "0"
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. q089 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

Ines's whole session, 9 turns

The request, as Ines received it

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

Title: Orders traced into captured payments
Question: Is there a clean, traceable link from an order to the money actually collected for it, or do orders vanish (no captured payment) or multiply (captured payments exceeding order value) along the way?
Grain: calendar month, 2023-01 through 2025-12, based on order placed_at month
Columns:
- month
- orders_count
- orders_grand_total_eur
- orders_with_captured_payment_count
- captured_payment_total_eur
- orders_without_captured_payment_count
- gap_eur
- gap_pct
Details: Scope: all tenants combined. Base population: orders with status not 'cancelled', grouped by the month of placed_at. orders_count = count of such orders. orders_grand_total_eur = sum of their grand_total, converted to EUR. Join to payments on payments.order_id = orders.id and payments.status='captured' to find linked captured payments (an order can have 0, 1, or multiple captured payments). orders_with_captured_payment_count = count of distinct orders with at least one linked captured payment. captured_payment_total_eur = sum of all captured payments.amount linked to orders in that month's cohort, converted to EUR. orders_without_captured_payment_count = orders_count minus orders_with_captured_payment_count. gap_eur = orders_grand_total_eur minus captured_payment_total_eur. gap_pct = gap_eur divided by orders_grand_total_eur times 100, rounded to 1 decimal. Convert orders.grand_total using orders.currency_code and payments.amount using payments.currency_code, both via exchange_rates (EUR amount = local / rate) using the relevant event date. Tables: orders, payments, exchange_rates. Order by month ascending.

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

Ines calls dry_run

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,
         to_char(o.placed_at, 'YYYY-MM') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id, ofl.month_label,
         ofl.grand_total / er.rate AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id, pc.order_id, ofl.month_label,
         pc.amount / er.rate AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Ines calls dry_run

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,
         strftime(o.placed_at, '%Y-%m') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id, ofl.month_label,
         ofl.grand_total / er.rate AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id, pc.order_id, ofl.month_label,
         pc.amount / er.rate AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_count, orders_grand_total_eur, orders_with_captured_payment_count, captured_payment_total_eur, orders_without_captured_payment_count, gap_eur, gap_pct

InesQuery compiles and produces the requested columns. Submitting.

Ines calls submit

WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date >= DATE '2023-01-01' AND calendar_date <= DATE '2025-12-31'
),
orders_filtered AS (
  SELECT o.id AS order_id, o.tenant_id, o.currency_code, o.grand_total, o.placed_at,
         strftime(o.placed_at, '%Y-%m') AS month_label
  FROM orders o
  WHERE o.status <> 'cancelled' AND o.placed_at IS NOT NULL
    AND o.placed_at >= TIMESTAMP '2023-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
),
orders_eur AS (
  SELECT ofl.order_id, ofl.month_label,
         ofl.grand_total / er.rate AS grand_total_eur
  FROM orders_filtered ofl
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = ofl.currency_code
    AND er.as_of = CAST(ofl.placed_at AS date)
),
payments_captured AS (
  SELECT p.id AS payment_id, p.order_id, p.amount, p.currency_code, p.captured_at
  FROM payments p
  WHERE p.status = 'captured' AND p.order_id IS NOT NULL
),
payments_linked AS (
  SELECT pc.payment_id, pc.order_id, ofl.month_label,
         pc.amount / er.rate AS amount_eur
  FROM payments_captured pc
  JOIN orders_filtered ofl ON ofl.order_id = pc.order_id
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pc.currency_code
    AND er.as_of = CAST(COALESCE(pc.captured_at, ofl.placed_at) AS date)
),
order_agg AS (
  SELECT month_label,
         COUNT(*) AS orders_count,
         SUM(grand_total_eur) AS orders_grand_total_eur
  FROM orders_eur
  GROUP BY month_label
),
order_with_payment AS (
  SELECT month_label, COUNT(DISTINCT order_id) AS orders_with_captured_payment_count
  FROM payments_linked
  GROUP BY month_label
),
payment_agg AS (
  SELECT month_label, SUM(amount_eur) AS captured_payment_total_eur
  FROM payments_linked
  GROUP BY month_label
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0), 2) AS orders_grand_total_eur,
  COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_with_captured_payment_count,
  ROUND(COALESCE(pa.captured_payment_total_eur, 0), 2) AS captured_payment_total_eur,
  COALESCE(oa.orders_count, 0) - COALESCE(owp.orders_with_captured_payment_count, 0) AS orders_without_captured_payment_count,
  ROUND(COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0), 2) AS gap_eur,
  CASE WHEN COALESCE(oa.orders_grand_total_eur, 0) = 0 THEN NULL
       ELSE ROUND((COALESCE(oa.orders_grand_total_eur, 0) - COALESCE(pa.captured_payment_total_eur, 0)) / oa.orders_grand_total_eur * 100, 1)
  END AS gap_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month_label = m.month_label
LEFT JOIN order_with_payment owp ON owp.month_label = m.month_label
LEFT JOIN payment_agg pa ON pa.month_label = m.month_label
ORDER BY m.month_label

The warehouse answers

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

InesSubmitted. Summary of what the query does: - **Grain:** one row per calendar month, 2023-01 through 2025-12 (full spine from `calendar_days`, so no month is skipped even if empty). - **Order cohort:** non-cancelled orders grouped by the month of `placed_at`, with `grand_total` converted to EUR using the EUR→currency rate as of the placed date. - **Captured payments:** `payments` rows with `status='captured'` joined to the same order cohort via `order_id`, converted to EUR using the rate as of `captured_at` (or `placed_at` if `captured_at` is missing). - **Measures:** orders_count, orders_grand_total_eur, distinct orders with ≥1 captured payment, total captured payment EUR, orders with no captured payment (count), and the resulting gap in EUR and as a percentage of order value. One judgment call noted in the submit note: payment amounts are converted using the payment's own captured date (not the order's placed date), since that's the economically correct event date; I flagged this in case the analyst expected a single conversion date per order.

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

Ines's transcript (284 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 (19 KB) · the week's memo (3 KB)

Orders traced into captured payments

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

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q089

Everything, as recorded