Queries · q024 · Chief Financial Officer · week 1, night 4

Refund Figures Cross-Check: Payments vs Order Returns, 2025

“Do the payments-table refund amounts we've been reporting agree with the independent order_returns (RMA) records, or is one source overstating/understating refunds?”

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 SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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

Tobias Lindqvist writes to Kofi Mensah
The manager, at the start of week 1
Subject: Finding my bearings – first set of questions for the week

Hi Kofi,

Good to be working with you. I'm still building my mental map of the business, so I'd rather ask a few broad questions well than a lot of narrow ones badly. Given the slot budget, here's what I'd prioritize this week, roughly in order:
1. How did we actually do in 2025, and where is the money coming from? I want to see our own revenue split between the subscription/plan fees and the payment-processing fees, over the year, and ideally how that's trending month to month. This is the backbone of the annual accounts narrative and will shape how I talk about 2025 to the board.
2. How concentrated is our business in a few large merchants, and how healthy are they? I keep hearing "a handful of large merchants drive most of the volume" — I'd like to understand who they are, how much of our gross volume and revenue they represent, and whether that concentration is growing or shrinking. This matters for risk assessment going into the budget.
3. What do refunds and returns look like across the merchant base? I want a sense of refund rates — overall and whether certain verticals or merchants are outliers — since this affects both our net revenue and how much cash we actually end up paying out.
4. How exposed are we to currency movements? Merchants bill in their own currencies, and shoppers come from ~40 countries. I'd like a picture of how our volume and revenue break down by currency, so I can gauge our FX exposure for the 2026 budget and think about whether we need any hedging conversation.
5. Are our B2B merchants paying us and their own customers on time? I know a few of our merchants are wholesalers running on B2B terms. I'd like to understand how their receivables look — how much is outstanding and how overdue it tends to get — since this could be a real cash-flow risk if it's worse than I'd expect.
For this week, let's focus on #1 and #2 — those are the two I most need before I can speak credibly about 2025 and the 2026 budget. If there's room, #3 would be next; #4 and #5 can wait for a follow-up week if slots are tight.

Let me know if any of these need me to narrow scope further before you brief the data engineers.

Thanks, Tobias
Kofi Mensah plans the night
The analyst · week 1, night 4, request 3 of 3
Report 1 confirms the fix: the corrected monthly series now ties exactly to the independently-verified 2025 annual totals (€3,972,987.99 gross / €93,583.20 fee / 2.4% blended fee rate), and payout counts roughly doubled vs. the original buggy report — consistent with it having silently dropped close to half of all payouts for a reason we never fully root-caused, but which is now moot since the corrected figures cross-foot cleanly. #1 is now safe to put in front of Tobias, with the one caveat I'll carry into the memo: nothing in this warehouse captures Tidewick's own platform-plan billing of merchants (the plans/subscriptions/invoices tables here model each merchant's billing of their customers, not Tidewick's billing of the merchant) — so I can only report the processing-fee half of the revenue split, not the plan-fee half.

On refunds, I did the arithmetic behind Report 2 and 3 by hand: subtracting Apparel's numbers from the company totals shows the non-Apparel refund rate was stable at ~2-3% through 2024, but spiked to 4.9% in 2025-Q2 and 9.1% in 2025-Q3 before dropping back to 4.2% in Q4 — a 3-4x jump that Apparel's own flat/improving trend doesn't explain. Apparel itself looks structural (19-26% every quarter, actually lowest in the latest quarter), so that part of #3 is answered and not worsening. But this non-Apparel spike is new information worth chasing on this last night before the memo, since it could be a specific merchant issue (e.g. a product recall or quality problem) rather than normal seasonal variation. I'm spending two slots to localize it (by vertical, then by merchant) and a third to cross-check the payments-based refund figures against the independent order_returns table, given how costly the earlier monthly-revenue bug was — I don't want to hand Tobias a refund narrative built on a single, unverified source.
Refund Figures Cross-Check: Payments vs Order Returns, 2025
Question
Do the payments-table refund amounts we've been reporting agree with the independent order_returns (RMA) records, or is one source overstating/understating refunds?
One row per
one calendar quarter, 2025
Columns
- quarter
- amount_refunded_eur_from_payments
- refund_total_eur_from_order_returns
- difference_eur
- difference_pct_of_payments_figure

