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

Other-Mismatch Payments: Characterization by Payment Method, 2025

“Is the 2.1%-of-payments 'other mismatch' ledger bucket explained by a specific payment method (e.g. bank transfer/wire rather than card/gateway), or is it unexplained?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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 1 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.
Other-Mismatch Payments: Characterization by Payment Method, 2025
Question
Is the 2.1%-of-payments 'other mismatch' ledger bucket explained by a specific payment method (e.g. bank transfer/wire rather than card/gateway), or is it unexplained?
One row per
one payment_methods.kind, restricted to 2025 payments in the 'other mismatch' reconciliation category
Columns
- payment_method_kind
- payment_count
- pct_of_mismatch_count
- total_payment_amount_eur
- pct_of_mismatch_amount
- avg_abs_diff_eur (average of |ledger_credit_total_eur - expected_eur| where expected is the nearer of net_amount or payment+refund)

Bea 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 t0_payments_eur AS (
  SELECT
    p.id AS payment_id,
    p.payment_method_id,
    p.amount / er0.rate AS payment_amount_eur,
    p.amount_refunded / er0.rate AS amount_refunded_eur
  FROM payments p
  JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = p.currency_code
   AND er0.as_of = DATE(p.captured_at)
  WHERE p.status IN ('captured','refunded','partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at <  DATE '2026-01-01'
),

t1_ledger_credit AS (
  SELECT
    le.payment_id AS payment_id,
    SUM(le.amount / er1.rate) AS ledger_credit_total_eur
  FROM ledger_entries le
  JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
   AND er1.quote_code = le.currency_code
   AND er1.as_of = DATE(le.occurred_at)
  WHERE le.direction = 'credit'
    AND le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t2_ledger_rowcount AS (
  SELECT
    le.payment_id AS payment_id,
    COUNT(*) AS ledger_row_count
  FROM ledger_entries le
  WHERE le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t3_payment_classified AS (
  SELECT
    t0.payment_id,
    t0.payment_method_id,
    t0.payment_amount_eur,
    COALESCE(t1.ledger_credit_total_eur, 0) AS ledger_credit_total_eur,
    COALESCE(t2.ledger_row_count, 0) AS ledger_row_count,
    (t0.payment_amount_eur - t0.amount_refunded_eur) AS expected_net_eur,
    (t0.payment_amount_eur + t0.amount_refunded_eur) AS expected_gross_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur - t0.amount_refunded_eur)) AS diff_net_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur + t0.amount_refunded_eur)) AS diff_gross_eur
  FROM t0_payments_eur t0
  LEFT JOIN t1_ledger_credit t1 ON t1.payment_id = t0.payment_id
  LEFT JOIN t2_ledger_rowcount t2 ON t2.payment_id = t0.payment_id
),

t4_other_mismatch AS (
  SELECT
    t3.payment_id,
    t3.payment_method_id,
    t3.payment_amount_eur,
    CASE WHEN t3.diff_net_eur <= t3.diff_gross_eur THEN t3.diff_net_eur ELSE t3.diff_gross_eur END AS abs_diff_eur
  FROM t3_payment_classified t3
  WHERE t3.ledger_row_count >= 1
    AND t3.diff_net_eur   > GREATEST(0.01 * ABS(t3.expected_net_eur), 1)
    AND t3.diff_gross_eur > GREATEST(0.01 * ABS(t3.expected_gross_eur), 1)
),

t5_mismatch_with_kind AS (
  SELECT
    t4.payment_id,
    t4.payment_amount_eur,
    t4.abs_diff_eur,
    COALESCE(pm.kind, 'unknown') AS payment_method_kind
  FROM t4_other_mismatch t4
  LEFT JOIN payment_methods pm ON pm.id = t4.payment_method_id
),

t6_grand_totals AS (
  SELECT
    COUNT(*) AS grand_total_count,
    SUM(t5.payment_amount_eur) AS grand_total_amount_eur
  FROM t5_mismatch_with_kind t5
),

