Queries · q090 · Head of Data · week 1, night 1

Payout reconciliation to gross, fees and refunds

“Do payouts consistently reconcile as net = gross minus fee, and do refunds actually reduce what we pay out, or could refunds be double-counted or missing from the payout math?”

Manager · asked the questionRosa Van den BergHead of Data. Took over the analytics team last week. Before the team's numbers go to the board she wants to know how far they can be trusted.

Analyst · planned the reportYuki TanakaDetail-oriented: picks specific merchants, products or days and looks at them closely.

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 Head of Data's desk
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Before the board pack goes out, Data must certify the key metrics. Do revenue, orders, customers, refunds and payouts agree wherever they are recorded, and where they do not, why not and by how much?
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Rosa Van den Berg writes to Yuki Tanaka
The manager, at the start of week 1
Subject: Certifying the key metrics before the board pack — what I need from you this week

Hi Yuki,

Tobias and I have to certify revenue, orders, customers, refunds and payouts before they go into the board pack. "Certify" means we're telling the board these numbers are reconcilable — so this week's job is to find out where the different places we record these figures agree, where they don't, and whether any gap is big enough to change the story we tell. If something doesn't reconcile, I need to know why and by how much, not just that it's "close enough." That feeds directly into the go/no-go Tobias and I give on the board pack, so please prioritize breadth across the five metrics over depth on any one of them — we can always dig deeper next week on whichever one looks shakiest.

You only have a handful of report slots a night, so here's what matters most to me, roughly in priority order:
1. If someone asks "what was our revenue last month," do we get the same answer regardless of which part of the business we ask? Where it diverges, how far apart are the answers, and is the gap explained by something sensible (timing, currency, returns) or does it look like a real inconsistency?
2. Do our order counts and our revenue tell a consistent story — i.e., is there a clean, traceable link from an order to the money that was actually collected for it, or are there orders that seem to vanish or multiply along the way?
3. Refunds and payouts: when money goes back to a customer, does it show up consistently as a reduction in revenue and in what we actually pay out to merchants? I want to know if refunds could be hiding or double-counted anywhere.
4. Customers: can we count "how many customers we have" or "how many are active" in one consistent way, or do different parts of the data give meaningfully different headcounts?
5. If you have a slot left: is there anything about how timestamps or currencies are handled that could quietly distort any of the above when we roll numbers up across time or across merchants?
Please flag anything you find inconsistent together with your best read of why, and roughly how big the discrepancy is in practice — that's what Tobias and I need to decide whether we can certify as-is, certify with a caveat, or need to hold a number back.

Thank you — I know this is a lot for one week. Let's talk before you spend your first slot tonight if you want to sanity-check the plan.

Rosa
A note from the Chief Financial Officer, Tobias Lindqvist
Subject: What I need for the revenue/payout certification

Hi Yuki,

A few things from my side as you scope this week's work:
1. Does our reported revenue tie out end to end? I want to know whether what we book as platform fees and processing fees matches what actually settles to us through payouts — and if there's a gap, roughly how large it is and whether it's timing (month-end cutoff) or something structural.
2. Do merchant payouts reconcile to the gross volume and refunds we report? I'm particularly interested in whether net payouts consistently equal gross sales minus refunds minus our fees, or whether certain merchants/currencies show persistent drift.
3. Are our B2B wholesalers' outstanding receivables consistent with what we'd show in the annual accounts? I want to understand how much is genuinely overdue versus just lagging normal payment terms, since that affects how we present them.
If anything else jumps out while you're in there — especially around currency conversion timing or refund treatment — flag it even if it's outside these three.