Grace 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 quarters as (
  select distinct quarter_start, year_num, quarter_num
  from calendar_days
  where year_num = 2025
),
payments_in_2025 as (
  select
    date_trunc('quarter', p.captured_at)::date as quarter_start,
    p.amount_refunded / er.rate as amount_refunded_eur
  from payments p
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = p.currency_code
   and er.as_of = date(p.captured_at)
  where p.captured_at >= date '2025-01-01'
    and p.captured_at < date '2026-01-01'
),
payments_by_quarter as (
  select quarter_start, sum(amount_refunded_eur) as amount_refunded_eur_from_payments
  from payments_in_2025
  group by quarter_start
),
returns_in_2025 as (
  select
    date_trunc('quarter', r.refunded_at)::date as quarter_start,
    r.refund_total / er.rate as refund_total_eur
  from order_returns r
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = r.currency_code
   and er.as_of = date(r.refunded_at)
  where r.status = 'refunded'
    and r.refunded_at >= date '2025-01-01'
    and r.refunded_at < date '2026-01-01'
),
returns_by_quarter as (
  select quarter_start, sum(refund_total_eur) as refund_total_eur_from_order_returns
  from returns_in_2025
  group by quarter_start
),
combined as (
  select
    q.quarter_start,
    q.year_num,
    q.quarter_num,
    coalesce(p.amount_refunded_eur_from_payments, 0) as amount_refunded_eur_from_payments,
    coalesce(r.refund_total_eur_from_order_returns, 0) as refund_total_eur_from_order_returns
  from quarters q
  left join payments_by_quarter p on p.quarter_start = q.quarter_start
  left join returns_by_quarter r on r.quarter_start = q.quarter_start
)
select
  year_num || '-Q' || quarter_num as quarter,
  round(amount_refunded_eur_from_payments, 2) as amount_refunded_eur_from_payments,
  round(refund_total_eur_from_order_returns, 2) as refund_total_eur_from_order_returns,
  round(amount_refunded_eur_from_payments - refund_total_eur_from_order_returns, 2) as difference_eur,
  round(
    case when amount_refunded_eur_from_payments = 0 then null
    else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100
    end, 2
  ) as difference_pct_of_payments_figure
from combined
order by year_num, quarter_num

“Query compiles and returns the expected columns. Submitting.”

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

“Submitted. The report computes, for each of 2025-Q1 through Q4, the EUR-converted refund totals from payments.amount_refunded (by captured_at) and order_returns.refund_total (status='refunded', by refunded_at), each converted using the matching daily EUR exchange rate per currency, plus the absolute and percentage difference between the two sources.”

The report comes back overnight

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