t7_count AS (
  SELECT t5.payment_method_kind, COUNT(*) AS payment_count
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t8_amount AS (
  SELECT t5.payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t9_avgdiff AS (
  SELECT t5.payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
)

SELECT
  t7.payment_method_kind AS payment_method_kind,
  t7.payment_count AS payment_count,
  ROUND(100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0), 2) AS pct_of_mismatch_count,
  ROUND(COALESCE(t8.total_payment_amount_eur, 0), 2) AS total_payment_amount_eur,
  ROUND(100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0), 2) AS pct_of_mismatch_amount,
  ROUND(COALESCE(t9.avg_abs_diff_eur, 0), 2) AS avg_abs_diff_eur
FROM t7_count t7
LEFT JOIN t8_amount t8 ON t8.payment_method_kind = t7.payment_method_kind
LEFT JOIN t9_avgdiff t9 ON t9.payment_method_kind = t7.payment_method_kind
CROSS JOIN t6_grand_totals t6
ORDER BY t7.payment_count DESC, t7.payment_method_kind ASC

“The query compiles and returns exactly 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 groups the 2025 "other mismatch" payments by payment_methods.kind (with null/missing mapped to 'unknown'), reproducing the same EUR-converted reconciliation logic from last night's report (net = gross − refund vs. gross = gross + refund, tolerance = max(1%, €1), excluding payments with zero linked ledger rows), so the CFO's team can see whether the mismatch concentrates in a particular payment rail or stays spread across all of them.”

The report comes back overnight

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