Thanks, Tobias
Yuki Tanaka plans the night
The analyst · week 1, night 1, request 3 of 3
Rosa wants breadth first across all five metrics before depth on any one, and Tobias's top two asks (revenue tie-out, payout reconciliation) sit inside that same breadth pass. So tonight I'm building the global, month-grain scaffolding that lets us see, in one glance, whether revenue/orders/refunds/payouts agree with each other at the top level, before drilling into any single tenant, currency, or the B2B receivables question (saving those for night 2+ once I know where the gaps are). All three reports are global (all tenants, EUR-converted) monthly time series over the full history (2023-01 through 2025-12) so we can also see whether any gap is new/worsening or has been there all along. I convert every local-currency amount to EUR via exchange_rates (EUR is base_code, so EUR amount = local amount divided by rate) using the date of the relevant event, consistently across all three reports so discrepancies aren't an artifact of inconsistent conversion. Report 1 answers whether different parts of the business agree on revenue. Report 2 traces orders forward into captured payments. Report 3 checks payouts against gross, fees and refunds. I'm holding back tenant-level and currency-timing drill-downs, and the B2B receivables question, for later nights.
Payout reconciliation to gross, fees and refunds
Question
Do payouts consistently reconcile as net = gross minus fee, and do refunds actually reduce what we pay out, or could refunds be double-counted or missing from the payout math?
One row per
calendar month, 2023-01 through 2025-12, based on payout paid_at month
Columns
- month
- payout_gross_eur
- payout_fee_eur
- payout_net_eur
- net_check_diff_eur
- refunds_processed_eur
- net_minus_refunds_diff_eur

Viktor writes the SQL

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

Dry run 1 · STATUS: REJECTED

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,
    SUM(p.gross_amount / er.rate) AS gross_eur,
    SUM(p.fee_amount / er.rate) AS fee_eur,
    SUM(p.net_amount / er.rate) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    date_trunc('month', pr.processed_at)::date AS month,
    SUM(pr.amount / er.rate) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month,
  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,
  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,
  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,
  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month

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

