DB Olympics

Queries · q225 · Chief Financial Officer · week 2, night 2

Payments With No Ledger Entries, by Merchant, 2025

“Is the 'payment with zero ledger entries' gap concentrated in a few merchants, or spread thinly across many?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. 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

  2. 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

  3. Kofi Mensah plans the night

    The analyst · week 2, night 2, request 3 of 3

    Report 1's ledger columns are the big finding. For every refunded payment, ledger_credit_total exactly equals payment_amount + amount_refunded, not net_amount: e.g. OfficeHub Nordic SO-100363 (265,275.15 paid, fully refunded) shows ledger_credit_total = 530,550.30 = 2x the payment, and Fell & Fjell #1179 shows 69,408.75 = 34,885.85 + 34,522.90 exactly. My ledger_debit_credit_diff column is useless for catching this (it's 0 everywhere by construction, since any balanced journal entry nets to zero regardless of which accounts are touched) — I designed that check too naively. Before I tell Tobias "the ledger balances," I need to see the actual account-level entries for a few of these to confirm this is just a normal (and explainable) refund-accounting pattern — e.g. a reversing entry on a different account — rather than revenue being double-booked.

    Two more things in Report 1 don't fit any pattern and need chasing at scale rather than left as anecdotes: Castell Fasteners' SO-100233 (no refund, not yet paid out) has ledger_credit_total of 805.56 against a payment_amount of 752.91 — an unexplained ~7% gap; and Marrowby & Vane's #3307 (a tiny $20 payment) has NULL ledger_credit_total, meaning no ledger entries exist for it at all. Given this week's pattern across other teams (CISO: deletions and role changes with zero audit trail), an "orphan payment with no ledger entry" is exactly the kind of gap auditors will zero in on, so I want to know if that's a one-off or systemic before Friday.

    Tonight's three slots: (1) pull the actual ledger line items for the flagged refunded + anomalous sample payments to explain the credit-total pattern; (2) run the same credit-total-vs-payment reconciliation across ALL of 2025's payments (not just the 25-sample) to see if the "+refund" pattern is universal/explainable and how big the "other mismatch" and "no ledger entries" buckets really are; (3) quantify the no-ledger-entry gap by merchant to see if it's concentrated or spread thin.

    Payments With No Ledger Entries, by Merchant, 2025
    Question
    Is the 'payment with zero ledger entries' gap concentrated in a few merchants, or spread thinly across many?
    One row per
    one tenant (merchant), for 2025
    Columns
    • merchant_name
    • total_payments_2025
    • payments_without_ledger_entries
    • pct_without_ledger_entries
    • amount_at_risk_eur (sum of payment_amount for payments with no ledger entries, converted to EUR)

  4. Ines writes the SQL

    The engineer · 2 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH pay_2025 AS (
  SELECT
    p.id AS payment_id,
    p.tenant_id,
    p.currency_code,
    p.amount,
    p.captured_at
  FROM payments p
  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
),
ledger_counts AS (
  SELECT payment_id, COUNT(*) AS entry_count
  FROM ledger_entries
  WHERE payment_id IS NOT NULL
  GROUP BY payment_id
),
pay_flagged AS (
  SELECT
    p.tenant_id,
    p.payment_id,
    p.currency_code,
    p.amount,
    p.captured_at,
    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger
  FROM pay_2025 p
  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id
),
pay_eur AS (
  SELECT
    pf.*,
    CASE
      WHEN pf.currency_code = 'EUR' THEN pf.amount
      ELSE pf.amount / er.rate
    END AS amount_eur
  FROM pay_flagged pf
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = pf.currency_code
    AND er.as_of = DATE(pf.captured_at)
)
SELECT
  t.name AS merchant_name,
  COUNT(*) AS total_payments_2025,
  SUM(pe.no_ledger) AS payments_without_ledger_entries,
  ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,
  ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur
