Queries · q227 · Chief Financial Officer · week 2, night 3

2025 Revenue Reconciliation via Correct Ledger Accounts, by Quarter

“Using the right ledger accounts (revenue credits net of refund contra-revenue debits), does the ledger's net revenue tie to the payments table's net captured amount each quarter?”

Manager · asked the questionTobias LindqvistChief Financial Officer. New this month, previously finance director at a retailer. Preparing the 2025 annual accounts and the 2026 budget.

Analyst · planned the reportKofi MensahHypothesis-driven: states what he expects and designs reports that would prove him wrong.

Engineer · wrote the SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

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 Chief Financial Officer's desk
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.
One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events
Tobias Lindqvist writes to Kofi Mensah
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 correction on the revenue report (off by roughly half, now fixed and cross-checked) is reassuring that you caught it, but it also makes me want a second pair of eyes before anything like this goes near the auditors.
- The refund numbers disagreeing by 16–40% depending on which source we use is the kind of thing an auditor will ask about directly. I don't want to walk in with two different refund totals and no explanation.
- Concentration easing as we add merchants is good context for the budget conversation — no urgent follow-up needed there this week.
- I also saw the notes from pricing and risk this week — the free-delivery giveaway and the refund pattern on that welcome discount are small in isolation, but they're exactly the kind of "where did the money go" questions auditors and I will both ask, so I'd like a rough sense of scale rather than letting them sit as separate anecdotes.
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:
1. Can we demonstrate, for a sample of transactions, that the money actually adds up end to end — shopper payment through refund/credit, payout to the merchant, and our retained fee — so we have something ready to hand the auditors rather than assembling it under time pressure?
2. Which refund figure should I trust for the 2025 accounts, and why do the two methods disagree? I don't need the full reconciliation solved, but I need to understand the likely cause well enough to explain it credibly if asked.
3. Where do things stand on getting our own plan-fee revenue into the picture, even approximately, so I'm not presenting half of 2025 revenue as the whole story?
4. If there's room: roughly how much revenue or margin might be leaking through the control gaps your peers flagged this week (free deliveries to canceled customers, uncapped coupon reuse) — just enough for me to judge whether it's noise or something that belongs in front of the auditors too.
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
Kofi Mensah plans the night
The analyst · week 2, night 3, request 2 of 3
Report 1 (ledger detail) actually resolves last night's big worry in Tidewick's favor: the "credit = payment + refund" pattern is correct double-entry accounting, not double-counted revenue. On capture: Sales revenue (credit) + VAT payable (credit) = Payment gateway clearing (debit). On refund: Refunds and returns/contra-revenue (debit) + VAT payable (debit) = Payment gateway clearing (credit) — e.g. Ampfeld Elektronik #1727 and every other refunded sample shows this exact structure, balanced to the cent. My "ledger_credit_total" diagnostic from Night 2 was simply summing credits across unrelated accounts (revenue credits at capture + clearing credits at refund), which is apples-to-oranges — the ledger itself is fine. Report 2's scale-up confirms this: 78.55% + 18.74% ≈ 97.3% of 2025's captured payments follow one of these two clean, explainable patterns. That's good news I can tell Tobias with confidence.

Two smaller things still need a next step before I call #1 fully done: (a) "other mismatch" is 249 payments / €84,443 in payment value (2.1% of count) — Castell Fasteners' SO-100233 in the detail report hints these may be non-gateway payment methods (bank transfer) where VAT/revenue nets differently than my simple gross/net comparison expected, not a real error, but I want to confirm that before saying so; (b) "no ledger entries" is small in money (€3,380 across 67 payments, 0.58%) but is a genuine audit-trail gap spread across 31 merchants — worth knowing whether it's an old, fixed bug or still happening now, since "we found gaps but can't tell if they're ongoing" is a weak answer to give auditors.

Tonight's three slots: characterize the "other mismatch" bucket by payment method (test the bank-transfer/B2B hypothesis); redo the revenue reconciliation using the correct ledger accounts (Sales/Shipping revenue credits minus Refunds-and-returns debits, compared to payments' net amount) so I have a trustworthy "yes, it ties out" statement built the right way instead of my flawed Night 2 shortcut; and trend the no-ledger-entries gap by month to see if it's historical or still occurring.
2025 Revenue Reconciliation via Correct Ledger Accounts, by Quarter
Question
Using the right ledger accounts (revenue credits net of refund contra-revenue debits), does the ledger's net revenue tie to the payments table's net captured amount each quarter?
One row per
one calendar quarter, 2025
Columns
- quarter
- ledger_revenue_credit_eur (sum of ledger_entries.amount, direction='credit', for ledger_accounts.account_type='revenue', occurred_at in the quarter)
- ledger_contra_revenue_debit_eur (same but account_type='contra_revenue', direction='debit')
- ledger_net_revenue_eur (credit minus debit)
- payments_net_amount_eur (sum of payments.amount - payments.amount_refunded for payments captured_at in the quarter, status captured/refunded/partially_refunded)
- diff_eur
- diff_pct