WITH months AS (  SELECT DISTINCT month_start AS month  FROM calendar_days  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'),payout_agg AS (  SELECT    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,    SUM(p.gross_amount / er.rate) AS gross_eur,    SUM(p.fee_amount / er.rate) AS fee_eur,    SUM(p.net_amount / er.rate) AS net_eur  FROM payouts p  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = p.currency_code   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL  GROUP BY 1),refund_agg AS (  SELECT    date_trunc('month', pr.processed_at)::date AS month,    SUM(pr.amount / er.rate) AS refunds_eur  FROM payment_refunds pr  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = pr.currency_code   AND er.as_of = CAST(pr.processed_at AS date)  WHERE pr.status = 'succeeded'  GROUP BY 1)SELECT  to_char(m.month, 'YYYY-MM') AS month,  strftime(m.month, '%Y-%m') AS month,  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eurFROM months mLEFT JOIN payout_agg pa ON pa.month = m.monthLEFT JOIN refund_agg ra ON ra.month = m.monthORDER BY m.month

“Query compiles with the expected 7 columns. Submitting.”

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 does: - Grain: one row per calendar month, 2023-01 through 2025-12 (36 rows, well under the 50-row cap). - Payout figures: gross/fee/net summed in each payout's own currency, converted to EUR via exchange_rates (EUR→local rate as of the payout date), grouped by month of paid_at (falling back to arrival_date when paid_at is null). - net_check_diff_eur: net − (gross − fee); this isolates any internal inconsistency in the payout row's own three numbers. - refunds_processed_eur: succeeded payment_refunds converted to EUR, grouped by month of processed_at. - net_minus_refunds_diff_eur: net − (gross − fee − refunds); a persistent nonzero value here suggests refunds aren't being netted into payouts (or are double-counted), which is exactly the question Yuki asked. One caveat flagged in the submit note: refunds are matched to payouts only by calendar month, since there's no direct FK from payment_refunds to payouts in the schema — so a gap in the last column could be timing lag rather than a true accounting hole.”

The report comes back overnight

As Yuki Tanaka saw it, from the small dataset the agents worked on

month   | payout_gross_eur | payout_fee_eur | payout_net_eur | net_check_diff_eur | refunds_processed_eur | net_minus_refunds_diff_eur
--------+------------------+----------------+----------------+--------------------+-----------------------+---------------------------
2023-01 |         53709.82 |        1406.58 |       50121.03 |            -2182.2 |               3393.01 |                     1210.8
2023-02 |         50590.75 |        1342.13 |       39422.43 |            -9826.2 |              13034.09 |                    3207.89
2023-03 |         55055.62 |        1485.94 |       43280.45 |          -10289.23 |               9083.75 |                   -1205.48
2023-04 |          68499.6 |        1982.09 |        56262.5 |          -10255.01 |               8376.32 |                   -1878.69
2023-05 |         93673.67 |        2481.27 |       81170.17 |          -10022.22 |              11213.65 |                    1191.44
2023-06 |         61954.44 |        1677.97 |       49455.12 |          -10821.35 |               9932.92 |                    -888.43
2023-07 |         64924.71 |         1766.4 |       53455.56 |           -9702.76 |              10670.89 |                     968.13
2023-08 |         65320.68 |        1750.02 |       52531.55 |          -11039.12 |              10041.38 |                    -997.74
2023-09 |         70968.13 |        1916.18 |       60613.58 |           -8438.36 |               9028.08 |                     589.72
2023-10 |         98006.93 |        2531.86 |       82801.85 |          -12673.22 |              12739.55 |                      66.32
2023-11 |         192998.2 |        5184.93 |      172017.88 |          -15795.39 |              16689.72 |                     894.33
2023-12 |        172550.08 |        4526.79 |      124617.61 |          -43405.67 |               45282.9 |                    1877.23
2024-01 |        112246.44 |        2988.79 |        81279.5 |          -27978.14 |              28762.24 |                      784.1
2024-02 |        112012.26 |        3263.45 |        90349.8 |          -18399.01 |              15721.05 |                   -2677.97
2024-03 |        110386.35 |         2972.1 |       95208.35 |          -12205.89 |              12224.57 |                      18.67
2024-04 |        115514.24 |        3242.26 |       94708.84 |          -17563.14 |              16993.01 |                    -570.13
2024-05 |        121296.23 |        3490.98 |      102869.58 |          -14935.68 |              15230.21 |                     294.53
2024-06 |        100219.73 |        2773.41 |       82652.97 |          -14793.35 |              15315.98 |                     522.64
2024-07 |        108894.15 |        2844.58 |        91460.3 |          -14589.27 |              14215.29 |                    -373.97
2024-08 |         96008.55 |        2727.22 |       77582.07 |          -15699.26 |              18128.78 |                    2429.53
2024-09 |        113343.79 |        2991.07 |       96348.19 |          -14004.52 |              15130.15 |                    1125.63
2024-10 |        133784.95 |        3558.57 |      115165.74 |          -15060.63 |               13860.5 |                   -1200.14
2024-11 |        177317.93 |        4548.44 |      152658.86 |          -20110.63 |               20434.5 |                     323.87
2024-12 |        286218.09 |        7526.96 |      228039.14 |          -50651.98 |              58827.06 |                    8175.07
2025-01 |        199172.31 |        4954.45 |      151256.63 |          -42961.24 |              36758.42 |                   -6202.82
2025-02 |        117777.21 |        2907.62 |        95906.6 |           -18963.0 |              18604.99 |                    -358.01
2025-03 |        135070.48 |        3567.13 |      115334.32 |          -16169.04 |              16479.69 |                     310.65
2025-04 |        188851.76 |        5006.79 |      169982.71 |          -13862.25 |              16429.99 |                    2567.74
2025-05 |        127570.11 |        3482.02 |       98002.35 |          -26085.75 |              26537.35 |                      451.6
2025-06 |        113673.02 |        3123.65 |       91578.07 |          -18971.31 |              19509.64 |                     538.33
2025-07 |        132602.63 |        3604.89 |       108283.9 |          -20713.84 |              17897.73 |                   -2816.11
2025-08 |        141126.23 |        4276.29 |       118864.4 |          -17985.54 |              39838.78 |                   21853.24
2025-09 |         122449.4 |        3165.85 |      100229.52 |          -19054.03 |               15922.5 |                   -3131.54
2025-10 |        154129.95 |        4108.16 |      130131.01 |          -19890.78 |              25748.61 |                    5857.83
2025-11 |        236843.96 |        6380.01 |      199899.74 |          -30564.21 |              26661.72 |                   -3902.49
2025-12 |        322182.01 |         8609.0 |      250319.95 |          -63253.07 |              74611.41 |                   11358.34
(36 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:
- q093Payout net formula check via payment-linked refunds
- q099Payout net-formula drift by currency
It builds on:
- q088Monthly revenue by source

It becomes benchmark query q090

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

editAn output alias reused the name of an input column the same block still uses; DataFusion rejects the query, which the standard resolves (docs/RUNNER.md, Dialects: a rewrite that costs nothing on any system goes into the canonical query).
spellingcast (2×)
placeholderyear month
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (4×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results

The benchmark text, against the submitted SQL

WITH months AS (  SELECT DISTINCT month_start AS month  FROM calendar_days  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'),payout_agg AS (  SELECT    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,    SUM(p.gross_amount / er.rate) AS gross_eur,    SUM(p.fee_amount / er.rate) AS fee_eur,    SUM(p.net_amount / er.rate) AS net_eur    CAST(date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp))) AS date) AS month,    SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS gross_eur,    SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS fee_eur,    SUM(CAST(p.net_amount / er.rate AS NUMERIC(18,6))) AS net_eur  FROM payouts p  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = p.currency_code   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL  GROUP BY 1),refund_agg AS (  SELECT    date_trunc('month', pr.processed_at)::date AS month,    SUM(pr.amount / er.rate) AS refunds_eur    CAST(date_trunc('month', pr.processed_at) AS date) AS month,    SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur  FROM payment_refunds pr  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = pr.currency_code   AND er.as_of = CAST(pr.processed_at AS date)  WHERE pr.status = 'succeeded'  GROUP BY 1)SELECT  strftime(m.month, '%Y-%m') AS month,  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eur  {{year_month(m.month)}} AS month_label,  COALESCE(pa.gross_eur,0) AS payout_gross_eur,  COALESCE(pa.fee_eur,0) AS payout_fee_eur,  COALESCE(pa.net_eur,0) AS payout_net_eur,  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)) AS net_check_diff_eur,  COALESCE(ra.refunds_eur,0) AS refunds_processed_eur,  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)) AS net_minus_refunds_diff_eurFROM months mLEFT JOIN payout_agg pa ON pa.month = m.monthLEFT JOIN refund_agg ra ON ra.month = m.monthORDER BY m.monthORDER BY m.month NULLS LAST

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

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    CAST(date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp))) AS date) AS month,
    SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS gross_eur,
    SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS fee_eur,
    SUM(CAST(p.net_amount / er.rate AS NUMERIC(18,6))) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    CAST(date_trunc('month', pr.processed_at) AS date) AS month,
    SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month_label,
  COALESCE(pa.gross_eur,0) AS payout_gross_eur,
  COALESCE(pa.fee_eur,0) AS payout_fee_eur,
  COALESCE(pa.net_eur,0) AS payout_net_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)) AS net_check_diff_eur,
  COALESCE(ra.refunds_eur,0) AS refunds_processed_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    CAST(date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp))) AS date) AS month,
    SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS gross_eur,
    SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS fee_eur,
    SUM(CAST(p.net_amount / er.rate AS NUMERIC(18,6))) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    CAST(date_trunc('month', pr.processed_at) AS date) AS month,
    SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month_label,
  COALESCE(pa.gross_eur,0) AS payout_gross_eur,
  COALESCE(pa.fee_eur,0) AS payout_fee_eur,
  COALESCE(pa.net_eur,0) AS payout_net_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)) AS net_check_diff_eur,
  COALESCE(ra.refunds_eur,0) AS refunds_processed_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    CAST(date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp))) AS date) AS month,
    SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS gross_eur,
    SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS fee_eur,
    SUM(CAST(p.net_amount / er.rate AS NUMERIC(18,6))) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    CAST(date_trunc('month', pr.processed_at) AS date) AS month,
    SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  to_char(CAST(m.month AS timestamp), 'YYYY-MM') AS month_label,
  COALESCE(pa.gross_eur,0) AS payout_gross_eur,
  COALESCE(pa.fee_eur,0) AS payout_fee_eur,
  COALESCE(pa.net_eur,0) AS payout_net_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)) AS net_check_diff_eur,
  COALESCE(ra.refunds_eur,0) AS refunds_processed_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month NULLS LAST

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    CAST(date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp))) AS date) AS month,
    SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS gross_eur,
    SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS fee_eur,
    SUM(CAST(p.net_amount / er.rate AS NUMERIC(18,6))) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    CAST(date_trunc('month', pr.processed_at) AS date) AS month,
    SUM(CAST(pr.amount / er.rate AS NUMERIC(18,6))) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  formatDateTime(m.month, '%Y-%m') AS month_label,
  COALESCE(pa.gross_eur,0) AS payout_gross_eur,
  COALESCE(pa.fee_eur,0) AS payout_fee_eur,
  COALESCE(pa.net_eur,0) AS payout_net_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)) AS net_check_diff_eur,
  COALESCE(ra.refunds_eur,0) AS refunds_processed_eur,
  COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month NULLS LAST