payment_method_kind | payment_count | pct_of_mismatch_count | total_payment_amount_eur | pct_of_mismatch_amount | avg_abs_diff_eur
--------------------+---------------+-----------------------+--------------------------+------------------------+-----------------
card                |           174 |                 69.88 |                  37280.8 |                  44.15 |            18.57
wallet              |            53 |                 21.29 |                 12417.77 |                  14.71 |            17.74
bank_transfer       |            19 |                  7.63 |                 34135.23 |                  40.42 |           897.95
credit              |             3 |                   1.2 |                   609.63 |                   0.72 |            33.08
(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:
- q230Bank Transfer Payments With Unexplained Ledger Mismatch: Full List, 2025
- q231Ledger Reconciliation Outcome by Payment Method, All 2025 Payments
It builds on:
- q224Ledger-to-Payment Reconciliation Pattern at Scale, 2025
- q223Ledger Line-Item Detail for Flagged Sample Payments, 2025

It becomes benchmark query q226

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

spellingdate cast (2×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
made explicita selected t.col gets AS col, its standard name made explicit (13×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)

The benchmark text, against the submitted SQL

WITH t0_payments_eur AS (  SELECT    p.id AS payment_id,    p.payment_method_id,    p.amount / er0.rate AS payment_amount_eur,    p.amount_refunded / er0.rate AS amount_refunded_eur    p.payment_method_id AS payment_method_id,    CAST(p.amount / er0.rate AS NUMERIC(18,6)) AS payment_amount_eur,    CAST(p.amount_refunded / er0.rate AS NUMERIC(18,6)) AS amount_refunded_eur  FROM payments p  JOIN exchange_rates er0    ON er0.base_code = 'EUR'   AND er0.quote_code = p.currency_code   AND er0.as_of = DATE(p.captured_at)   AND er0.as_of = CAST(p.captured_at AS DATE)  WHERE p.status IN ('captured','refunded','partially_refunded')    AND p.captured_at >= DATE '2025-01-01'    AND p.captured_at <  DATE '2026-01-01'),t1_ledger_credit AS (  SELECT    le.payment_id AS payment_id,    SUM(le.amount / er1.rate) AS ledger_credit_total_eur    SUM(CAST(le.amount / er1.rate AS NUMERIC(18,6))) AS ledger_credit_total_eur  FROM ledger_entries le  JOIN exchange_rates er1    ON er1.base_code = 'EUR'   AND er1.quote_code = le.currency_code   AND er1.as_of = DATE(le.occurred_at)   AND er1.as_of = CAST(le.occurred_at AS DATE)  WHERE le.direction = 'credit'    AND le.payment_id IS NOT NULL  GROUP BY le.payment_id),t2_ledger_rowcount AS (  SELECT    le.payment_id AS payment_id,    COUNT(*) AS ledger_row_count  FROM ledger_entries le  WHERE le.payment_id IS NOT NULL  GROUP BY le.payment_id),t3_payment_classified AS (  SELECT    t0.payment_id,    t0.payment_method_id,    t0.payment_amount_eur,    t0.payment_id AS payment_id,    t0.payment_method_id AS payment_method_id,    t0.payment_amount_eur AS payment_amount_eur,    COALESCE(t1.ledger_credit_total_eur, 0) AS ledger_credit_total_eur,    COALESCE(t2.ledger_row_count, 0) AS ledger_row_count,    (t0.payment_amount_eur - t0.amount_refunded_eur) AS expected_net_eur,    (t0.payment_amount_eur + t0.amount_refunded_eur) AS expected_gross_eur,    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur - t0.amount_refunded_eur)) AS diff_net_eur,    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur + t0.amount_refunded_eur)) AS diff_gross_eur  FROM t0_payments_eur t0  LEFT JOIN t1_ledger_credit t1 ON t1.payment_id = t0.payment_id  LEFT JOIN t2_ledger_rowcount t2 ON t2.payment_id = t0.payment_id),t4_other_mismatch AS (  SELECT    t3.payment_id,    t3.payment_method_id,    t3.payment_amount_eur,    t3.payment_id AS payment_id,    t3.payment_method_id AS payment_method_id,    t3.payment_amount_eur AS payment_amount_eur,    CASE WHEN t3.diff_net_eur <= t3.diff_gross_eur THEN t3.diff_net_eur ELSE t3.diff_gross_eur END AS abs_diff_eur  FROM t3_payment_classified t3  WHERE t3.ledger_row_count >= 1    AND t3.diff_net_eur   > GREATEST(0.01 * ABS(t3.expected_net_eur), 1)    AND t3.diff_gross_eur > GREATEST(0.01 * ABS(t3.expected_gross_eur), 1)),t5_mismatch_with_kind AS (  SELECT    t4.payment_id,    t4.payment_amount_eur,    t4.abs_diff_eur,    t4.payment_id AS payment_id,    t4.payment_amount_eur AS payment_amount_eur,    t4.abs_diff_eur AS abs_diff_eur,    COALESCE(pm.kind, 'unknown') AS payment_method_kind  FROM t4_other_mismatch t4  LEFT JOIN payment_methods pm ON pm.id = t4.payment_method_id),t6_grand_totals AS (  SELECT    COUNT(*) AS grand_total_count,    SUM(t5.payment_amount_eur) AS grand_total_amount_eur  FROM t5_mismatch_with_kind t5),t7_count AS (  SELECT t5.payment_method_kind, COUNT(*) AS payment_count  SELECT t5.payment_method_kind AS payment_method_kind, COUNT(*) AS payment_count  FROM t5_mismatch_with_kind t5  GROUP BY t5.payment_method_kind),t8_amount AS (  SELECT t5.payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur  SELECT t5.payment_method_kind AS payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur  FROM t5_mismatch_with_kind t5  GROUP BY t5.payment_method_kind),t9_avgdiff AS (  SELECT t5.payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur  SELECT t5.payment_method_kind AS payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur  FROM t5_mismatch_with_kind t5  GROUP BY t5.payment_method_kind)SELECT  t7.payment_method_kind AS payment_method_kind,  t7.payment_count AS payment_count,  ROUND(100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0), 2) AS pct_of_mismatch_count,  ROUND(COALESCE(t8.total_payment_amount_eur, 0), 2) AS total_payment_amount_eur,  ROUND(100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0), 2) AS pct_of_mismatch_amount,  ROUND(COALESCE(t9.avg_abs_diff_eur, 0), 2) AS avg_abs_diff_eur  100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0) AS pct_of_mismatch_count,  COALESCE(t8.total_payment_amount_eur, 0) AS total_payment_amount_eur,  100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0) AS pct_of_mismatch_amount,  COALESCE(t9.avg_abs_diff_eur, 0) AS avg_abs_diff_eurFROM t7_count t7LEFT JOIN t8_amount t8 ON t8.payment_method_kind = t7.payment_method_kindLEFT JOIN t9_avgdiff t9 ON t9.payment_method_kind = t7.payment_method_kindCROSS JOIN t6_grand_totals t6ORDER BY t7.payment_count DESC, t7.payment_method_kind ASCORDER BY t7.payment_count DESC NULLS FIRST, t7.payment_method_kind ASC NULLS LAST

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