Viktor writes the SQL

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

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

WITH ledger_q AS (
  SELECT
    EXTRACT(YEAR FROM le.occurred_at)::int AS yr,
    EXTRACT(QUARTER FROM le.occurred_at)::int AS qtr,
    SUM(CASE
          WHEN la.account_type = 'revenue' AND le.direction = 'credit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_revenue_credit_eur,
    SUM(CASE
          WHEN la.account_type = 'contra_revenue' AND le.direction = 'debit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_contra_revenue_debit_eur
  FROM ledger_entries le
  JOIN ledger_accounts la ON la.id = le.ledger_account_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = le.currency_code
   AND er.as_of = date(le.occurred_at)
  WHERE le.occurred_at >= DATE '2025-01-01'
    AND le.occurred_at < DATE '2026-01-01'
    AND la.account_type IN ('revenue', 'contra_revenue')
  GROUP BY 1, 2
),
payments_q AS (
  SELECT
    EXTRACT(YEAR FROM p.captured_at)::int AS yr,
    EXTRACT(QUARTER FROM p.captured_at)::int AS qtr,
    SUM(CASE
          WHEN p.currency_code = 'EUR' THEN (p.amount - p.amount_refunded)
          ELSE (p.amount - p.amount_refunded) / er2.rate
        END) AS payments_net_amount_eur
  FROM payments p
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = p.currency_code
   AND er2.as_of = date(p.captured_at)
  WHERE p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
    AND p.status IN ('captured', 'refunded', 'partially_refunded')
  GROUP BY 1, 2
)
SELECT
  l.yr || '-Q' || l.qtr AS quarter,
  ROUND(l.ledger_revenue_credit_eur, 2) AS ledger_revenue_credit_eur,
  ROUND(l.ledger_contra_revenue_debit_eur, 2) AS ledger_contra_revenue_debit_eur,
  ROUND(l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur, 2) AS ledger_net_revenue_eur,
  ROUND(p.payments_net_amount_eur, 2) AS payments_net_amount_eur,
  ROUND((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur, 2) AS diff_eur,
  ROUND(
    ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)
    / NULLIF(p.payments_net_amount_eur, 0) * 100,
    2
  ) AS diff_pct
FROM ledger_q l
JOIN payments_q p ON p.yr = l.yr AND p.qtr = l.qtr
ORDER BY l.yr, l.qtr

“Query compiles and returns 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. The report computes, for each 2025 calendar quarter, the ledger's net revenue (revenue credits minus contra-revenue debits, both converted to EUR) against payments' net captured amount (also EUR), along with the absolute and percentage difference between the two — exactly as Kofi specified.”

The report comes back overnight

As Kofi Mensah saw it, from the small dataset the agents worked on

quarter | ledger_revenue_credit_eur | ledger_contra_revenue_debit_eur | ledger_net_revenue_eur | payments_net_amount_eur |   diff_eur | diff_pct
--------+---------------------------+---------------------------------+------------------------+-------------------------+------------+---------
2025-Q1 |                1153838.92 |                       102694.04 |             1051144.88 |              1249571.12 | -198426.24 |   -15.88
2025-Q2 |                1210594.71 |                       107349.94 |             1103244.77 |              1279430.77 |  -176186.0 |   -13.77
2025-Q3 |                1007488.29 |                        88413.91 |              919074.38 |              1065326.23 | -146251.85 |   -13.73
2025-Q4 |                1744185.92 |                       194388.87 |             1549797.05 |              1824173.25 |  -274376.2 |   -15.04
(4 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q2292025 Revenue Reconciliation via Ledger Accounts, Including VAT, by Quarter
It builds on:
- q223Ledger Line-Item Detail for Flagged Sample Payments, 2025
- q224Ledger-to-Payment Reconciliation Pattern at Scale, 2025

It becomes benchmark query q227

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

spellingcast (4×)
spellingdate cast (2×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)

The benchmark text, against the submitted SQL

WITH ledger_q AS (  SELECT    EXTRACT(YEAR FROM le.occurred_at)::int AS yr,    EXTRACT(QUARTER FROM le.occurred_at)::int AS qtr,    CAST(EXTRACT(YEAR FROM le.occurred_at) AS int) AS yr,    CAST(EXTRACT(QUARTER FROM le.occurred_at) AS int) AS qtr,    SUM(CASE          WHEN la.account_type = 'revenue' AND le.direction = 'credit'          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE CAST(le.amount / er.rate AS NUMERIC(18,6)) END)          ELSE 0        END) AS ledger_revenue_credit_eur,    SUM(CASE          WHEN la.account_type = 'contra_revenue' AND le.direction = 'debit'          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE CAST(le.amount / er.rate AS NUMERIC(18,6)) END)          ELSE 0        END) AS ledger_contra_revenue_debit_eur  FROM ledger_entries le  JOIN ledger_accounts la ON la.id = le.ledger_account_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = le.currency_code   AND er.as_of = date(le.occurred_at)   AND er.as_of = CAST(le.occurred_at AS DATE)  WHERE le.occurred_at >= DATE '2025-01-01'    AND le.occurred_at < DATE '2026-01-01'    AND la.account_type IN ('revenue', 'contra_revenue')  GROUP BY 1, 2),payments_q AS (  SELECT    EXTRACT(YEAR FROM p.captured_at)::int AS yr,    EXTRACT(QUARTER FROM p.captured_at)::int AS qtr,    CAST(EXTRACT(YEAR FROM p.captured_at) AS int) AS yr,    CAST(EXTRACT(QUARTER FROM p.captured_at) AS int) AS qtr,    SUM(CASE          WHEN p.currency_code = 'EUR' THEN (p.amount - p.amount_refunded)          ELSE (p.amount - p.amount_refunded) / er2.rate          ELSE CAST((p.amount - p.amount_refunded) / er2.rate AS NUMERIC(18,6))        END) AS payments_net_amount_eur  FROM payments p  LEFT JOIN exchange_rates er2    ON er2.base_code = 'EUR'   AND er2.quote_code = p.currency_code   AND er2.as_of = date(p.captured_at)   AND er2.as_of = CAST(p.captured_at AS DATE)  WHERE p.captured_at >= DATE '2025-01-01'    AND p.captured_at < DATE '2026-01-01'    AND p.status IN ('captured', 'refunded', 'partially_refunded')  GROUP BY 1, 2)SELECT  l.yr || '-Q' || l.qtr AS quarter,  ROUND(l.ledger_revenue_credit_eur, 2) AS ledger_revenue_credit_eur,  ROUND(l.ledger_contra_revenue_debit_eur, 2) AS ledger_contra_revenue_debit_eur,  ROUND(l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur, 2) AS ledger_net_revenue_eur,  ROUND(p.payments_net_amount_eur, 2) AS payments_net_amount_eur,  ROUND((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur, 2) AS diff_eur,  ROUND(    ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)    / NULLIF(p.payments_net_amount_eur, 0) * 100,    2  ) AS diff_pct  l.ledger_revenue_credit_eur AS ledger_revenue_credit_eur,  l.ledger_contra_revenue_debit_eur AS ledger_contra_revenue_debit_eur,  l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur AS ledger_net_revenue_eur,  p.payments_net_amount_eur AS payments_net_amount_eur,  (l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur AS diff_eur,  ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)  / NULLIF(p.payments_net_amount_eur, 0) * 100 AS diff_pctFROM ledger_q lJOIN payments_q p ON p.yr = l.yr AND p.qtr = l.qtrORDER BY l.yr, l.qtrORDER BY l.yr NULLS LAST, l.qtr NULLS LAST

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

WITH ledger_q AS (
  SELECT
    CAST(EXTRACT(YEAR FROM le.occurred_at) AS int) AS yr,
    CAST(EXTRACT(QUARTER FROM le.occurred_at) AS int) AS qtr,
    SUM(CASE
          WHEN la.account_type = 'revenue' AND le.direction = 'credit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE CAST(le.amount / er.rate AS NUMERIC(18,6)) END)
          ELSE 0
        END) AS ledger_revenue_credit_eur,
    SUM(CASE
          WHEN la.account_type = 'contra_revenue' AND le.direction = 'debit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE CAST(le.amount / er.rate AS NUMERIC(18,6)) END)
          ELSE 0
        END) AS ledger_contra_revenue_debit_eur
  FROM ledger_entries le
  JOIN ledger_accounts la ON la.id = le.ledger_account_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = le.currency_code
   AND er.as_of = CAST(le.occurred_at AS DATE)
  WHERE le.occurred_at >= DATE '2025-01-01'
    AND le.occurred_at < DATE '2026-01-01'
    AND la.account_type IN ('revenue', 'contra_revenue')
  GROUP BY 1, 2
),
payments_q AS (
  SELECT
    CAST(EXTRACT(YEAR FROM p.captured_at) AS int) AS yr,
    CAST(EXTRACT(QUARTER FROM p.captured_at) AS int) AS qtr,
    SUM(CASE
          WHEN p.currency_code = 'EUR' THEN (p.amount - p.amount_refunded)
          ELSE CAST((p.amount - p.amount_refunded) / er2.rate AS NUMERIC(18,6))
        END) AS payments_net_amount_eur
  FROM payments p
  LEFT 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.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
    AND p.status IN ('captured', 'refunded', 'partially_refunded')
  GROUP BY 1, 2
)
SELECT
  l.yr || '-Q' || l.qtr AS quarter,
  l.ledger_revenue_credit_eur AS ledger_revenue_credit_eur,
  l.ledger_contra_revenue_debit_eur AS ledger_contra_revenue_debit_eur,
  l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur AS ledger_net_revenue_eur,
  p.payments_net_amount_eur AS payments_net_amount_eur,
  (l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur AS diff_eur,
  ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)
  / NULLIF(p.payments_net_amount_eur, 0) * 100 AS diff_pct
FROM ledger_q l
JOIN payments_q p ON p.yr = l.yr AND p.qtr = l.qtr
ORDER BY l.yr NULLS LAST, l.qtr NULLS LAST

Firebolt{ "errors": [ { "description": "Line 7, Column 87: 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": "ab186dc1-b761-4d82-b585-245e9c41d82d", "query_labe
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.amount Decimal(14, 4) Decimal64(size = 0), __table4.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

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

Viktor's whole session, 7 turns

The request, as Viktor received it

Report request from Kofi Mensah (analyst for the Chief Financial Officer):

Title: 2025 Revenue Reconciliation via Correct Ledger Accounts, by Quarter
Question: Using the right ledger accounts (revenue credits net of refund contra-revenue debits), does the ledger's net revenue tie to the payments table's net captured amount each quarter?
Grain: one calendar quarter, 2025
Columns:
- quarter
- ledger_revenue_credit_eur (sum of ledger_entries.amount, direction='credit', for ledger_accounts.account_type='revenue', occurred_at in the quarter)
- ledger_contra_revenue_debit_eur (same but account_type='contra_revenue', direction='debit')
- ledger_net_revenue_eur (credit minus debit)
- payments_net_amount_eur (sum of payments.amount - payments.amount_refunded for payments captured_at in the quarter, status captured/refunded/partially_refunded)
- diff_eur
- diff_pct
Details: Source: ledger_entries joined to ledger_accounts for account_type, filtered to occurred_at in calendar year 2025, converting each ledger_entries.amount to EUR via exchange_rates (base_code='EUR', quote_code=ledger_entries.currency_code, as_of=date(occurred_at)). Separately, payments filtered to captured_at in 2025 with the usual status filter, converting amount and amount_refunded to EUR via exchange_rates on captured_at. Group both sides by calendar quarter and join on quarter. diff_eur = ledger_net_revenue_eur - payments_net_amount_eur; diff_pct = diff_eur / payments_net_amount_eur * 100. 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.

Viktor calls dry_run

WITH ledger_q AS (
  SELECT
    EXTRACT(YEAR FROM le.occurred_at)::int AS yr,
    EXTRACT(QUARTER FROM le.occurred_at)::int AS qtr,
    SUM(CASE
          WHEN la.account_type = 'revenue' AND le.direction = 'credit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_revenue_credit_eur,
    SUM(CASE
          WHEN la.account_type = 'contra_revenue' AND le.direction = 'debit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_contra_revenue_debit_eur
  FROM ledger_entries le
  JOIN ledger_accounts la ON la.id = le.ledger_account_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = le.currency_code
   AND er.as_of = date(le.occurred_at)
  WHERE le.occurred_at >= DATE '2025-01-01'
    AND le.occurred_at < DATE '2026-01-01'
    AND la.account_type IN ('revenue', 'contra_revenue')
  GROUP BY 1, 2
),
payments_q AS (
  SELECT
    EXTRACT(YEAR FROM p.captured_at)::int AS yr,
    EXTRACT(QUARTER FROM p.captured_at)::int AS qtr,
    SUM(CASE
          WHEN p.currency_code = 'EUR' THEN (p.amount - p.amount_refunded)
          ELSE (p.amount - p.amount_refunded) / er2.rate
        END) AS payments_net_amount_eur
  FROM payments p
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = p.currency_code
   AND er2.as_of = date(p.captured_at)
  WHERE p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
    AND p.status IN ('captured', 'refunded', 'partially_refunded')
  GROUP BY 1, 2
)
SELECT
  l.yr || '-Q' || l.qtr AS quarter,
  ROUND(l.ledger_revenue_credit_eur, 2) AS ledger_revenue_credit_eur,
  ROUND(l.ledger_contra_revenue_debit_eur, 2) AS ledger_contra_revenue_debit_eur,
  ROUND(l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur, 2) AS ledger_net_revenue_eur,
  ROUND(p.payments_net_amount_eur, 2) AS payments_net_amount_eur,
  ROUND((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur, 2) AS diff_eur,
  ROUND(
    ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)
    / NULLIF(p.payments_net_amount_eur, 0) * 100,
    2
  ) AS diff_pct
FROM ledger_q l
JOIN payments_q p ON p.yr = l.yr AND p.qtr = l.qtr
ORDER BY l.yr, l.qtr

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: quarter, ledger_revenue_credit_eur, ledger_contra_revenue_debit_eur, ledger_net_revenue_eur, payments_net_amount_eur, diff_eur, diff_pct

ViktorQuery compiles and returns the requested columns. Submitting.

Viktor calls submit

WITH ledger_q AS (
  SELECT
    EXTRACT(YEAR FROM le.occurred_at)::int AS yr,
    EXTRACT(QUARTER FROM le.occurred_at)::int AS qtr,
    SUM(CASE
          WHEN la.account_type = 'revenue' AND le.direction = 'credit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_revenue_credit_eur,
    SUM(CASE
          WHEN la.account_type = 'contra_revenue' AND le.direction = 'debit'
          THEN (CASE WHEN le.currency_code = 'EUR' THEN le.amount ELSE le.amount / er.rate END)
          ELSE 0
        END) AS ledger_contra_revenue_debit_eur
  FROM ledger_entries le
  JOIN ledger_accounts la ON la.id = le.ledger_account_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = le.currency_code
   AND er.as_of = date(le.occurred_at)
  WHERE le.occurred_at >= DATE '2025-01-01'
    AND le.occurred_at < DATE '2026-01-01'
    AND la.account_type IN ('revenue', 'contra_revenue')
  GROUP BY 1, 2
),
payments_q AS (
  SELECT
    EXTRACT(YEAR FROM p.captured_at)::int AS yr,
    EXTRACT(QUARTER FROM p.captured_at)::int AS qtr,
    SUM(CASE
          WHEN p.currency_code = 'EUR' THEN (p.amount - p.amount_refunded)
          ELSE (p.amount - p.amount_refunded) / er2.rate
        END) AS payments_net_amount_eur
  FROM payments p
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = p.currency_code
   AND er2.as_of = date(p.captured_at)
  WHERE p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
    AND p.status IN ('captured', 'refunded', 'partially_refunded')
  GROUP BY 1, 2
)
SELECT
  l.yr || '-Q' || l.qtr AS quarter,
  ROUND(l.ledger_revenue_credit_eur, 2) AS ledger_revenue_credit_eur,
  ROUND(l.ledger_contra_revenue_debit_eur, 2) AS ledger_contra_revenue_debit_eur,
  ROUND(l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur, 2) AS ledger_net_revenue_eur,
  ROUND(p.payments_net_amount_eur, 2) AS payments_net_amount_eur,
  ROUND((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur, 2) AS diff_eur,
  ROUND(
    ((l.ledger_revenue_credit_eur - l.ledger_contra_revenue_debit_eur) - p.payments_net_amount_eur)
    / NULLIF(p.payments_net_amount_eur, 0) * 100,
    2
  ) AS diff_pct
FROM ledger_q l
JOIN payments_q p ON p.yr = l.yr AND p.qtr = l.qtr
ORDER BY l.yr, l.qtr

The warehouse answers

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

ViktorSubmitted. The report computes, for each 2025 calendar quarter, the ledger's net revenue (revenue credits minus contra-revenue debits, both converted to EUR) against payments' net captured amount (also EUR), along with the absolute and percentage difference between the two — exactly as Kofi specified.

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

Viktor's transcript (270 KB) · Kofi Mensah's transcript (906 KB) · Tobias Lindqvist's transcript (269 KB) · the night's plan (6 KB) · the night's reports (5 KB) · the week's memo (3 KB)

2025 Revenue Reconciliation via Correct Ledger Accounts, by Quarter

A business event lands on the Chief Financial Officer's desk

Tobias Lindqvist writes to Kofi Mensah

Kofi Mensah plans the night

Viktor writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q227

Everything, as recorded