Firebolt{ "errors": [ { "description": "Line 9, Column 29: 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": "603b8dbe-8fbd-4bcb-8872-9c16a691b916", "query_labe
DataFusionrow 1: col 1: "2023-01" vs "YYYY-MM"
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.gross_amount Decimal(14, 4) Decimal64(size = 0), __table5.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

The request, as Viktor received it

Report request from Yuki Tanaka (analyst for the Head of Data):

Title: Payout reconciliation to gross, fees and refunds
Question: Do payouts consistently reconcile as net = gross minus fee, and do refunds actually reduce what we pay out, or could refunds be double-counted or missing from the payout math?
Grain: calendar month, 2023-01 through 2025-12, based on payout paid_at month
Columns:
- month
- payout_gross_eur
- payout_fee_eur
- payout_net_eur
- net_check_diff_eur
- refunds_processed_eur
- net_minus_refunds_diff_eur
Details: Scope: all tenants combined. Group by month of payouts.paid_at (fall back to arrival_date if paid_at is null). payout_gross_eur = sum of payouts.gross_amount, converted to EUR. payout_fee_eur = sum of payouts.fee_amount, converted to EUR. payout_net_eur = sum of payouts.net_amount, converted to EUR. net_check_diff_eur = payout_net_eur minus (payout_gross_eur minus payout_fee_eur); should be ~0 if gross/fee/net are internally consistent. refunds_processed_eur = sum of payment_refunds.amount where status='succeeded', grouped by month of processed_at, converted to EUR. net_minus_refunds_diff_eur = payout_net_eur minus (payout_gross_eur minus payout_fee_eur minus refunds_processed_eur); tests whether refunds are already reflected in the payout gross/net figures. Convert all amounts via their own currency_code using exchange_rates (EUR amount = local / rate) as of the relevant date. Tables: payouts, payment_refunds, exchange_rates. Order by month ascending.

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

Viktor calls dry_run

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,
    SUM(p.gross_amount / er.rate) AS gross_eur,
    SUM(p.fee_amount / er.rate) AS fee_eur,
    SUM(p.net_amount / er.rate) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    date_trunc('month', pr.processed_at)::date AS month,
    SUM(pr.amount / er.rate) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  to_char(m.month, 'YYYY-MM') AS month,
  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,
  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,
  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,
  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Viktor calls dry_run

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,
    SUM(p.gross_amount / er.rate) AS gross_eur,
    SUM(p.fee_amount / er.rate) AS fee_eur,
    SUM(p.net_amount / er.rate) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    date_trunc('month', pr.processed_at)::date AS month,
    SUM(pr.amount / er.rate) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month,
  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,
  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,
  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,
  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, payout_gross_eur, payout_fee_eur, payout_net_eur, net_check_diff_eur, refunds_processed_eur, net_minus_refunds_diff_eur

ViktorQuery compiles with the expected 7 columns. Submitting.

Viktor calls submit

WITH months AS (
  SELECT DISTINCT month_start AS month
  FROM calendar_days
  WHERE month_start >= DATE '2023-01-01' AND month_start <= DATE '2025-12-01'
),
payout_agg AS (
  SELECT
    date_trunc('month', COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)))::date AS month,
    SUM(p.gross_amount / er.rate) AS gross_eur,
    SUM(p.fee_amount / er.rate) AS fee_eur,
    SUM(p.net_amount / er.rate) AS net_eur
  FROM payouts p
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = p.currency_code
   AND er.as_of = COALESCE(CAST(p.paid_at AS date), p.arrival_date)
  WHERE COALESCE(p.paid_at, CAST(p.arrival_date AS timestamp)) IS NOT NULL
  GROUP BY 1
),
refund_agg AS (
  SELECT
    date_trunc('month', pr.processed_at)::date AS month,
    SUM(pr.amount / er.rate) AS refunds_eur
  FROM payment_refunds pr
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = pr.currency_code
   AND er.as_of = CAST(pr.processed_at AS date)
  WHERE pr.status = 'succeeded'
  GROUP BY 1
)
SELECT
  strftime(m.month, '%Y-%m') AS month,
  ROUND(COALESCE(pa.gross_eur,0),2) AS payout_gross_eur,
  ROUND(COALESCE(pa.fee_eur,0),2) AS payout_fee_eur,
  ROUND(COALESCE(pa.net_eur,0),2) AS payout_net_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0)),2) AS net_check_diff_eur,
  ROUND(COALESCE(ra.refunds_eur,0),2) AS refunds_processed_eur,
  ROUND(COALESCE(pa.net_eur,0) - (COALESCE(pa.gross_eur,0) - COALESCE(pa.fee_eur,0) - COALESCE(ra.refunds_eur,0)),2) AS net_minus_refunds_diff_eur