WITH t0_payments_eur AS (
  SELECT
    p.id AS payment_id,
    p.payment_method_id AS payment_method_id,
    CAST(p.amount / er0.rate AS NUMERIC(18,6)) AS payment_amount_eur,
    CAST(p.amount_refunded / er0.rate AS NUMERIC(18,6)) AS amount_refunded_eur
  FROM payments p
  JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = p.currency_code
   AND er0.as_of = CAST(p.captured_at AS DATE)
  WHERE p.status IN ('captured','refunded','partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at <  DATE '2026-01-01'
),

t1_ledger_credit AS (
  SELECT
    le.payment_id AS payment_id,
    SUM(CAST(le.amount / er1.rate AS NUMERIC(18,6))) AS ledger_credit_total_eur
  FROM ledger_entries le
  JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
   AND er1.quote_code = le.currency_code
   AND er1.as_of = CAST(le.occurred_at AS DATE)
  WHERE le.direction = 'credit'
    AND le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t2_ledger_rowcount AS (
  SELECT
    le.payment_id AS payment_id,
    COUNT(*) AS ledger_row_count
  FROM ledger_entries le
  WHERE le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t3_payment_classified AS (
  SELECT
    t0.payment_id AS payment_id,
    t0.payment_method_id AS payment_method_id,
    t0.payment_amount_eur AS payment_amount_eur,
    COALESCE(t1.ledger_credit_total_eur, 0) AS ledger_credit_total_eur,
    COALESCE(t2.ledger_row_count, 0) AS ledger_row_count,
    (t0.payment_amount_eur - t0.amount_refunded_eur) AS expected_net_eur,
    (t0.payment_amount_eur + t0.amount_refunded_eur) AS expected_gross_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur - t0.amount_refunded_eur)) AS diff_net_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur + t0.amount_refunded_eur)) AS diff_gross_eur
  FROM t0_payments_eur t0
  LEFT JOIN t1_ledger_credit t1 ON t1.payment_id = t0.payment_id
  LEFT JOIN t2_ledger_rowcount t2 ON t2.payment_id = t0.payment_id
),

t4_other_mismatch AS (
  SELECT
    t3.payment_id AS payment_id,
    t3.payment_method_id AS payment_method_id,
    t3.payment_amount_eur AS payment_amount_eur,
    CASE WHEN t3.diff_net_eur <= t3.diff_gross_eur THEN t3.diff_net_eur ELSE t3.diff_gross_eur END AS abs_diff_eur
  FROM t3_payment_classified t3
  WHERE t3.ledger_row_count >= 1
    AND t3.diff_net_eur   > GREATEST(0.01 * ABS(t3.expected_net_eur), 1)
    AND t3.diff_gross_eur > GREATEST(0.01 * ABS(t3.expected_gross_eur), 1)
),

t5_mismatch_with_kind AS (
  SELECT
    t4.payment_id AS payment_id,
    t4.payment_amount_eur AS payment_amount_eur,
    t4.abs_diff_eur AS abs_diff_eur,
    COALESCE(pm.kind, 'unknown') AS payment_method_kind
  FROM t4_other_mismatch t4
  LEFT JOIN payment_methods pm ON pm.id = t4.payment_method_id
),

t6_grand_totals AS (
  SELECT
    COUNT(*) AS grand_total_count,
    SUM(t5.payment_amount_eur) AS grand_total_amount_eur
  FROM t5_mismatch_with_kind t5
),

t7_count AS (
  SELECT t5.payment_method_kind AS payment_method_kind, COUNT(*) AS payment_count
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t8_amount AS (
  SELECT t5.payment_method_kind AS payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t9_avgdiff AS (
  SELECT t5.payment_method_kind AS payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
)

SELECT
  t7.payment_method_kind AS payment_method_kind,
  t7.payment_count AS payment_count,
  100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0) AS pct_of_mismatch_count,
  COALESCE(t8.total_payment_amount_eur, 0) AS total_payment_amount_eur,
  100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0) AS pct_of_mismatch_amount,
  COALESCE(t9.avg_abs_diff_eur, 0) AS avg_abs_diff_eur
