Queries · q091 · Head of Data · week 1, night 2
“Is the 20-40% gap between order value and captured payments explained by order status (i.e. structural - many orders simply never get paid), or does it look like something else, once we only look at orders old enough that timing lag can't be the excuse?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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?
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:
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
Subject: What I need for the revenue/payout certification
Hi Yuki,
A few things from my side as you scope this week's work:
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
The analyst · week 1, night 2, request 1 of 3
Night 1 surfaced three things worth running down before I widen scope further: (1) orders_booked_revenue is consistently 15-40% above captured payments, and critically this gap does NOT shrink for old cohorts (e.g. 2023-04 orders are 2.5 years old and still show a 39% gap, 2025-01 orders are a year old and still show 15.5%) — that rules out simple timing lag and points to a structural cause (orders that never convert to a captured payment, e.g. abandoned/declined/cancelled), which matters a lot for "revenue" certification if orders.grand_total is ever quoted as revenue. I want to confirm this is explained by order status rather than something stranger. (2) invoice_paid_revenue is much smaller than order revenue and I'm not yet sure whether it's incremental (subscription billing on top of orders) or overlapping (already counted inside order revenue) — that changes whether we're under- or double-counting. (3) Report 3's net_check_diff_eur (net vs gross-fee) is large and growing, but shrinks a lot once refunds are subtracted too, suggesting the true formula is net ≈ gross - fee - refunds and refunds aren't double-counted — but I matched refunds by calendar month only, which is a loose proxy and leaves real noise (-6k to +21k swings). I want the structural, payment-linked version of that reconciliation via payout_items to see if the noise disappears, which would let us certify the payout formula with confidence rather than a caveat.
The engineer · 1 dry run on the empty schema, then the submission
SELECT pop.status AS order_status, pop.order_count, ROUND(pop.order_count * 100.0 / SUM(pop.order_count) OVER (), 1) AS pct_of_orders, ROUND(pop.grand_total_eur, 2) AS orders_grand_total_eur, pop.orders_with_captured_payment_count, ROUND(COALESCE(pay.captured_eur, 0), 2) AS matched_captured_payment_eur, ROUND(pop.grand_total_eur - COALESCE(pay.captured_eur, 0), 2) AS gap_eur, ROUND((pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100, 1) AS gap_pct FROM ( SELECT o.status, COUNT(*) AS order_count, SUM(o.grand_total / er.rate) AS grand_total_eur, COUNT(CASE WHEN EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.status = 'captured' ) THEN 1 END) AS orders_with_captured_payment_count FROM orders o JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pop LEFT JOIN ( SELECT o.status, SUM(p.amount / er2.rate) AS captured_eur FROM payments p JOIN orders o ON o.id = p.order_id JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = p.currency_code AND er2.as_of = CAST(p.captured_at AS date) WHERE p.status = 'captured' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pay ON pay.status = pop.status ORDER BY orders_grand_total_eur DESC, pop.status
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report groups orders placed between 2023-01-01 and 2025-06-30 by orders.status, giving order counts/share, EUR-converted order totals, how many of those orders have at least one captured payment, the EUR-converted captured amount, and the resulting gap in EUR and percent — which should let Yuki see whether the gap concentrates in non-paid-looking statuses (pending/cancelled) or shows up even in paid/fulfilled orders old enough that timing can't explain it.”
As Yuki Tanaka saw it, from the small dataset the agents worked on
order_status | order_count | pct_of_orders | orders_grand_total_eur | orders_with_captured_payment_count | matched_captured_payment_eur | gap_eur | gap_pct -------------+-------------+---------------+------------------------+------------------------------------+------------------------------+------------+-------- fulfilled | 18540 | 91.6 | 4863934.96 | 13120 | 3792892.83 | 1071042.14 | 22.0 cancelled | 788 | 3.9 | 158380.3 | 0 | 0.0 | 158380.3 | 100.0 refunded | 923 | 4.6 | 125051.87 | 7 | 195.09 | 124856.78 | 99.8 (3 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT pop.status AS order_status, pop.order_count, ROUND(pop.order_count * 100.0 / SUM(pop.order_count) OVER (), 1) AS pct_of_orders, ROUND(pop.grand_total_eur, 2) AS orders_grand_total_eur, pop.orders_with_captured_payment_count, ROUND(COALESCE(pay.captured_eur, 0), 2) AS matched_captured_payment_eur, ROUND(pop.grand_total_eur - COALESCE(pay.captured_eur, 0), 2) AS gap_eur, ROUND((pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100, 1) AS gap_pct pop.order_count AS order_count, pop.order_count * 100.0 / SUM(pop.order_count) OVER () AS pct_of_orders, pop.grand_total_eur AS orders_grand_total_eur, pop.orders_with_captured_payment_count AS orders_with_captured_payment_count, COALESCE(pay.captured_eur, 0) AS matched_captured_payment_eur, pop.grand_total_eur - COALESCE(pay.captured_eur, 0) AS gap_eur, (pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100 AS gap_pctFROM ( SELECT o.status, o.status AS status, COUNT(*) AS order_count, SUM(o.grand_total / er.rate) AS grand_total_eur, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS grand_total_eur, COUNT(CASE WHEN EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.status = 'captured' ) THEN 1 END) AS orders_with_captured_payment_count FROM orders o JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status) popLEFT JOIN ( SELECT o.status, SUM(p.amount / er2.rate) AS captured_eur o.status AS status, SUM(CAST(p.amount / er2.rate AS NUMERIC(18,6))) AS captured_eur FROM payments p JOIN orders o ON o.id = p.order_id JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = p.currency_code AND er2.as_of = CAST(p.captured_at AS date) WHERE p.status = 'captured' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status) pay ON pay.status = pop.statusORDER BY orders_grand_total_eur DESC, pop.statusORDER BY orders_grand_total_eur DESC NULLS FIRST, pop.status NULLS LAST
SELECT pop.status AS order_status, pop.order_count AS order_count, pop.order_count * 100.0 / SUM(pop.order_count) OVER () AS pct_of_orders, pop.grand_total_eur AS orders_grand_total_eur, pop.orders_with_captured_payment_count AS orders_with_captured_payment_count, COALESCE(pay.captured_eur, 0) AS matched_captured_payment_eur, pop.grand_total_eur - COALESCE(pay.captured_eur, 0) AS gap_eur, (pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100 AS gap_pct FROM ( SELECT o.status AS status, COUNT(*) AS order_count, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS grand_total_eur, COUNT(CASE WHEN EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.status = 'captured' ) THEN 1 END) AS orders_with_captured_payment_count FROM orders o JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pop LEFT JOIN ( SELECT o.status AS status, SUM(CAST(p.amount / er2.rate AS NUMERIC(18,6))) AS captured_eur FROM payments p JOIN orders o ON o.id = p.order_id JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = p.currency_code AND er2.as_of = CAST(p.captured_at AS date) WHERE p.status = 'captured' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pay ON pay.status = pop.status ORDER BY orders_grand_total_eur DESC NULLS FIRST, pop.status NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Yuki Tanaka (analyst for the Head of Data): Title: Order-to-payment gap by order status, aged cohort Question: Is the 20-40% gap between order value and captured payments explained by order status (i.e. structural - many orders simply never get paid), or does it look like something else, once we only look at orders old enough that timing lag can't be the excuse? Grain: order status Columns: - order_status - order_count - pct_of_orders - orders_grand_total_eur - orders_with_captured_payment_count - matched_captured_payment_eur - gap_eur - gap_pct Details: Population: orders with placed_at between 2023-01-01 and 2025-06-30 inclusive (at least 6 months old as of the 2025-12-31 data cutoff, so any legitimate capture lag has had time to resolve). Group by orders.status (pending, confirmed, paid, partially_fulfilled, fulfilled, cancelled, refunded). order_count = count of orders in that status. pct_of_orders = order_count / total orders in the population, as a percentage rounded to 1 decimal. orders_grand_total_eur = sum of grand_total converted to EUR (via exchange_rates, base_code='EUR', rate matched to placed_at date and orders.currency_code). orders_with_captured_payment_count = count of those orders with at least one linked payments row (payments.order_id = orders.id) with status='captured'. matched_captured_payment_eur = sum of those linked captured payments.amount, converted to EUR using payments.currency_code and captured_at date. gap_eur = orders_grand_total_eur minus matched_captured_payment_eur. gap_pct = gap_eur / orders_grand_total_eur * 100, rounded to 1 decimal. Tables: orders, payments, exchange_rates. Order by orders_grand_total_eur descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT pop.status AS order_status, pop.order_count, ROUND(pop.order_count * 100.0 / SUM(pop.order_count) OVER (), 1) AS pct_of_orders, ROUND(pop.grand_total_eur, 2) AS orders_grand_total_eur, pop.orders_with_captured_payment_count, ROUND(COALESCE(pay.captured_eur, 0), 2) AS matched_captured_payment_eur, ROUND(pop.grand_total_eur - COALESCE(pay.captured_eur, 0), 2) AS gap_eur, ROUND((pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100, 1) AS gap_pct FROM ( SELECT o.status, COUNT(*) AS order_count, SUM(o.grand_total / er.rate) AS grand_total_eur, COUNT(CASE WHEN EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.status = 'captured' ) THEN 1 END) AS orders_with_captured_payment_count FROM orders o JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pop LEFT JOIN ( SELECT o.status, SUM(p.amount / er2.rate) AS captured_eur FROM payments p JOIN orders o ON o.id = p.order_id JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = p.currency_code AND er2.as_of = CAST(p.captured_at AS date) WHERE p.status = 'captured' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pay ON pay.status = pop.status ORDER BY orders_grand_total_eur DESC, pop.status
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: order_status, order_count, pct_of_orders, orders_grand_total_eur, orders_with_captured_payment_count, matched_captured_payment_eur, gap_eur, gap_pct
submitSELECT pop.status AS order_status, pop.order_count, ROUND(pop.order_count * 100.0 / SUM(pop.order_count) OVER (), 1) AS pct_of_orders, ROUND(pop.grand_total_eur, 2) AS orders_grand_total_eur, pop.orders_with_captured_payment_count, ROUND(COALESCE(pay.captured_eur, 0), 2) AS matched_captured_payment_eur, ROUND(pop.grand_total_eur - COALESCE(pay.captured_eur, 0), 2) AS gap_eur, ROUND((pop.grand_total_eur - COALESCE(pay.captured_eur, 0)) / NULLIF(pop.grand_total_eur, 0) * 100, 1) AS gap_pct FROM ( SELECT o.status, COUNT(*) AS order_count, SUM(o.grand_total / er.rate) AS grand_total_eur, COUNT(CASE WHEN EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.status = 'captured' ) THEN 1 END) AS orders_with_captured_payment_count FROM orders o JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS date) WHERE o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pop LEFT JOIN ( SELECT o.status, SUM(p.amount / er2.rate) AS captured_eur FROM payments p JOIN orders o ON o.id = p.order_id JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = p.currency_code AND er2.as_of = CAST(p.captured_at AS date) WHERE p.status = 'captured' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2025-07-01' GROUP BY o.status ) pay ON pay.status = pop.status ORDER BY orders_grand_total_eur DESC, pop.status
Submitted to tonight's batch. Your work on this request is done.