FROM months m
LEFT JOIN payout_agg pa ON pa.month = m.month
LEFT JOIN refund_agg ra ON ra.month = m.month
ORDER BY m.month

The warehouse answers

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

ViktorSubmitted. Summary of what the report does: - **Grain:** one row per calendar month, 2023-01 through 2025-12 (36 rows, well under the 50-row cap). - **Payout figures:** gross/fee/net summed in each payout's own currency, converted to EUR via `exchange_rates` (EUR→local rate as of the payout date), grouped by month of `paid_at` (falling back to `arrival_date` when `paid_at` is null). - **net_check_diff_eur:** net − (gross − fee); this isolates any internal inconsistency in the payout row's own three numbers. - **refunds_processed_eur:** succeeded `payment_refunds` converted to EUR, grouped by month of `processed_at`. - **net_minus_refunds_diff_eur:** net − (gross − fee − refunds); a persistent nonzero value here suggests refunds aren't being netted into payouts (or are double-counted), which is exactly the question Yuki asked. One caveat flagged in the submit note: refunds are matched to payouts only by calendar month, since there's no direct FK from `payment_refunds` to `payouts` in the schema — so a gap in the last column could be timing lag rather than a true accounting hole.

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 (277 KB) · Yuki Tanaka's transcript (954 KB) · Rosa Van den Berg's transcript (262 KB) · the night's plan (6 KB) · the night's reports (19 KB) · the week's memo (3 KB)

Payout reconciliation to gross, fees and refunds

A business event lands on the Head of Data's desk

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Viktor writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q090

Everything, as recorded