FROM t7_count t7
LEFT JOIN t8_amount t8 ON t8.payment_method_kind = t7.payment_method_kind
LEFT JOIN t9_avgdiff t9 ON t9.payment_method_kind = t7.payment_method_kind
CROSS JOIN t6_grand_totals t6
ORDER BY t7.payment_count DESC NULLS FIRST, t7.payment_method_kind ASC NULLS LAST

Firebolt{ "errors": [ { "description": "Line 5, Column 19: 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": "a5a43738-0697-477c-a6f2-351dc878f7d1", "query_labe
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table6.amount Decimal(14, 4) Decimal64(size = 0), __table7.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Bea's whole session, 7 turns

The request, as Bea received it

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

Title: Other-Mismatch Payments: Characterization by Payment Method, 2025
Question: Is the 2.1%-of-payments 'other mismatch' ledger bucket explained by a specific payment method (e.g. bank transfer/wire rather than card/gateway), or is it unexplained?
Grain: one payment_methods.kind, restricted to 2025 payments in the 'other mismatch' reconciliation category
Columns:
- payment_method_kind
- payment_count
- pct_of_mismatch_count
- total_payment_amount_eur
- pct_of_mismatch_amount
- avg_abs_diff_eur (average of |ledger_credit_total_eur - expected_eur| where expected is the nearer of net_amount or payment+refund)
Details: Recompute the same classification as last night's 'Ledger-to-Payment Reconciliation Pattern at Scale, 2025' report: for payments captured in 2025 (status captured/refunded/partially_refunded), aggregate linked ledger_entries by payment_id (sum of direction='credit' amounts, each converted to EUR via exchange_rates on the ledger entry's own currency_code and date(occurred_at)), and compare to payment_amount_eur and amount_refunded_eur (converted via exchange_rates on captured_at). A payment falls in 'other mismatch' if its ledger credit total is NOT within 1% (or €1, whichever is larger) of either (payment_amount_eur - amount_refunded_eur) or (payment_amount_eur + amount_refunded_eur), and it has at least one ledger_entries row (i.e., exclude 'no ledger entries' payments). For that 'other mismatch' set only, join payments.payment_method_id -> payment_methods.kind and group by kind (null/missing payment_method_id as 'unknown'). avg_abs_diff_eur = average of the absolute difference between ledger_credit_total_eur and whichever of the two expected values (net or gross+refund) is closer. Order by payment_count descending.

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

Bea calls dry_run

WITH t0_payments_eur AS (
  SELECT
    p.id AS payment_id,
    p.payment_method_id,
    p.amount / er0.rate AS payment_amount_eur,
    p.amount_refunded / er0.rate AS amount_refunded_eur
  FROM payments p
  JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = p.currency_code
   AND er0.as_of = DATE(p.captured_at)
  WHERE p.status IN ('captured','refunded','partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at <  DATE '2026-01-01'
),

t1_ledger_credit AS (
  SELECT
    le.payment_id AS payment_id,
    SUM(le.amount / er1.rate) AS ledger_credit_total_eur
  FROM ledger_entries le
  JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
   AND er1.quote_code = le.currency_code
   AND er1.as_of = DATE(le.occurred_at)
  WHERE le.direction = 'credit'
    AND le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t2_ledger_rowcount AS (
  SELECT
    le.payment_id AS payment_id,
    COUNT(*) AS ledger_row_count
  FROM ledger_entries le
  WHERE le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t3_payment_classified AS (
  SELECT
    t0.payment_id,
    t0.payment_method_id,
    t0.payment_amount_eur,
    COALESCE(t1.ledger_credit_total_eur, 0) AS ledger_credit_total_eur,
    COALESCE(t2.ledger_row_count, 0) AS ledger_row_count,
    (t0.payment_amount_eur - t0.amount_refunded_eur) AS expected_net_eur,
    (t0.payment_amount_eur + t0.amount_refunded_eur) AS expected_gross_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur - t0.amount_refunded_eur)) AS diff_net_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur + t0.amount_refunded_eur)) AS diff_gross_eur
  FROM t0_payments_eur t0
  LEFT JOIN t1_ledger_credit t1 ON t1.payment_id = t0.payment_id
  LEFT JOIN t2_ledger_rowcount t2 ON t2.payment_id = t0.payment_id
),

t4_other_mismatch AS (
  SELECT
    t3.payment_id,
    t3.payment_method_id,
    t3.payment_amount_eur,
    CASE WHEN t3.diff_net_eur <= t3.diff_gross_eur THEN t3.diff_net_eur ELSE t3.diff_gross_eur END AS abs_diff_eur
  FROM t3_payment_classified t3
  WHERE t3.ledger_row_count >= 1
    AND t3.diff_net_eur   > GREATEST(0.01 * ABS(t3.expected_net_eur), 1)
    AND t3.diff_gross_eur > GREATEST(0.01 * ABS(t3.expected_gross_eur), 1)
),

t5_mismatch_with_kind AS (
  SELECT
    t4.payment_id,
    t4.payment_amount_eur,
    t4.abs_diff_eur,
    COALESCE(pm.kind, 'unknown') AS payment_method_kind
  FROM t4_other_mismatch t4
  LEFT JOIN payment_methods pm ON pm.id = t4.payment_method_id
),

t6_grand_totals AS (
  SELECT
    COUNT(*) AS grand_total_count,
    SUM(t5.payment_amount_eur) AS grand_total_amount_eur
  FROM t5_mismatch_with_kind t5
),

t7_count AS (
  SELECT t5.payment_method_kind, COUNT(*) AS payment_count
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t8_amount AS (
  SELECT t5.payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t9_avgdiff AS (
  SELECT t5.payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
)

SELECT
  t7.payment_method_kind AS payment_method_kind,
  t7.payment_count AS payment_count,
  ROUND(100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0), 2) AS pct_of_mismatch_count,
  ROUND(COALESCE(t8.total_payment_amount_eur, 0), 2) AS total_payment_amount_eur,
  ROUND(100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0), 2) AS pct_of_mismatch_amount,
  ROUND(COALESCE(t9.avg_abs_diff_eur, 0), 2) AS avg_abs_diff_eur
FROM t7_count t7
LEFT JOIN t8_amount t8 ON t8.payment_method_kind = t7.payment_method_kind
LEFT JOIN t9_avgdiff t9 ON t9.payment_method_kind = t7.payment_method_kind
CROSS JOIN t6_grand_totals t6
ORDER BY t7.payment_count DESC, t7.payment_method_kind ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: payment_method_kind, payment_count, pct_of_mismatch_count, total_payment_amount_eur, pct_of_mismatch_amount, avg_abs_diff_eur

BeaThe query compiles and returns exactly the requested columns. Submitting.

Bea calls submit

WITH t0_payments_eur AS (
  SELECT
    p.id AS payment_id,
    p.payment_method_id,
    p.amount / er0.rate AS payment_amount_eur,
    p.amount_refunded / er0.rate AS amount_refunded_eur
  FROM payments p
  JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = p.currency_code
   AND er0.as_of = DATE(p.captured_at)
  WHERE p.status IN ('captured','refunded','partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at <  DATE '2026-01-01'
),

t1_ledger_credit AS (
  SELECT
    le.payment_id AS payment_id,
    SUM(le.amount / er1.rate) AS ledger_credit_total_eur
  FROM ledger_entries le
  JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
   AND er1.quote_code = le.currency_code
   AND er1.as_of = DATE(le.occurred_at)
  WHERE le.direction = 'credit'
    AND le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t2_ledger_rowcount AS (
  SELECT
    le.payment_id AS payment_id,
    COUNT(*) AS ledger_row_count
  FROM ledger_entries le
  WHERE le.payment_id IS NOT NULL
  GROUP BY le.payment_id
),

t3_payment_classified AS (
  SELECT
    t0.payment_id,
    t0.payment_method_id,
    t0.payment_amount_eur,
    COALESCE(t1.ledger_credit_total_eur, 0) AS ledger_credit_total_eur,
    COALESCE(t2.ledger_row_count, 0) AS ledger_row_count,
    (t0.payment_amount_eur - t0.amount_refunded_eur) AS expected_net_eur,
    (t0.payment_amount_eur + t0.amount_refunded_eur) AS expected_gross_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur - t0.amount_refunded_eur)) AS diff_net_eur,
    ABS(COALESCE(t1.ledger_credit_total_eur,0) - (t0.payment_amount_eur + t0.amount_refunded_eur)) AS diff_gross_eur
  FROM t0_payments_eur t0
  LEFT JOIN t1_ledger_credit t1 ON t1.payment_id = t0.payment_id
  LEFT JOIN t2_ledger_rowcount t2 ON t2.payment_id = t0.payment_id
),

t4_other_mismatch AS (
  SELECT
    t3.payment_id,
    t3.payment_method_id,
    t3.payment_amount_eur,
    CASE WHEN t3.diff_net_eur <= t3.diff_gross_eur THEN t3.diff_net_eur ELSE t3.diff_gross_eur END AS abs_diff_eur
  FROM t3_payment_classified t3
  WHERE t3.ledger_row_count >= 1
    AND t3.diff_net_eur   > GREATEST(0.01 * ABS(t3.expected_net_eur), 1)
    AND t3.diff_gross_eur > GREATEST(0.01 * ABS(t3.expected_gross_eur), 1)
),

t5_mismatch_with_kind AS (
  SELECT
    t4.payment_id,
    t4.payment_amount_eur,
    t4.abs_diff_eur,
    COALESCE(pm.kind, 'unknown') AS payment_method_kind
  FROM t4_other_mismatch t4
  LEFT JOIN payment_methods pm ON pm.id = t4.payment_method_id
),

t6_grand_totals AS (
  SELECT
    COUNT(*) AS grand_total_count,
    SUM(t5.payment_amount_eur) AS grand_total_amount_eur
  FROM t5_mismatch_with_kind t5
),

t7_count AS (
  SELECT t5.payment_method_kind, COUNT(*) AS payment_count
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t8_amount AS (
  SELECT t5.payment_method_kind, SUM(t5.payment_amount_eur) AS total_payment_amount_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
),

t9_avgdiff AS (
  SELECT t5.payment_method_kind, AVG(t5.abs_diff_eur) AS avg_abs_diff_eur
  FROM t5_mismatch_with_kind t5
  GROUP BY t5.payment_method_kind
)

SELECT
  t7.payment_method_kind AS payment_method_kind,
  t7.payment_count AS payment_count,
  ROUND(100.0 * t7.payment_count / NULLIF(t6.grand_total_count, 0), 2) AS pct_of_mismatch_count,
  ROUND(COALESCE(t8.total_payment_amount_eur, 0), 2) AS total_payment_amount_eur,
  ROUND(100.0 * COALESCE(t8.total_payment_amount_eur, 0) / NULLIF(t6.grand_total_amount_eur, 0), 2) AS pct_of_mismatch_amount,
  ROUND(COALESCE(t9.avg_abs_diff_eur, 0), 2) AS avg_abs_diff_eur
FROM t7_count t7
LEFT JOIN t8_amount t8 ON t8.payment_method_kind = t7.payment_method_kind
LEFT JOIN t9_avgdiff t9 ON t9.payment_method_kind = t7.payment_method_kind
CROSS JOIN t6_grand_totals t6
ORDER BY t7.payment_count DESC, t7.payment_method_kind ASC

The warehouse answers

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

BeaSubmitted. The report groups the 2025 "other mismatch" payments by `payment_methods.kind` (with null/missing mapped to `'unknown'`), reproducing the same EUR-converted reconciliation logic from last night's report (net = gross − refund vs. gross = gross + refund, tolerance = max(1%, €1), excluding payments with zero linked ledger rows), so the CFO's team can see whether the mismatch concentrates in a particular payment rail or stays spread across all of them.

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

Bea's transcript (293 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)

Other-Mismatch Payments: Characterization by Payment Method, 2025

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

Tobias Lindqvist writes to Kofi Mensah

Kofi Mensah plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q226

Everything, as recorded