quarter | amount_refunded_eur_from_payments | refund_total_eur_from_order_returns | difference_eur | difference_pct_of_payments_figure
--------+-----------------------------------+-------------------------------------+----------------+----------------------------------
2025-Q1 |                          53953.48 |                            74426.38 |       -20472.9 |                            -37.95
2025-Q2 |                          64609.26 |                            54413.77 |       10195.49 |                             15.78
2025-Q3 |                          78289.11 |                            46541.26 |       31747.85 |                             40.55
2025-Q4 |                         110674.65 |                            134322.6 |      -23647.95 |                            -21.37
(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:
- q221Refund Attribution Lag: Purchase Quarter vs Refund Quarter, 2025
It builds on:
- q020Company-Wide Refund Rate Trend by Quarter, 2024-2025

It becomes benchmark query q024

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

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

The benchmark text, against the submitted SQL

with quarters as (  select distinct quarter_start, year_num, quarter_num  from calendar_days  where year_num = 2025),payments_in_2025 as (  select    date_trunc('quarter', p.captured_at)::date as quarter_start,    p.amount_refunded / er.rate as amount_refunded_eur    CAST(date_trunc('quarter', p.captured_at) AS date) as quarter_start,    CAST(p.amount_refunded / er.rate AS NUMERIC(18,6)) as amount_refunded_eur  from payments p  join exchange_rates er    on er.base_code = 'EUR'   and er.quote_code = p.currency_code   and er.as_of = date(p.captured_at)   and er.as_of = CAST(p.captured_at AS DATE)  where p.captured_at >= date '2025-01-01'    and p.captured_at < date '2026-01-01'),payments_by_quarter as (  select quarter_start, sum(amount_refunded_eur) as amount_refunded_eur_from_payments  from payments_in_2025  group by quarter_start),returns_in_2025 as (  select    date_trunc('quarter', r.refunded_at)::date as quarter_start,    r.refund_total / er.rate as refund_total_eur    CAST(date_trunc('quarter', r.refunded_at) AS date) as quarter_start,    CAST(r.refund_total / er.rate AS NUMERIC(18,6)) as refund_total_eur  from order_returns r  join exchange_rates er    on er.base_code = 'EUR'   and er.quote_code = r.currency_code   and er.as_of = date(r.refunded_at)   and er.as_of = CAST(r.refunded_at AS DATE)  where r.status = 'refunded'    and r.refunded_at >= date '2025-01-01'    and r.refunded_at < date '2026-01-01'),returns_by_quarter as (  select quarter_start, sum(refund_total_eur) as refund_total_eur_from_order_returns  from returns_in_2025  group by quarter_start),combined as (  select    q.quarter_start,    q.year_num,    q.quarter_num,    q.quarter_start AS quarter_start,    q.year_num AS year_num,    q.quarter_num AS quarter_num,    coalesce(p.amount_refunded_eur_from_payments, 0) as amount_refunded_eur_from_payments,    coalesce(r.refund_total_eur_from_order_returns, 0) as refund_total_eur_from_order_returns  from quarters q  left join payments_by_quarter p on p.quarter_start = q.quarter_start  left join returns_by_quarter r on r.quarter_start = q.quarter_start)select  year_num || '-Q' || quarter_num as quarter,  round(amount_refunded_eur_from_payments, 2) as amount_refunded_eur_from_payments,  round(refund_total_eur_from_order_returns, 2) as refund_total_eur_from_order_returns,  round(amount_refunded_eur_from_payments - refund_total_eur_from_order_returns, 2) as difference_eur,  round(    case when amount_refunded_eur_from_payments = 0 then null    else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100    end, 2  ) as difference_pct_of_payments_figure  amount_refunded_eur_from_payments as amount_refunded_eur_from_payments,  refund_total_eur_from_order_returns as refund_total_eur_from_order_returns,  amount_refunded_eur_from_payments - refund_total_eur_from_order_returns as difference_eur,  case when amount_refunded_eur_from_payments = 0 then null  else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100  end as difference_pct_of_payments_figurefrom combinedorder by year_num, quarter_numorder by year_num NULLS LAST, quarter_num NULLS LAST, quarter_start NULLS LAST

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

with quarters as (
  select distinct quarter_start, year_num, quarter_num
  from calendar_days
  where year_num = 2025
),
payments_in_2025 as (
  select
    CAST(date_trunc('quarter', p.captured_at) AS date) as quarter_start,
    CAST(p.amount_refunded / er.rate AS NUMERIC(18,6)) as amount_refunded_eur
  from payments p
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = p.currency_code
   and er.as_of = CAST(p.captured_at AS DATE)
  where p.captured_at >= date '2025-01-01'
    and p.captured_at < date '2026-01-01'
),
payments_by_quarter as (
  select quarter_start, sum(amount_refunded_eur) as amount_refunded_eur_from_payments
  from payments_in_2025
  group by quarter_start
),
returns_in_2025 as (
  select
    CAST(date_trunc('quarter', r.refunded_at) AS date) as quarter_start,
    CAST(r.refund_total / er.rate AS NUMERIC(18,6)) as refund_total_eur
  from order_returns r
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = r.currency_code
   and er.as_of = CAST(r.refunded_at AS DATE)
  where r.status = 'refunded'
    and r.refunded_at >= date '2025-01-01'
    and r.refunded_at < date '2026-01-01'
),
returns_by_quarter as (
  select quarter_start, sum(refund_total_eur) as refund_total_eur_from_order_returns
  from returns_in_2025
  group by quarter_start
),
combined as (
  select
    q.quarter_start AS quarter_start,
    q.year_num AS year_num,
    q.quarter_num AS quarter_num,
    coalesce(p.amount_refunded_eur_from_payments, 0) as amount_refunded_eur_from_payments,
    coalesce(r.refund_total_eur_from_order_returns, 0) as refund_total_eur_from_order_returns
  from quarters q
  left join payments_by_quarter p on p.quarter_start = q.quarter_start
  left join returns_by_quarter r on r.quarter_start = q.quarter_start
)
select
  year_num || '-Q' || quarter_num as quarter,
  amount_refunded_eur_from_payments as amount_refunded_eur_from_payments,
  refund_total_eur_from_order_returns as refund_total_eur_from_order_returns,
  amount_refunded_eur_from_payments - refund_total_eur_from_order_returns as difference_eur,
  case when amount_refunded_eur_from_payments = 0 then null
  else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100
  end as difference_pct_of_payments_figure
from combined
order by year_num NULLS LAST, quarter_num NULLS LAST, quarter_start NULLS LAST

Firebolt{ "errors": [ { "description": "Line 9, Column 28: 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": "d5cac349-a5a6-4919-bfcf-622b8a095d50", "query_labe
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table6.amount_refunded 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. q024 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

Grace's whole session, 7 turns

The request, as Grace received it

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

Title: Refund Figures Cross-Check: Payments vs Order Returns, 2025
Question: Do the payments-table refund amounts we've been reporting agree with the independent order_returns (RMA) records, or is one source overstating/understating refunds?
Grain: one calendar quarter, 2025
Columns:
- quarter
- amount_refunded_eur_from_payments
- refund_total_eur_from_order_returns
- difference_eur
- difference_pct_of_payments_figure
Details: Compute two independent quarterly totals for 2025, both converted to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=date of the relevant event, dividing by the matched rate): (1) sum of payments.amount_refunded for payments with captured_at in that quarter (same definition used in this week's other refund reports); (2) sum of order_returns.refund_total for returns with status='refunded' and refunded_at in that quarter. Report both totals side by side per quarter along with the absolute and percentage difference between them. Order chronologically 2025-Q1 to 2025-Q4 (4 rows).

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

Grace calls dry_run

with quarters as (
  select distinct quarter_start, year_num, quarter_num
  from calendar_days
  where year_num = 2025
),
payments_in_2025 as (
  select
    date_trunc('quarter', p.captured_at)::date as quarter_start,
    p.amount_refunded / er.rate as amount_refunded_eur
  from payments p
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = p.currency_code
   and er.as_of = date(p.captured_at)
  where p.captured_at >= date '2025-01-01'
    and p.captured_at < date '2026-01-01'
),
payments_by_quarter as (
  select quarter_start, sum(amount_refunded_eur) as amount_refunded_eur_from_payments
  from payments_in_2025
  group by quarter_start
),
returns_in_2025 as (
  select
    date_trunc('quarter', r.refunded_at)::date as quarter_start,
    r.refund_total / er.rate as refund_total_eur
  from order_returns r
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = r.currency_code
   and er.as_of = date(r.refunded_at)
  where r.status = 'refunded'
    and r.refunded_at >= date '2025-01-01'
    and r.refunded_at < date '2026-01-01'
),
returns_by_quarter as (
  select quarter_start, sum(refund_total_eur) as refund_total_eur_from_order_returns
  from returns_in_2025
  group by quarter_start
),
combined as (
  select
    q.quarter_start,
    q.year_num,
    q.quarter_num,
    coalesce(p.amount_refunded_eur_from_payments, 0) as amount_refunded_eur_from_payments,
    coalesce(r.refund_total_eur_from_order_returns, 0) as refund_total_eur_from_order_returns
  from quarters q
  left join payments_by_quarter p on p.quarter_start = q.quarter_start
  left join returns_by_quarter r on r.quarter_start = q.quarter_start
)
select
  year_num || '-Q' || quarter_num as quarter,
  round(amount_refunded_eur_from_payments, 2) as amount_refunded_eur_from_payments,
  round(refund_total_eur_from_order_returns, 2) as refund_total_eur_from_order_returns,
  round(amount_refunded_eur_from_payments - refund_total_eur_from_order_returns, 2) as difference_eur,
  round(
    case when amount_refunded_eur_from_payments = 0 then null
    else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100
    end, 2
  ) as difference_pct_of_payments_figure
from combined
order by year_num, quarter_num

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: quarter, amount_refunded_eur_from_payments, refund_total_eur_from_order_returns, difference_eur, difference_pct_of_payments_figure

GraceQuery compiles and returns the expected columns. Submitting.

Grace calls submit

with quarters as (
  select distinct quarter_start, year_num, quarter_num
  from calendar_days
  where year_num = 2025
),
payments_in_2025 as (
  select
    date_trunc('quarter', p.captured_at)::date as quarter_start,
    p.amount_refunded / er.rate as amount_refunded_eur
  from payments p
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = p.currency_code
   and er.as_of = date(p.captured_at)
  where p.captured_at >= date '2025-01-01'
    and p.captured_at < date '2026-01-01'
),
payments_by_quarter as (
  select quarter_start, sum(amount_refunded_eur) as amount_refunded_eur_from_payments
  from payments_in_2025
  group by quarter_start
),
returns_in_2025 as (
  select
    date_trunc('quarter', r.refunded_at)::date as quarter_start,
    r.refund_total / er.rate as refund_total_eur
  from order_returns r
  join exchange_rates er
    on er.base_code = 'EUR'
   and er.quote_code = r.currency_code
   and er.as_of = date(r.refunded_at)
  where r.status = 'refunded'
    and r.refunded_at >= date '2025-01-01'
    and r.refunded_at < date '2026-01-01'
),
returns_by_quarter as (
  select quarter_start, sum(refund_total_eur) as refund_total_eur_from_order_returns
  from returns_in_2025
  group by quarter_start
),
combined as (
  select
    q.quarter_start,
    q.year_num,
    q.quarter_num,
    coalesce(p.amount_refunded_eur_from_payments, 0) as amount_refunded_eur_from_payments,
    coalesce(r.refund_total_eur_from_order_returns, 0) as refund_total_eur_from_order_returns
  from quarters q
  left join payments_by_quarter p on p.quarter_start = q.quarter_start
  left join returns_by_quarter r on r.quarter_start = q.quarter_start
)
select
  year_num || '-Q' || quarter_num as quarter,
  round(amount_refunded_eur_from_payments, 2) as amount_refunded_eur_from_payments,
  round(refund_total_eur_from_order_returns, 2) as refund_total_eur_from_order_returns,
  round(amount_refunded_eur_from_payments - refund_total_eur_from_order_returns, 2) as difference_eur,
  round(
    case when amount_refunded_eur_from_payments = 0 then null
    else (amount_refunded_eur_from_payments - refund_total_eur_from_order_returns) / amount_refunded_eur_from_payments * 100
    end, 2
  ) as difference_pct_of_payments_figure
from combined
order by year_num, quarter_num

The warehouse answers

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

GraceSubmitted. The report computes, for each of 2025-Q1 through Q4, the EUR-converted refund totals from `payments.amount_refunded` (by `captured_at`) and `order_returns.refund_total` (status='refunded', by `refunded_at`), each converted using the matching daily EUR exchange rate per currency, plus the absolute and percentage difference between the two sources.

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

Grace's transcript (268 KB) · Kofi Mensah's transcript (906 KB) · Tobias Lindqvist's transcript (269 KB) · the night's plan (5 KB) · the night's reports (14 KB) · the week's memo (3 KB)

Refund Figures Cross-Check: Payments vs Order Returns, 2025

Tobias Lindqvist writes to Kofi Mensah

Kofi Mensah plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q024

Everything, as recorded