Queries · q088 · Head of Data · week 1, night 1
“If we ask what revenue was in a given month from four different places (orders booked, payments captured, payouts settled, invoices paid), do we get the same answer, and how far apart are they?”
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 1, request 1 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.
The engineer · 1 dry run on the empty schema, then the submission
WITH months AS ( SELECT DISTINCT month_start, month_label FROM calendar_days WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01' ), orders_rev AS ( SELECT date_trunc('month', o.placed_at)::date AS month_start, ROUND(SUM(o.grand_total / er.rate), 2) AS orders_booked_revenue_eur, COUNT(*) AS order_count FROM orders o LEFT 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.status <> 'cancelled' AND o.placed_at IS NOT NULL GROUP BY 1 ), payments_rev AS ( SELECT date_trunc('month', p.captured_at)::date AS month_start, ROUND(SUM(p.amount / er.rate), 2) AS payments_captured_eur, COUNT(*) AS payment_count FROM payments p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS DATE) WHERE p.status = 'captured' AND p.captured_at IS NOT NULL GROUP BY 1 ), payouts_rev AS ( SELECT date_trunc('month', po.paid_at)::date AS month_start, ROUND(SUM(po.gross_amount / er.rate), 2) AS payout_gross_eur FROM payouts po LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = po.currency_code AND er.as_of = CAST(po.paid_at AS DATE) WHERE po.paid_at IS NOT NULL GROUP BY 1 ), invoices_rev AS ( SELECT date_trunc('month', i.paid_at)::date AS month_start, ROUND(SUM(i.total / er.rate), 2) AS invoice_paid_revenue_eur FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at IS NOT NULL GROUP BY 1 ), refunds_rev AS ( SELECT date_trunc('month', pr.processed_at)::date AS month_start, ROUND(SUM(pr.amount / er.rate), 2) AS refunds_eur FROM payment_refunds pr LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS DATE) WHERE pr.status = 'succeeded' AND pr.processed_at IS NOT NULL GROUP BY 1 ) SELECT m.month_label AS month, COALESCE(orr.orders_booked_revenue_eur, 0) AS orders_booked_revenue_eur, COALESCE(pay.payments_captured_eur, 0) AS payments_captured_eur, COALESCE(pou.payout_gross_eur, 0) AS payout_gross_eur, COALESCE(inv.invoice_paid_revenue_eur, 0) AS invoice_paid_revenue_eur, COALESCE(ref.refunds_eur, 0) AS refunds_eur, COALESCE(orr.order_count, 0) AS order_count, COALESCE(pay.payment_count, 0) AS payment_count FROM months m LEFT JOIN orders_rev orr ON orr.month_start = m.month_start LEFT JOIN payments_rev pay ON pay.month_start = m.month_start LEFT JOIN payouts_rev pou ON pou.month_start = m.month_start LEFT JOIN invoices_rev inv ON inv.month_start = m.month_start LEFT JOIN refunds_rev ref ON ref.month_start = m.month_start ORDER BY m.month_start
“The query compiles and returns exactly the 8 requested columns at monthly grain. Submitting.”
“Submitted. The report gives one row per calendar month (2023-01 through 2025-12, 36 rows) with all four revenue views side by side in EUR plus refunds and the two volume counts, so Yuki can directly compare how far apart "orders booked," "payments captured," "payouts settled," and "invoices paid" run in any given month.”
As Yuki Tanaka saw it, from the small dataset the agents worked on
month | orders_booked_revenue_eur | payments_captured_eur | payout_gross_eur | invoice_paid_revenue_eur | refunds_eur | order_count | payment_count --------+---------------------------+-----------------------+------------------+--------------------------+-------------+-------------+-------------- 2023-01 | 91008.3 | 31637.57 | 53709.82 | 240.1 | 3393.01 | 499 | 361 2023-02 | 83045.67 | 61583.77 | 50590.75 | 30447.48 | 13034.09 | 522 | 415 2023-03 | 119694.55 | 48729.2 | 55055.62 | 11805.8 | 9083.75 | 611 | 494 2023-04 | 98534.83 | 89063.47 | 68499.6 | 53361.54 | 8376.32 | 653 | 534 2023-05 | 135971.02 | 88119.12 | 93673.67 | 34048.73 | 11213.65 | 772 | 638 2023-06 | 78438.84 | 87513.97 | 61954.44 | 51424.95 | 9932.92 | 713 | 606 2023-07 | 113754.47 | 79342.56 | 64924.71 | 40185.66 | 10670.89 | 762 | 645 2023-08 | 106351.86 | 86331.25 | 65320.68 | 44857.34 | 10041.38 | 813 | 672 2023-09 | 95570.89 | 68523.5 | 70968.13 | 26241.1 | 9028.08 | 877 | 712 2023-10 | 126934.07 | 113753.8 | 98006.93 | 43286.11 | 12739.55 | 1049 | 856 2023-11 | 249227.23 | 138897.26 | 192998.2 | 32148.67 | 16689.72 | 1787 | 1370 2023-12 | 188375.92 | 158862.82 | 172550.08 | 57431.19 | 45282.9 | 1540 | 1246 2024-01 | 169303.39 | 98061.9 | 112246.44 | 29369.94 | 28762.24 | 1275 | 1030 2024-02 | 135473.31 | 97579.58 | 112012.26 | 17026.41 | 15721.05 | 1077 | 941 2024-03 | 137254.12 | 137526.73 | 110386.35 | 66620.11 | 12224.57 | 1166 | 970 2024-04 | 161281.28 | 105686.23 | 115514.24 | 26086.24 | 16993.01 | 1194 | 978 2024-05 | 185763.86 | 133043.31 | 121296.23 | 50734.02 | 15230.21 | 1309 | 1086 2024-06 | 130547.67 | 100866.4 | 100219.73 | 42702.25 | 15315.98 | 1265 | 1032 2024-07 | 211861.18 | 125672.04 | 108894.15 | 55863.56 | 14215.29 | 1331 | 1063 2024-08 | 154838.72 | 125847.43 | 96008.55 | 66551.6 | 18128.78 | 1271 | 1047 2024-09 | 165908.69 | 184794.84 | 113343.79 | 104050.4 | 15130.15 | 1315 | 1069 2024-10 | 201478.08 | 163948.67 | 133784.95 | 74227.13 | 13860.5 | 1457 | 1190 2024-11 | 268367.57 | 181532.89 | 177317.93 | 67457.53 | 20434.5 | 2056 | 1605 2024-12 | 309476.98 | 228914.7 | 286218.09 | 69924.06 | 58827.06 | 2546 | 2044 2025-01 | 346981.34 | 219325.7 | 199172.31 | 87669.68 | 36758.42 | 1768 | 1455 2025-02 | 174721.1 | 185390.87 | 117777.21 | 94256.49 | 18604.99 | 1370 | 1162 2025-03 | 199394.65 | 166350.88 | 135070.48 | 74183.95 | 16479.69 | 1586 | 1332 2025-04 | 232332.56 | 210209.5 | 188851.76 | 69077.99 | 16429.99 | 1520 | 1263 2025-05 | 152855.42 | 128106.12 | 127570.11 | 55535.48 | 26537.35 | 1633 | 1311 2025-06 | 164239.26 | 136523.52 | 113673.02 | 65902.64 | 19509.64 | 1461 | 1213 2025-07 | 180052.83 | 172036.43 | 132602.63 | 77495.8 | 17897.73 | 1596 | 1307 2025-08 | 171818.78 | 125315.55 | 141126.23 | 47444.26 | 39838.78 | 1502 | 1229 2025-09 | 190535.95 | 152057.98 | 122449.4 | 70502.79 | 15922.5 | 1659 | 1355 2025-10 | 232741.04 | 168510.39 | 154129.95 | 74084.7 | 25748.61 | 1828 | 1455 2025-11 | 328682.7 | 227293.96 | 236843.96 | 76404.99 | 26661.72 | 2550 | 2013 2025-12 | 332982.57 | 323026.37 | 322182.01 | 79290.12 | 74611.41 | 2994 | 2754 (36 rows)
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:
1. Revenue sources mostly reconcile — except invoices, which aren't a 5th source. (Monthly revenue by source; Invoice revenue composition) The four "revenue" figures diverge (Dec-25: orders €332,982 vs payments captured €323,026 vs payout gross €322,182 vs invoices €79,290), but invoices turned out to be 93-98% order-linked billing documents, not an independent figure — only the small, growing subscription-only slice (€15,128 in Dec-25) is genuinely incremental. Confident: invoices should not be added on top of order revenue in the board pack; use order revenue + the subscription-only sliver.
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH months AS ( SELECT DISTINCT month_start, month_label FROM calendar_days WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01'),orders_rev AS ( SELECT date_trunc('month', o.placed_at)::date AS month_start, ROUND(SUM(o.grand_total / er.rate), 2) AS orders_booked_revenue_eur, SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month_start, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS orders_booked_revenue_eur, COUNT(*) AS order_count FROM orders o LEFT 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.status <> 'cancelled' AND o.placed_at IS NOT NULL GROUP BY 1),payments_rev AS ( SELECT date_trunc('month', p.captured_at)::date AS month_start, ROUND(SUM(p.amount / er.rate), 2) AS payments_captured_eur, SELECT CAST(date_trunc('month', p.captured_at) AS date) AS month_start, SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))) AS payments_captured_eur, COUNT(*) AS payment_count FROM payments p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS DATE) WHERE p.status = 'captured' AND p.captured_at IS NOT NULL GROUP BY 1),payouts_rev AS ( SELECT date_trunc('month', po.paid_at)::date AS month_start, ROUND(SUM(po.gross_amount / er.rate), 2) AS payout_gross_eur SELECT CAST(date_trunc('month', po.paid_at) AS date) AS month_start, SUM(CAST(po.gross_amount / er.rate AS NUMERIC(18,6))) AS payout_gross_eur FROM payouts po LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = po.currency_code AND er.as_of = CAST(po.paid_at AS DATE) WHERE po.paid_at IS NOT NULL GROUP BY 1),invoices_rev AS ( SELECT date_trunc('month', i.paid_at)::date AS month_start, ROUND(SUM(i.total / er.rate), 2) AS invoice_paid_revenue_eur SELECT CAST(date_trunc('month', i.paid_at) AS date) AS month_start, SUM(CAST(i.total / er.rate AS NUMERIC(18,6))) AS invoice_paid_revenue_eur FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at IS NOT NULL GROUP BY 1),refunds_rev AS ( SELECT date_trunc('month', pr.processed_at)::date AS month_start, ROUND(SUM(pr.amount / er.rate), 2) AS refunds_eur SELECT CAST(date_trunc('month', pr.processed_at) AS date) AS month_start, SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur FROM payment_refunds pr LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS DATE) WHERE pr.status = 'succeeded' AND pr.processed_at IS NOT NULL GROUP BY 1)SELECT m.month_label AS month, COALESCE(orr.orders_booked_revenue_eur, 0) AS orders_booked_revenue_eur, COALESCE(pay.payments_captured_eur, 0) AS payments_captured_eur, COALESCE(pou.payout_gross_eur, 0) AS payout_gross_eur, COALESCE(inv.invoice_paid_revenue_eur, 0) AS invoice_paid_revenue_eur, COALESCE(ref.refunds_eur, 0) AS refunds_eur, COALESCE(orr.order_count, 0) AS order_count, COALESCE(pay.payment_count, 0) AS payment_countFROM months mLEFT JOIN orders_rev orr ON orr.month_start = m.month_startLEFT JOIN payments_rev pay ON pay.month_start = m.month_startLEFT JOIN payouts_rev pou ON pou.month_start = m.month_startLEFT JOIN invoices_rev inv ON inv.month_start = m.month_startLEFT JOIN refunds_rev ref ON ref.month_start = m.month_startORDER BY m.month_startORDER BY m.month_start NULLS LAST, m.month_label NULLS LAST
WITH months AS ( SELECT DISTINCT month_start, month_label FROM calendar_days WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01' ), orders_rev AS ( SELECT CAST(date_trunc('month', o.placed_at) AS date) AS month_start, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS orders_booked_revenue_eur, COUNT(*) AS order_count FROM orders o LEFT 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.status <> 'cancelled' AND o.placed_at IS NOT NULL GROUP BY 1 ), payments_rev AS ( SELECT CAST(date_trunc('month', p.captured_at) AS date) AS month_start, SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))) AS payments_captured_eur, COUNT(*) AS payment_count FROM payments p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS DATE) WHERE p.status = 'captured' AND p.captured_at IS NOT NULL GROUP BY 1 ), payouts_rev AS ( SELECT CAST(date_trunc('month', po.paid_at) AS date) AS month_start, SUM(CAST(po.gross_amount / er.rate AS NUMERIC(18,6))) AS payout_gross_eur FROM payouts po LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = po.currency_code AND er.as_of = CAST(po.paid_at AS DATE) WHERE po.paid_at IS NOT NULL GROUP BY 1 ), invoices_rev AS ( SELECT CAST(date_trunc('month', i.paid_at) AS date) AS month_start, SUM(CAST(i.total / er.rate AS NUMERIC(18,6))) AS invoice_paid_revenue_eur FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at IS NOT NULL GROUP BY 1 ), refunds_rev AS ( SELECT CAST(date_trunc('month', pr.processed_at) AS date) AS month_start, SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur FROM payment_refunds pr LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS DATE) WHERE pr.status = 'succeeded' AND pr.processed_at IS NOT NULL GROUP BY 1 ) SELECT m.month_label AS month, COALESCE(orr.orders_booked_revenue_eur, 0) AS orders_booked_revenue_eur, COALESCE(pay.payments_captured_eur, 0) AS payments_captured_eur, COALESCE(pou.payout_gross_eur, 0) AS payout_gross_eur, COALESCE(inv.invoice_paid_revenue_eur, 0) AS invoice_paid_revenue_eur, COALESCE(ref.refunds_eur, 0) AS refunds_eur, COALESCE(orr.order_count, 0) AS order_count, COALESCE(pay.payment_count, 0) AS payment_count FROM months m LEFT JOIN orders_rev orr ON orr.month_start = m.month_start LEFT JOIN payments_rev pay ON pay.month_start = m.month_start LEFT JOIN payouts_rev pou ON pou.month_start = m.month_start LEFT JOIN invoices_rev inv ON inv.month_start = m.month_start LEFT JOIN refunds_rev ref ON ref.month_start = m.month_start ORDER BY m.month_start NULLS LAST, m.month_label 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: Monthly revenue by source Question: If we ask what revenue was in a given month from four different places (orders booked, payments captured, payouts settled, invoices paid), do we get the same answer, and how far apart are they? Grain: calendar month, 2023-01 through 2025-12 Columns: - month - orders_booked_revenue_eur - payments_captured_eur - payout_gross_eur - invoice_paid_revenue_eur - refunds_eur - order_count - payment_count Details: Scope: all tenants combined, global totals. Time window: every calendar month from 2023-01 to 2025-12 (use calendar_days to ensure months with zero activity still appear). orders_booked_revenue_eur = sum of orders.grand_total for non-cancelled orders, grouped by month of placed_at, converted to EUR. payments_captured_eur = sum of payments.amount where status='captured', grouped by month of captured_at, converted to EUR. payout_gross_eur = sum of payouts.gross_amount, grouped by month of paid_at, converted to EUR. invoice_paid_revenue_eur = sum of invoices.total where status='paid', grouped by month of paid_at, converted to EUR. refunds_eur = sum of payment_refunds.amount where status='succeeded', grouped by month of processed_at, converted to EUR. order_count = count of non-cancelled orders placed that month. payment_count = count of captured payments that month. Currency conversion: for each row's native currency_code, convert to EUR using exchange_rates where base_code='EUR', quote_code=<currency>, as_of = the event's date (cast timestamp to date); EUR amount = local amount / rate. Tables: orders, payments, payouts, invoices, payment_refunds, exchange_rates. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH months AS ( SELECT DISTINCT month_start, month_label FROM calendar_days WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01' ), orders_rev AS ( SELECT date_trunc('month', o.placed_at)::date AS month_start, ROUND(SUM(o.grand_total / er.rate), 2) AS orders_booked_revenue_eur, COUNT(*) AS order_count FROM orders o LEFT 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.status <> 'cancelled' AND o.placed_at IS NOT NULL GROUP BY 1 ), payments_rev AS ( SELECT date_trunc('month', p.captured_at)::date AS month_start, ROUND(SUM(p.amount / er.rate), 2) AS payments_captured_eur, COUNT(*) AS payment_count FROM payments p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS DATE) WHERE p.status = 'captured' AND p.captured_at IS NOT NULL GROUP BY 1 ), payouts_rev AS ( SELECT date_trunc('month', po.paid_at)::date AS month_start, ROUND(SUM(po.gross_amount / er.rate), 2) AS payout_gross_eur FROM payouts po LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = po.currency_code AND er.as_of = CAST(po.paid_at AS DATE) WHERE po.paid_at IS NOT NULL GROUP BY 1 ), invoices_rev AS ( SELECT date_trunc('month', i.paid_at)::date AS month_start, ROUND(SUM(i.total / er.rate), 2) AS invoice_paid_revenue_eur FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at IS NOT NULL GROUP BY 1 ), refunds_rev AS ( SELECT date_trunc('month', pr.processed_at)::date AS month_start, ROUND(SUM(pr.amount / er.rate), 2) AS refunds_eur FROM payment_refunds pr LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS DATE) WHERE pr.status = 'succeeded' AND pr.processed_at IS NOT NULL GROUP BY 1 ) SELECT m.month_label AS month, COALESCE(orr.orders_booked_revenue_eur, 0) AS orders_booked_revenue_eur, COALESCE(pay.payments_captured_eur, 0) AS payments_captured_eur, COALESCE(pou.payout_gross_eur, 0) AS payout_gross_eur, COALESCE(inv.invoice_paid_revenue_eur, 0) AS invoice_paid_revenue_eur, COALESCE(ref.refunds_eur, 0) AS refunds_eur, COALESCE(orr.order_count, 0) AS order_count, COALESCE(pay.payment_count, 0) AS payment_count FROM months m LEFT JOIN orders_rev orr ON orr.month_start = m.month_start LEFT JOIN payments_rev pay ON pay.month_start = m.month_start LEFT JOIN payouts_rev pou ON pou.month_start = m.month_start LEFT JOIN invoices_rev inv ON inv.month_start = m.month_start LEFT JOIN refunds_rev ref ON ref.month_start = m.month_start ORDER BY m.month_start
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, orders_booked_revenue_eur, payments_captured_eur, payout_gross_eur, invoice_paid_revenue_eur, refunds_eur, order_count, payment_count
submitWITH months AS ( SELECT DISTINCT month_start, month_label FROM calendar_days WHERE month_start BETWEEN DATE '2023-01-01' AND DATE '2025-12-01' ), orders_rev AS ( SELECT date_trunc('month', o.placed_at)::date AS month_start, ROUND(SUM(o.grand_total / er.rate), 2) AS orders_booked_revenue_eur, COUNT(*) AS order_count FROM orders o LEFT 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.status <> 'cancelled' AND o.placed_at IS NOT NULL GROUP BY 1 ), payments_rev AS ( SELECT date_trunc('month', p.captured_at)::date AS month_start, ROUND(SUM(p.amount / er.rate), 2) AS payments_captured_eur, COUNT(*) AS payment_count FROM payments p LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS DATE) WHERE p.status = 'captured' AND p.captured_at IS NOT NULL GROUP BY 1 ), payouts_rev AS ( SELECT date_trunc('month', po.paid_at)::date AS month_start, ROUND(SUM(po.gross_amount / er.rate), 2) AS payout_gross_eur FROM payouts po LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = po.currency_code AND er.as_of = CAST(po.paid_at AS DATE) WHERE po.paid_at IS NOT NULL GROUP BY 1 ), invoices_rev AS ( SELECT date_trunc('month', i.paid_at)::date AS month_start, ROUND(SUM(i.total / er.rate), 2) AS invoice_paid_revenue_eur FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at IS NOT NULL GROUP BY 1 ), refunds_rev AS ( SELECT date_trunc('month', pr.processed_at)::date AS month_start, ROUND(SUM(pr.amount / er.rate), 2) AS refunds_eur FROM payment_refunds pr LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS DATE) WHERE pr.status = 'succeeded' AND pr.processed_at IS NOT NULL GROUP BY 1 ) SELECT m.month_label AS month, COALESCE(orr.orders_booked_revenue_eur, 0) AS orders_booked_revenue_eur, COALESCE(pay.payments_captured_eur, 0) AS payments_captured_eur, COALESCE(pou.payout_gross_eur, 0) AS payout_gross_eur, COALESCE(inv.invoice_paid_revenue_eur, 0) AS invoice_paid_revenue_eur, COALESCE(ref.refunds_eur, 0) AS refunds_eur, COALESCE(orr.order_count, 0) AS order_count, COALESCE(pay.payment_count, 0) AS payment_count FROM months m LEFT JOIN orders_rev orr ON orr.month_start = m.month_start LEFT JOIN payments_rev pay ON pay.month_start = m.month_start LEFT JOIN payouts_rev pou ON pou.month_start = m.month_start LEFT JOIN invoices_rev inv ON inv.month_start = m.month_start LEFT JOIN refunds_rev ref ON ref.month_start = m.month_start ORDER BY m.month_start
Submitted to tonight's batch. Your work on this request is done.