Queries · q229 · Chief Financial Officer · week 2, night 4
“Once VAT/sales-tax is included on the ledger side (not just net-of-tax revenue), does the ledger's gross billed amount tie to the payments table's net captured amount each quarter?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2
The external auditors start the 2025 year-end audit in two weeks. They will want to see that the money adds up across the whole chain: what shoppers paid, what was refunded or credited, what merchants received in payouts, what Tidewick kept, and that invoices agree with all of it. They will also trace sample transactions end to end.
The manager, at the start of week 2
Subject: Good progress — but the audit clock is now the priority
Kofi,
Thanks — this is useful, and I appreciate you flagging the caveats rather than smoothing them over. A few reactions and then where I want to focus next week.
What stood out:
The big new driver: external audit starts in two weeks. They'll want to walk the full money trail — what a shopper paid, what was refunded or credited back, what the merchant was paid out, what we kept as our fee, and that it all agrees with the invoices and accounts — and they'll pick sample transactions and trace them start to finish. I'd rather we got ahead of that than react to it.
Questions for this week, in priority order:
Audit readiness (#1) is the one I most need — please make that the lead request even if it means FX and B2B receivables slip another week.
Thanks, Tobias
The analyst · week 2, night 4, request 1 of 3
Report 1 from last night looked alarming at first — ledger net revenue running 13.7-15.9% below payments net amount every quarter — but I think I made the same class of mistake as Night 2: "Sales revenue"/"Shipping income" ledger accounts are booked net of VAT (the VAT portion is credited separately to "VAT / sales tax payable"), while my "payments_net_amount_eur" was the VAT-inclusive gross-minus-refund figure. Spot-checking the Night 2 ledger detail confirms it: Ampfeld Elektronik #1727's VAT credit (2,653.61) is 15.97% of its gross payment (16,619.99) — right in the middle of the quarterly gaps I'm seeing. So this is very likely just tax-inclusive vs tax-exclusive revenue, not money going missing, but I want to prove it precisely before I tell Tobias it's resolved, since "the ledger is €200k short this quarter" is exactly the kind of number an auditor would seize on if I handed it over unexplained.
Report 1's payment-method breakdown is the one genuine, bounded item left: bank_transfer payments are only 19 of the 249 "other mismatch" payments by count, but carry 40% of the mismatch euros with an average gap of €898 (vs ~€18-33 for card/wallet/credit) — real money, concentrated in a small, nameable list. Report 3 is a clean, reassuring finding: the no-ledger-entries gap was worst in January (2.17%, €1,241) and has fallen to near-zero by October, with only a small December blip plausibly tied to holiday volume — looks like a fixed/fading issue, not an active control failure.
Tonight (last night before the memo): (1) redo the revenue reconciliation correctly by including VAT in the ledger-side total, to get the real tie-out number; (2) pull the actual 19 bank-transfer mismatched payments as a named, handoff-ready list; (3) check whether bank_transfer payments are systemically more mismatch-prone as a channel (vs. a handful of one-off accidents) by looking at all 2025 bank_transfer payments, not just the flagged ones.
The engineer · 2 dry runs on the empty schema, then the submission
WITH quarters AS ( SELECT generate_series(1,4) AS q ), ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' ) ), ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date ), ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q ), payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded') ), payments_eur AS ( SELECT pf.q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date ), payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q ) SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pct FROM quarters LEFT JOIN ledger_quarterly lq ON lq.q = quarters.q LEFT JOIN payments_quarterly pq ON pq.q = quarters.q ORDER BY quarters.q
WITH quarters AS ( SELECT generate_series(1,4) AS q SELECT * FROM (VALUES (1),(2),(3),(4)) AS v(q)),ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' )),ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date),ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q),payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded')),payments_eur AS ( SELECT pf.q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date),payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q)SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pctFROM quartersLEFT JOIN ledger_quarterly lq ON lq.q = quarters.qLEFT JOIN payments_quarterly pq ON pq.q = quarters.qORDER BY quarters.q
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report computes, per 2025 quarter:
- ledger_gross_billed_eur: credits to revenue or VAT/sales-tax-payable accounts minus debits to contra_revenue or VAT/sales-tax-payable accounts, each row converted to EUR using the exchange rate as of that ledger entry's own occurred_at date.
- payments_net_amount_eur: amount - amount_refunded for payments with status captured/refunded/partially_refunded, converted to EUR using the rate as of captured_at.
- diff_eur and diff_pct (ledger minus payments, as a percent of payments; null if payments total is zero for a quarter).
Rows are ordered 2025-Q1 → 2025-Q4 via an explicit quarter spine so all four quarters print even if one side has no activity.”
As Kofi Mensah saw it, from the small dataset the agents worked on
quarter | ledger_gross_billed_eur | payments_net_amount_eur | diff_eur | diff_pct --------+-------------------------+-------------------------+-----------+--------- 2025-Q1 | 1224288.48 | 1249571.12 | -25282.64 | -2.02 2025-Q2 | 1281589.56 | 1279430.77 | 2158.79 | 0.17 2025-Q3 | 1070758.66 | 1065326.23 | 5432.43 | 0.51 2025-Q4 | 1795895.17 | 1824173.25 | -28278.07 | -1.55 (4 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH quarters AS ( SELECT * FROM (VALUES (1),(2),(3),(4)) AS v(q)),ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, je.id AS id, je.direction AS direction, je.amount AS amount, je.currency_code AS currency_code, je.occurred_at AS occurred_at, la.account_type AS account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' )),ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, lf.id AS id, lf.direction AS direction, lf.amount AS amount, lf.currency_code AS currency_code, lf.account_type AS account_type, lf.account_name AS account_name, CAST(EXTRACT(QUARTER FROM lf.occurred_at) AS int) AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur ELSE CAST(lf.amount / er.rate AS NUMERIC(18,6)) END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date AND er.as_of = CAST(lf.occurred_at AS date)),ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q),payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q p.id AS id, p.amount AS amount, p.amount_refunded AS amount_refunded, p.currency_code AS currency_code, p.captured_at AS captured_at, CAST(EXTRACT(QUARTER FROM p.captured_at) AS int) AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded')),payments_eur AS ( SELECT pf.q, pf.q AS q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur ELSE CAST((pf.amount - pf.amount_refunded) / er.rate AS NUMERIC(18,6)) END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date AND er.as_of = CAST(pf.captured_at AS date)),payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q)SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pct COALESCE(lq.ledger_gross_billed_eur,0) AS ledger_gross_billed_eur, COALESCE(pq.payments_net_amount_eur,0) AS payments_net_amount_eur, COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0) AS diff_eur, CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END AS diff_pctFROM quartersLEFT JOIN ledger_quarterly lq ON lq.q = quarters.qLEFT JOIN payments_quarterly pq ON pq.q = quarters.qORDER BY quarters.qORDER BY quarters.q NULLS LAST
WITH quarters AS ( SELECT * FROM (VALUES (1),(2),(3),(4)) AS v(q) ), ledger_filtered AS ( SELECT je.id AS id, je.direction AS direction, je.amount AS amount, je.currency_code AS currency_code, je.occurred_at AS occurred_at, la.account_type AS account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' ) ), ledger_eur AS ( SELECT lf.id AS id, lf.direction AS direction, lf.amount AS amount, lf.currency_code AS currency_code, lf.account_type AS account_type, lf.account_name AS account_name, CAST(EXTRACT(QUARTER FROM lf.occurred_at) AS int) AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE CAST(lf.amount / er.rate AS NUMERIC(18,6)) END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = CAST(lf.occurred_at AS date) ), ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q ), payments_filtered AS ( SELECT p.id AS id, p.amount AS amount, p.amount_refunded AS amount_refunded, p.currency_code AS currency_code, p.captured_at AS captured_at, CAST(EXTRACT(QUARTER FROM p.captured_at) AS int) AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded') ), payments_eur AS ( SELECT pf.q AS q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE CAST((pf.amount - pf.amount_refunded) / er.rate AS NUMERIC(18,6)) END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = CAST(pf.captured_at AS date) ), payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q ) SELECT '2025-Q' || quarters.q AS quarter, COALESCE(lq.ledger_gross_billed_eur,0) AS ledger_gross_billed_eur, COALESCE(pq.payments_net_amount_eur,0) AS payments_net_amount_eur, COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0) AS diff_eur, CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END AS diff_pct FROM quarters LEFT JOIN ledger_quarterly lq ON lq.q = quarters.q LEFT JOIN payments_quarterly pq ON pq.q = quarters.q ORDER BY quarters.q 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 Kofi Mensah (analyst for the Chief Financial Officer): Title: 2025 Revenue Reconciliation via Ledger Accounts, Including VAT, by Quarter Question: Once VAT/sales-tax is included on the ledger side (not just net-of-tax revenue), does the ledger's gross billed amount tie to the payments table's net captured amount each quarter? Grain: one calendar quarter, 2025 Columns: - quarter - ledger_gross_billed_eur (sum of credit amounts where ledger_accounts.account_type='revenue' OR ledger_accounts.name='VAT / sales tax payable', minus sum of debit amounts where ledger_accounts.account_type='contra_revenue' OR ledger_accounts.name='VAT / sales tax payable', all for ledger_entries.occurred_at in the quarter) - payments_net_amount_eur (sum of payments.amount - payments.amount_refunded, captured_at in the quarter, status captured/refunded/partially_refunded) - diff_eur - diff_pct Details: Same method as last night's revenue reconciliation report (EUR conversion per-row via exchange_rates on the ledger entry's own occurred_at/currency_code for the ledger side, and on captured_at/currency_code for the payments side), but this time include the 'VAT / sales tax payable' ledger account's credits (at capture) and debits (at refund reversal) in the ledger-side total alongside 'revenue'-type accounts and 'contra_revenue'-type debits, so the ledger total represents the full gross-billed-minus-refunded amount rather than tax-exclusive revenue. Group by calendar quarter of occurred_at (ledger side) / captured_at (payments side). Order chronologically 2025-Q1 to 2025-Q4 (4 rows). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH quarters AS ( SELECT generate_series(1,4) AS q ), ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' ) ), ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date ), ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q ), payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded') ), payments_eur AS ( SELECT pf.q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date ), payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q ) SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pct FROM quarters LEFT JOIN ledger_quarterly lq ON lq.q = quarters.q LEFT JOIN payments_quarterly pq ON pq.q = quarters.q ORDER BY quarters.q
STATUS: REJECTED - does not compile: Catalog Error: Function with name generate_series does not exist!
dry_runWITH quarters AS ( SELECT * FROM (VALUES (1),(2),(3),(4)) AS v(q) ), ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' ) ), ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date ), ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q ), payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded') ), payments_eur AS ( SELECT pf.q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date ), payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q ) SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pct FROM quarters LEFT JOIN ledger_quarterly lq ON lq.q = quarters.q LEFT JOIN payments_quarterly pq ON pq.q = quarters.q ORDER BY quarters.q
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: quarter, ledger_gross_billed_eur, payments_net_amount_eur, diff_eur, diff_pct
submitWITH quarters AS ( SELECT * FROM (VALUES (1),(2),(3),(4)) AS v(q) ), ledger_filtered AS ( SELECT je.id, je.direction, je.amount, je.currency_code, je.occurred_at, la.account_type, la.name AS account_name FROM ledger_entries je JOIN ledger_accounts la ON la.id = je.ledger_account_id WHERE je.occurred_at >= DATE '2025-01-01' AND je.occurred_at < DATE '2026-01-01' AND ( la.account_type IN ('revenue','contra_revenue') OR la.name = 'VAT / sales tax payable' ) ), ledger_eur AS ( SELECT lf.id, lf.direction, lf.amount, lf.currency_code, lf.account_type, lf.account_name, EXTRACT(QUARTER FROM lf.occurred_at)::int AS q, CASE WHEN lf.currency_code = 'EUR' THEN lf.amount ELSE lf.amount / er.rate END AS amount_eur FROM ledger_filtered lf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lf.currency_code AND er.as_of = lf.occurred_at::date ), ledger_quarterly AS ( SELECT q, SUM(CASE WHEN direction = 'credit' AND (account_type = 'revenue' OR account_name = 'VAT / sales tax payable') THEN amount_eur WHEN direction = 'debit' AND (account_type = 'contra_revenue' OR account_name = 'VAT / sales tax payable') THEN -amount_eur ELSE 0 END) AS ledger_gross_billed_eur FROM ledger_eur GROUP BY q ), payments_filtered AS ( SELECT p.id, p.amount, p.amount_refunded, p.currency_code, p.captured_at, EXTRACT(QUARTER FROM p.captured_at)::int AS q FROM payments p WHERE p.captured_at >= DATE '2025-01-01' AND p.captured_at < DATE '2026-01-01' AND p.status IN ('captured','refunded','partially_refunded') ), payments_eur AS ( SELECT pf.q, CASE WHEN pf.currency_code = 'EUR' THEN (pf.amount - pf.amount_refunded) ELSE (pf.amount - pf.amount_refunded) / er.rate END AS net_amount_eur FROM payments_filtered pf LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pf.currency_code AND er.as_of = pf.captured_at::date ), payments_quarterly AS ( SELECT q, SUM(net_amount_eur) AS payments_net_amount_eur FROM payments_eur GROUP BY q ) SELECT '2025-Q' || quarters.q AS quarter, ROUND(COALESCE(lq.ledger_gross_billed_eur,0), 2) AS ledger_gross_billed_eur, ROUND(COALESCE(pq.payments_net_amount_eur,0), 2) AS payments_net_amount_eur, ROUND(COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0), 2) AS diff_eur, ROUND( CASE WHEN COALESCE(pq.payments_net_amount_eur,0) = 0 THEN NULL ELSE (COALESCE(lq.ledger_gross_billed_eur,0) - COALESCE(pq.payments_net_amount_eur,0)) / pq.payments_net_amount_eur * 100 END, 2 ) AS diff_pct FROM quarters LEFT JOIN ledger_quarterly lq ON lq.q = quarters.q LEFT JOIN payments_quarterly pq ON pq.q = quarters.q ORDER BY quarters.q
Submitted to tonight's batch. Your work on this request is done.