FROM pay_eur pe
JOIN tenants t ON t.id = pe.tenant_id
GROUP BY t.name
HAVING SUM(pe.no_ledger) >= 1
ORDER BY amount_at_risk_eur DESC, merchant_name ASC
LIMIT 30
    Dry run 2 · 2 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH pay_2025 AS (  SELECT    p.id AS payment_id,    p.tenant_id,    p.currency_code,    p.amount,    p.captured_at  FROM payments p  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')    AND p.captured_at >= DATE '2025-01-01'    AND p.captured_at < DATE '2026-01-01'),ledger_counts AS (  SELECT payment_id, COUNT(*) AS entry_count  FROM ledger_entries  WHERE payment_id IS NOT NULL  GROUP BY payment_id),pay_flagged AS (  SELECT    p.tenant_id,    p.payment_id,    p.currency_code,    p.amount,    p.captured_at,    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger  FROM pay_2025 p  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id),pay_eur AS (  SELECT    pf.*,    CASE      WHEN pf.currency_code = 'EUR' THEN pf.amount      ELSE pf.amount / er.rate    END AS amount_eur  FROM pay_flagged pf  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = pf.currency_code    AND er.as_of = DATE(pf.captured_at)),by_tenant AS (  SELECT    t.name AS merchant_name,    COUNT(*) AS total_payments_2025,    SUM(pe.no_ledger) AS payments_without_ledger_entries,    ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,    ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur  FROM pay_eur pe  JOIN tenants t ON t.id = pe.tenant_id  GROUP BY t.name  HAVING SUM(pe.no_ledger) >= 1)SELECT  t.name AS merchant_name,  COUNT(*) AS total_payments_2025,  SUM(pe.no_ledger) AS payments_without_ledger_entries,  ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,  ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eurFROM pay_eur peJOIN tenants t ON t.id = pe.tenant_idGROUP BY t.nameHAVING SUM(pe.no_ledger) >= 1  merchant_name,  total_payments_2025,  payments_without_ledger_entries,  pct_without_ledger_entries,  amount_at_risk_eur,  COUNT(*) OVER () AS tenants_with_gap_totalFROM by_tenantORDER BY amount_at_risk_eur DESC, merchant_name ASCLIMIT 30

    “This compiles cleanly with the right columns. Submitting now.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of what the report delivers: - Grain: one row per merchant (tenant), restricted to merchants with ≥1 payment lacking any linked ledger entry. - Filter: payments with status in captured, refunded, or partially_refunded, captured in 2025. - Gap detection: LEFT JOIN to a per-payment count of ledger_entries; zero/null count flags the payment as having no ledger entry. - Conversion: local payment amount → EUR via exchange_rates (base EUR, quote = payment currency, matched on the capture date). - Columns: merchant_name, total_payments_2025, payments_without_ledger_entries, pct_without_ledger_entries, amount_at_risk_eur, plus a tenants_with_gap_total window-function column repeating the overall count of merchants with any gap (since only the top 30 by amount_at_risk are printed). - Order: amount_at_risk_eur descending, merchant_name ascending tie-break, capped at 30 rows.”

  5. The report comes back overnight

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

    merchant_name          | total_payments_2025 | payments_without_ledger_entries | pct_without_ledger_entries | amount_at_risk_eur | tenants_with_gap_total
-----------------------+---------------------+---------------------------------+----------------------------+--------------------+-----------------------
Cragsley Outfitters    |                3566 |                              23 |                       0.64 |            1141.37 |                     31
Marrowby & Vane        |                1719 |                              16 |                       0.93 |             815.67 |                     31
Linden & Loom          |                 266 |                              11 |                       4.14 |             681.88 |                     31
Petalcourt Beauty      |                 692 |                               8 |                       1.16 |             510.83 |                     31
Wiesgrund Wohnen       |                 294 |                               5 |                        1.7 |              199.1 |                     31
Coastline Interiors    |                 153 |                               2 |                       1.31 |             150.62 |                     31
Kayak Corner           |                 106 |                               2 |                       1.89 |             148.83 |                     31
Rue Lafayette Studio   |                 182 |                               2 |                        1.1 |              126.1 |                     31
Gratlinie Outdoor      |                 231 |                               2 |                       0.87 |              125.0 |                     31
The Balm Room          |                 104 |                               2 |                       1.92 |             104.95 |                     31
Ostwind Mode           |                 120 |                               1 |                       0.83 |              100.0 |                     31
Wohnglück              |                 145 |                               1 |                       0.69 |              100.0 |                     31
Fennel & Flint         |                 383 |                               2 |                       0.52 |              75.99 |                     31
Hautnah                |                  94 |                               2 |                       2.13 |               75.0 |                     31
Studio Vrij            |                 608 |                               2 |                       0.33 |               75.0 |                     31
Saffron & Sage         |                 117 |                               2 |                       1.71 |              74.54 |                     31
Little Larch Kids      |                  24 |                               1 |                       4.17 |              52.22 |                     31
Velvet Ash             |                 132 |                               1 |                       0.76 |              50.85 |                     31
Fell & Fjell           |                  83 |                               1 |                        1.2 |              50.41 |                     31
Nordic Bloom Cosmetics |                 141 |                               1 |                       0.71 |               50.4 |                     31
Oakhurst Tailoring     |                 162 |                               1 |                       0.62 |               50.3 |                     31
Bijoux Lune            |                  22 |                               1 |                       4.55 |               50.0 |                     31
Lumen Collective       |                  52 |                               1 |                       1.92 |               50.0 |                     31
Maison Clair           |                  52 |                               1 |                       1.92 |               50.0 |                     31
Noordzee Surfwear      |                  66 |                               2 |                       3.03 |               50.0 |                     31
Ondine Botanicals      |                  25 |                               1 |                        4.0 |               50.0 |                     31
Sorella Skin           |                  97 |                               1 |                       1.03 |               50.0 |                     31
Juniper Apothecary     |                 154 |                               2 |                        1.3 |               49.5 |                     31
Silverthread           |                  14 |                               1 |                       7.14 |              26.36 |                     31
Tiny Atelier           |                  35 |                               1 |                       2.86 |              23.25 |                     31
(30 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q225

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

    • spellingfetch first
    • spellingdate cast
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (9×)
    • 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×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    WITH pay_2025 AS (  SELECT    p.id AS payment_id,    p.tenant_id,    p.currency_code,    p.amount,    p.captured_at    p.tenant_id AS tenant_id,    p.currency_code AS currency_code,    p.amount AS amount,    p.captured_at AS captured_at  FROM payments p  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')    AND p.captured_at >= DATE '2025-01-01'    AND p.captured_at < DATE '2026-01-01'),ledger_counts AS (  SELECT payment_id, COUNT(*) AS entry_count  FROM ledger_entries  WHERE payment_id IS NOT NULL  GROUP BY payment_id),pay_flagged AS (  SELECT    p.tenant_id,    p.payment_id,    p.currency_code,    p.amount,    p.captured_at,    p.tenant_id AS tenant_id,    p.payment_id AS payment_id,    p.currency_code AS currency_code,    p.amount AS amount,    p.captured_at AS captured_at,    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger  FROM pay_2025 p  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id),pay_eur AS (  SELECT    pf.*,    CASE      WHEN pf.currency_code = 'EUR' THEN pf.amount      ELSE pf.amount / er.rate      ELSE CAST(pf.amount / er.rate AS NUMERIC(18,6))    END AS amount_eur  FROM pay_flagged pf  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = pf.currency_code    AND er.as_of = DATE(pf.captured_at)    AND er.as_of = CAST(pf.captured_at AS DATE)),by_tenant AS (  SELECT    t.name AS merchant_name,    COUNT(*) AS total_payments_2025,    SUM(pe.no_ledger) AS payments_without_ledger_entries,    ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,    ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur    100.0 * SUM(pe.no_ledger) / COUNT(*) AS pct_without_ledger_entries,    SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END) AS amount_at_risk_eur  FROM pay_eur pe  JOIN tenants t ON t.id = pe.tenant_id  GROUP BY t.name  HAVING SUM(pe.no_ledger) >= 1)SELECT  merchant_name,  total_payments_2025,  payments_without_ledger_entries,  pct_without_ledger_entries,  amount_at_risk_eur,  COUNT(*) OVER () AS tenants_with_gap_totalFROM by_tenantORDER BY amount_at_risk_eur DESC, merchant_name ASCLIMIT 30ORDER BY amount_at_risk_eur DESC NULLS FIRST, merchant_name ASC NULLS LAST{{fetch_first(30)}}

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

    WITH pay_2025 AS (
  SELECT
    p.id AS payment_id,
    p.tenant_id AS tenant_id,
    p.currency_code AS currency_code,
    p.amount AS amount,
    p.captured_at AS captured_at
  FROM payments p
  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
),
ledger_counts AS (
  SELECT payment_id, COUNT(*) AS entry_count
  FROM ledger_entries
  WHERE payment_id IS NOT NULL
  GROUP BY payment_id
),
pay_flagged AS (
  SELECT
    p.tenant_id AS tenant_id,
    p.payment_id AS payment_id,
    p.currency_code AS currency_code,
    p.amount AS amount,
    p.captured_at AS captured_at,
    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger
  FROM pay_2025 p
  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id
),
pay_eur AS (
  SELECT
    pf.*,
    CASE
      WHEN pf.currency_code = 'EUR' THEN pf.amount
      ELSE CAST(pf.amount / er.rate AS NUMERIC(18,6))
    END AS amount_eur
  FROM pay_flagged 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)
),
by_tenant AS (
  SELECT
    t.name AS merchant_name,
    COUNT(*) AS total_payments_2025,
    SUM(pe.no_ledger) AS payments_without_ledger_entries,
    100.0 * SUM(pe.no_ledger) / COUNT(*) AS pct_without_ledger_entries,
    SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END) AS amount_at_risk_eur
  FROM pay_eur pe
  JOIN tenants t ON t.id = pe.tenant_id
  GROUP BY t.name
  HAVING SUM(pe.no_ledger) >= 1
)
SELECT
  merchant_name,
  total_payments_2025,
  payments_without_ledger_entries,
  pct_without_ledger_entries,
  amount_at_risk_eur,
  COUNT(*) OVER () AS tenants_with_gap_total
FROM by_tenant
ORDER BY amount_at_risk_eur DESC NULLS FIRST, merchant_name ASC NULLS LAST
FETCH FIRST 30 ROWS ONLY
    100 ms1 s10 sHyper551 msDuckDB358 msGizmoSQL316 msclickhouse_brew1.02 sFirebolterrorDataFusion536 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 35, Column 27: 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": "d3b14138-e04a-4215-a7f2-07e00338737b", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.amount Decimal(14, 4) Decimal64(size = 0), __table8.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. q225 on the start page, with every configuration.

  8. 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

    Ines's whole session, 9 turns
    • The request, as Ines received it
      Report request from Kofi Mensah (analyst for the Chief Financial Officer):

Title: Payments With No Ledger Entries, by Merchant, 2025
Question: Is the 'payment with zero ledger entries' gap concentrated in a few merchants, or spread thinly across many?
Grain: one tenant (merchant), for 2025
Columns:
- merchant_name
- total_payments_2025
- payments_without_ledger_entries
- pct_without_ledger_entries
- amount_at_risk_eur (sum of payment_amount for payments with no ledger entries, converted to EUR)
Details: Source: payments with status in a captured/refunded/partially_refunded state and captured_at in 2025, LEFT JOIN to a per-payment count of ledger_entries via payment_id. Convert payment amounts to EUR via exchange_rates (base_code='EUR', quote_code=payments.currency_code, as_of=date(captured_at)). Group by tenant_id/merchant_name. Only include tenants with at least 1 payment that has zero linked ledger_entries. Order by amount_at_risk_eur descending, tie-break merchant_name ascending; print up to 30 rows and note in the report how many tenants in total had at least one such gap.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH pay_2025 AS (
  SELECT
    p.id AS payment_id,
    p.tenant_id,
    p.currency_code,
    p.amount,
    p.captured_at
  FROM payments p
  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
),
ledger_counts AS (
  SELECT payment_id, COUNT(*) AS entry_count
  FROM ledger_entries
  WHERE payment_id IS NOT NULL
  GROUP BY payment_id
),
pay_flagged AS (
  SELECT
    p.tenant_id,
    p.payment_id,
    p.currency_code,
    p.amount,
    p.captured_at,
    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger
  FROM pay_2025 p
  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id
),
pay_eur AS (
  SELECT
    pf.*,
    CASE
      WHEN pf.currency_code = 'EUR' THEN pf.amount
      ELSE pf.amount / er.rate
    END AS amount_eur
  FROM pay_flagged pf
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = pf.currency_code
    AND er.as_of = DATE(pf.captured_at)
)
SELECT
  t.name AS merchant_name,
  COUNT(*) AS total_payments_2025,
  SUM(pe.no_ledger) AS payments_without_ledger_entries,
  ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,
  ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur
FROM pay_eur pe
JOIN tenants t ON t.id = pe.tenant_id
GROUP BY t.name
HAVING SUM(pe.no_ledger) >= 1
ORDER BY amount_at_risk_eur DESC, merchant_name ASC
LIMIT 30
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, total_payments_2025, payments_without_ledger_entries, pct_without_ledger_entries, amount_at_risk_eur
    • Ines calls dry_run
      WITH pay_2025 AS (
  SELECT
    p.id AS payment_id,
    p.tenant_id,
    p.currency_code,
    p.amount,
    p.captured_at
  FROM payments p
  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
),
ledger_counts AS (
  SELECT payment_id, COUNT(*) AS entry_count
  FROM ledger_entries
  WHERE payment_id IS NOT NULL
  GROUP BY payment_id
),
pay_flagged AS (
  SELECT
    p.tenant_id,
    p.payment_id,
    p.currency_code,
    p.amount,
    p.captured_at,
    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger
  FROM pay_2025 p
  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id
),
pay_eur AS (
  SELECT
    pf.*,
    CASE
      WHEN pf.currency_code = 'EUR' THEN pf.amount
      ELSE pf.amount / er.rate
    END AS amount_eur
  FROM pay_flagged pf
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = pf.currency_code
    AND er.as_of = DATE(pf.captured_at)
),
by_tenant AS (
  SELECT
    t.name AS merchant_name,
    COUNT(*) AS total_payments_2025,
    SUM(pe.no_ledger) AS payments_without_ledger_entries,
    ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,
    ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur
  FROM pay_eur pe
  JOIN tenants t ON t.id = pe.tenant_id
  GROUP BY t.name
  HAVING SUM(pe.no_ledger) >= 1
)
SELECT
  merchant_name,
  total_payments_2025,
  payments_without_ledger_entries,
  pct_without_ledger_entries,
  amount_at_risk_eur,
  COUNT(*) OVER () AS tenants_with_gap_total
FROM by_tenant
ORDER BY amount_at_risk_eur DESC, merchant_name ASC
LIMIT 30
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, total_payments_2025, payments_without_ledger_entries, pct_without_ledger_entries, amount_at_risk_eur, tenants_with_gap_total
    • InesThis compiles cleanly with the right columns. Submitting now.
    • Ines calls submit
      WITH pay_2025 AS (
  SELECT
    p.id AS payment_id,
    p.tenant_id,
    p.currency_code,
    p.amount,
    p.captured_at
  FROM payments p
  WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
    AND p.captured_at >= DATE '2025-01-01'
    AND p.captured_at < DATE '2026-01-01'
),
ledger_counts AS (
  SELECT payment_id, COUNT(*) AS entry_count
  FROM ledger_entries
  WHERE payment_id IS NOT NULL
  GROUP BY payment_id
),
pay_flagged AS (
  SELECT
    p.tenant_id,
    p.payment_id,
    p.currency_code,
    p.amount,
    p.captured_at,
    CASE WHEN lc.entry_count IS NULL OR lc.entry_count = 0 THEN 1 ELSE 0 END AS no_ledger
  FROM pay_2025 p
  LEFT JOIN ledger_counts lc ON lc.payment_id = p.payment_id
),
pay_eur AS (
  SELECT
    pf.*,
    CASE
      WHEN pf.currency_code = 'EUR' THEN pf.amount
      ELSE pf.amount / er.rate
    END AS amount_eur
  FROM pay_flagged pf
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = pf.currency_code
    AND er.as_of = DATE(pf.captured_at)
),
by_tenant AS (
  SELECT
    t.name AS merchant_name,
    COUNT(*) AS total_payments_2025,
    SUM(pe.no_ledger) AS payments_without_ledger_entries,
    ROUND(100.0 * SUM(pe.no_ledger) / COUNT(*), 2) AS pct_without_ledger_entries,
    ROUND(SUM(CASE WHEN pe.no_ledger = 1 THEN pe.amount_eur ELSE 0 END), 2) AS amount_at_risk_eur
  FROM pay_eur pe
  JOIN tenants t ON t.id = pe.tenant_id
  GROUP BY t.name
  HAVING SUM(pe.no_ledger) >= 1
)
SELECT
  merchant_name,
  total_payments_2025,
  payments_without_ledger_entries,
  pct_without_ledger_entries,
  amount_at_risk_eur,
  COUNT(*) OVER () AS tenants_with_gap_total
FROM by_tenant
ORDER BY amount_at_risk_eur DESC, merchant_name ASC
LIMIT 30
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. Summary of what the report delivers: - **Grain:** one row per merchant (tenant), restricted to merchants with ≥1 payment lacking any linked ledger entry. - **Filter:** payments with status in `captured`, `refunded`, or `partially_refunded`, captured in 2025. - **Gap detection:** LEFT JOIN to a per-payment count of `ledger_entries`; zero/null count flags the payment as having no ledger entry. - **Conversion:** local payment amount → EUR via `exchange_rates` (base `EUR`, quote = payment currency, matched on the capture date). - **Columns:** merchant_name, total_payments_2025, payments_without_ledger_entries, pct_without_ledger_entries, amount_at_risk_eur, plus a `tenants_with_gap_total` window-function column repeating the overall count of merchants with any gap (since only the top 30 by amount_at_risk are printed). - **Order:** amount_at_risk_eur descending, merchant_name ascending tie-break, capped at 30 rows.

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

    Ines's transcript (274 KB) · Kofi Mensah's transcript (906 KB) · Tobias Lindqvist's transcript (269 KB) · the night's plan (6 KB) · the night's reports (19 KB) · the week's memo (3 KB)