Queries · q300 · Head of Data · week 2, night 1

Late-refund concentration and trend by merchant

“Is the refund-after-payout-settled problem concentrated in a handful of merchants, and did it get bigger or smaller in the second half of 2025 versus the first?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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 2
Subject: Good work — a few things I need pinned down before we certify

Yuki,

This is exactly the kind of digging I wanted — especially catching that invoices weren't a real fifth number, and that your first "20-40% gap" read was wrong and you went back to check it. That's the instinct I want on this team.

Three things surprised me and need explaining before I can tell Tobias we're ready:
1. Even with invoices out of the picture, order revenue, payments captured and payout gross still don't match each other (332,982 vs 323,026 vs 322,182 in your December example). You've explained orders-to-payments very well at the order level — but what explains that same gap when we look at it as one portfolio total for a month? Is it simply orders placed near month-end that haven't been paid yet, or something else?
2. The refund/payout traceability gap worries me more than you've flagged it. Before we loop in engineering, I want a sense of scale and shape: is this refund-timing effect spread evenly across all merchants, or concentrated in a few — because if it's concentrated, "we can't point to which payout absorbed it" becomes a real merchant-facing payout-support problem, not just a reporting footnote.
3. On customers — I don't want to just pick two numbers ourselves. I want to understand what each of those two counts is actually used for elsewhere in the business today, so we're not inventing a new definition the rest of the company doesn't already use.
Questions for this week:
- For the revenue gap between orders and money actually collected, at the portfolio level — can you tell me how much of it is pure timing (uncollected yet) versus something that never gets collected at all?
- For the refund/payout gap — is it concentrated in particular merchants or spread thinly across all of them, and does the size of it grow or shrink over the past year?
- Between the two customer counts, which one is already being used anywhere else in the business (support, billing, marketing), so we're not asking the board to adopt a brand-new definition?
- On the B2B receivables — rather than spending a slot rebuilding the merchant list this week, can you give me a rough sense of whether wholesalers are large enough a slice of revenue that leaving that line out of Friday's pack is actually a problem?
Keep the FX-adjusted view for USD/NOK on your list — that one's solid and the board will ask about it.

Thanks for the pace you're keeping on this.

Rosa
Yuki Tanaka plans the night
The analyst · week 2, night 1, request 2 of 3
Rosa's three questions this week are each really "is this timing, is this concentrated, and is this already solved elsewhere" - all testable with the right grain. (1) For the portfolio-level revenue gap, the order-level work showed <1-5% residual gap once refunded/partially_refunded payments are included, but that used orders aged 6+ months - I never actually tested whether that residual shrinks with age (timing) or is flat from day one (structural). An age-bucket grain since placed_at will show the ramp-up curve directly: if match rate keeps climbing past 90-180 days it's timing; if it plateaus early at e.g. 95-98% and stays there, that plateau is the real structural ceiling. (2) For refund/payout traceability, Rosa wants concentration AND trend together - I can get both from one grain (tenant) by comparing H1 vs H2 2025 late-refund euros per tenant, which also directly tests whether the problem is spreading or shrinking. (3) For the B2B sizing question, rather than rebuilding my fuzzy industry-text merchant list, I'm pulling revenue by industry vertical directly - this settles both "how big is B2B wholesale" and incidentally double-checks my prior ad hoc filter against the real industry categories. I'm holding the customer-definition-usage question (which other systems already use "ever purchased" vs "last 12 months") for tomorrow night, since it likely means digging into segments/cohorts/CRM definitions rather than a straightforward aggregate query, and I'd rather scope that precisely after seeing tonight's results.
Late-refund concentration and trend by merchant
Question
Is the refund-after-payout-settled problem concentrated in a handful of merchants, and did it get bigger or smaller in the second half of 2025 versus the first?
One row per
tenant (merchant)
Columns
- tenant_name
- h1_2025_late_refund_eur
- h2_2025_late_refund_eur
- change_pct
- total_2025_late_refund_eur
- pct_of_all_late_refunds_2025

Omar 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 late_refunds AS (
  SELECT pr.tenant_id, pr.processed_at, pr.amount, pr.currency_code
  FROM payment_refunds pr
  JOIN payout_items pi ON pi.payment_id = pr.payment_id
  JOIN payouts po ON po.id = pi.payout_id
  WHERE pr.status = 'succeeded'
    AND pr.processed_at >= DATE '2025-01-01'
    AND pr.processed_at < DATE '2026-01-01'
    AND pr.processed_at > po.paid_at
),
converted AS (
  SELECT
    lr.tenant_id,
    lr.processed_at,
    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount
         ELSE lr.amount / er.rate END AS eur_amount
  FROM late_refunds lr
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code
    AND er.as_of = CAST(lr.processed_at AS date)
),
tenant_totals AS (
  SELECT
    tenant_id,
    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,
    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur
  FROM converted
  GROUP BY tenant_id
),
totals AS (
  SELECT tenant_id,
    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,
    h1_2025_late_refund_eur, h2_2025_late_refund_eur
  FROM tenant_totals
),
grand_total AS (
  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals
)
SELECT
  t.name AS tenant_name,
  ROUND(tot.h1_2025_late_refund_eur, 2) AS h1_2025_late_refund_eur,
  ROUND(tot.h2_2025_late_refund_eur, 2) AS h2_2025_late_refund_eur,
  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL
       ELSE ROUND((tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100, 1)
  END AS change_pct,
  ROUND(tot.total_2025_late_refund_eur, 2) AS total_2025_late_refund_eur,
  ROUND(tot.total_2025_late_refund_eur / gt.grand_total_eur * 100, 1) AS pct_of_all_late_refunds_2025
FROM totals tot
JOIN tenants t ON t.id = tot.tenant_id
CROSS JOIN grand_total gt
ORDER BY tot.total_2025_late_refund_eur DESC, t.name ASC
LIMIT 20

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

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

“Submitted. The report computes late refunds (refunds succeeded in 2025 whose processed_at is after the settling payout's paid_at), splits them into H1/H2 2025 per tenant converted to EUR, and ranks the top 20 tenants by total late-refund value, with each tenant's share of the platform-wide 2025 late-refund total.”

The report comes back overnight

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

tenant_name                   | h1_2025_late_refund_eur | h2_2025_late_refund_eur | change_pct | total_2025_late_refund_eur | pct_of_all_late_refunds_2025
------------------------------+-------------------------+-------------------------+------------+----------------------------+-----------------------------
Cragsley Outfitters           |                67417.62 |                91799.89 |       36.2 |                  159217.51 |                         36.2
Ampfeld Elektronik            |                41642.32 |                25512.18 |      -38.7 |                    67154.5 |                         15.3
Marrowby & Vane               |                25503.42 |                31788.02 |       24.6 |                   57291.45 |                         13.0
Studio Vrij                   |                12799.75 |                 14737.3 |       15.1 |                   27537.05 |                          6.3
Cinder Peak Sports Co.        |                 6982.91 |                 8688.08 |       24.4 |                   15670.99 |                          3.6
Gratlinie Outdoor             |                 2400.54 |                 8178.94 |      240.7 |                   10579.48 |                          2.4
Ballycorran Industrial Supply |                 8496.84 |                  339.41 |      -96.0 |                    8836.25 |                          2.0
Rue Lafayette Studio          |                 2796.59 |                 2690.02 |       -3.8 |                    5486.61 |                          1.2
Velvet Ash                    |                  3256.5 |                 2227.21 |      -31.6 |                    5483.72 |                          1.2
Fell & Fjell                  |                  365.31 |                  5105.0 |     1297.5 |                    5470.31 |                          1.2
Midland Safety Wear           |                   10.44 |                 4736.58 |    45269.5 |                    4747.02 |                          1.1
Bytefield                     |                    NULL |                 4487.99 |       NULL |                    4487.99 |                          1.0
Trailmark                     |                 1907.49 |                 2018.13 |        5.8 |                    3925.62 |                          0.9
Ostwind Mode                  |                 1450.17 |                 2216.34 |       52.8 |                    3666.51 |                          0.8
Petalcourt Beauty             |                  862.03 |                 2728.32 |      216.5 |                    3590.34 |                          0.8
Fennel & Flint                |                  1591.2 |                 1914.73 |       20.3 |                    3505.93 |                          0.8
Strandgut                     |                 1143.18 |                 1879.35 |       64.4 |                    3022.53 |                          0.7
Kayak Corner                  |                 1796.16 |                  911.48 |      -49.3 |                    2707.64 |                          0.6
Linden & Loom                 |                 1273.36 |                 1388.23 |        9.0 |                    2661.59 |                          0.6
Oakhurst Tailoring            |                  874.18 |                  1728.6 |       97.7 |                    2602.78 |                          0.6
(20 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:
- q307Late-refund share as a percentage of each merchant's own refund volume
It builds on:
- q095Refund timing relative to payout settlement

It becomes benchmark query q300

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

spellingfetch first
determinismtie-breaker: tenant names are not unique
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
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 (6×)
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×)
placeholderfetch first

The benchmark text, against the submitted SQL

WITH late_refunds AS (  SELECT pr.tenant_id, pr.processed_at, pr.amount, pr.currency_code  SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at, pr.amount AS amount, pr.currency_code AS currency_code  FROM payment_refunds pr  JOIN payout_items pi ON pi.payment_id = pr.payment_id  JOIN payouts po ON po.id = pi.payout_id  WHERE pr.status = 'succeeded'    AND pr.processed_at >= DATE '2025-01-01'    AND pr.processed_at < DATE '2026-01-01'    AND pr.processed_at > po.paid_at),converted AS (  SELECT    lr.tenant_id,    lr.processed_at,    lr.tenant_id AS tenant_id,    lr.processed_at AS processed_at,    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount         ELSE lr.amount / er.rate END AS eur_amount         ELSE CAST(lr.amount / er.rate AS NUMERIC(18,6)) END AS eur_amount  FROM late_refunds lr  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code    AND er.as_of = CAST(lr.processed_at AS date)),tenant_totals AS (  SELECT    tenant_id,    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur  FROM converted  GROUP BY tenant_id),totals AS (  SELECT tenant_id,    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,    h1_2025_late_refund_eur, h2_2025_late_refund_eur  FROM tenant_totals),grand_total AS (  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals)SELECT  t.name AS tenant_name,  ROUND(tot.h1_2025_late_refund_eur, 2) AS h1_2025_late_refund_eur,  ROUND(tot.h2_2025_late_refund_eur, 2) AS h2_2025_late_refund_eur,  tot.h1_2025_late_refund_eur AS h1_2025_late_refund_eur,  tot.h2_2025_late_refund_eur AS h2_2025_late_refund_eur,  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL       ELSE ROUND((tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100, 1)       ELSE (tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100  END AS change_pct,  ROUND(tot.total_2025_late_refund_eur, 2) AS total_2025_late_refund_eur,  ROUND(tot.total_2025_late_refund_eur / gt.grand_total_eur * 100, 1) AS pct_of_all_late_refunds_2025  tot.total_2025_late_refund_eur AS total_2025_late_refund_eur,  tot.total_2025_late_refund_eur / gt.grand_total_eur * 100 AS pct_of_all_late_refunds_2025FROM totals totJOIN tenants t ON t.id = tot.tenant_idCROSS JOIN grand_total gtORDER BY tot.total_2025_late_refund_eur DESC, t.name ASCLIMIT 20ORDER BY tot.total_2025_late_refund_eur DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST{{fetch_first(20)}}

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

WITH late_refunds AS (
  SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at, pr.amount AS amount, pr.currency_code AS currency_code
  FROM payment_refunds pr
  JOIN payout_items pi ON pi.payment_id = pr.payment_id
  JOIN payouts po ON po.id = pi.payout_id
  WHERE pr.status = 'succeeded'
    AND pr.processed_at >= DATE '2025-01-01'
    AND pr.processed_at < DATE '2026-01-01'
    AND pr.processed_at > po.paid_at
),
converted AS (
  SELECT
    lr.tenant_id AS tenant_id,
    lr.processed_at AS processed_at,
    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount
         ELSE CAST(lr.amount / er.rate AS NUMERIC(18,6)) END AS eur_amount
  FROM late_refunds lr
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code
    AND er.as_of = CAST(lr.processed_at AS date)
),
tenant_totals AS (
  SELECT
    tenant_id,
    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,
    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur
  FROM converted
  GROUP BY tenant_id
),
totals AS (
  SELECT tenant_id,
    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,
    h1_2025_late_refund_eur, h2_2025_late_refund_eur
  FROM tenant_totals
),
grand_total AS (
  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals
)
SELECT
  t.name AS tenant_name,
  tot.h1_2025_late_refund_eur AS h1_2025_late_refund_eur,
  tot.h2_2025_late_refund_eur AS h2_2025_late_refund_eur,
  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL
       ELSE (tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100
  END AS change_pct,
  tot.total_2025_late_refund_eur AS total_2025_late_refund_eur,
  tot.total_2025_late_refund_eur / gt.grand_total_eur * 100 AS pct_of_all_late_refunds_2025
FROM totals tot
JOIN tenants t ON t.id = tot.tenant_id
CROSS JOIN grand_total gt
ORDER BY tot.total_2025_late_refund_eur DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST
FETCH FIRST 20 ROWS ONLY

WITH late_refunds AS (
  SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at, pr.amount AS amount, pr.currency_code AS currency_code
  FROM payment_refunds pr
  JOIN payout_items pi ON pi.payment_id = pr.payment_id
  JOIN payouts po ON po.id = pi.payout_id
  WHERE pr.status = 'succeeded'
    AND pr.processed_at >= DATE '2025-01-01'
    AND pr.processed_at < DATE '2026-01-01'
    AND pr.processed_at > po.paid_at
),
converted AS (
  SELECT
    lr.tenant_id AS tenant_id,
    lr.processed_at AS processed_at,
    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount
         ELSE CAST(lr.amount / er.rate AS NUMERIC(18,6)) END AS eur_amount
  FROM late_refunds lr
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code
    AND er.as_of = CAST(lr.processed_at AS date)
),
tenant_totals AS (
  SELECT
    tenant_id,
    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,
    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur
  FROM converted
  GROUP BY tenant_id
),
totals AS (
  SELECT tenant_id,
    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,
    h1_2025_late_refund_eur, h2_2025_late_refund_eur
  FROM tenant_totals
),
grand_total AS (
  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals
)
SELECT
  t.name AS tenant_name,
  tot.h1_2025_late_refund_eur AS h1_2025_late_refund_eur,
  tot.h2_2025_late_refund_eur AS h2_2025_late_refund_eur,
  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL
       ELSE (tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100
  END AS change_pct,
  tot.total_2025_late_refund_eur AS total_2025_late_refund_eur,
  tot.total_2025_late_refund_eur / gt.grand_total_eur * 100 AS pct_of_all_late_refunds_2025
FROM totals tot
JOIN tenants t ON t.id = tot.tenant_id
CROSS JOIN grand_total gt
ORDER BY tot.total_2025_late_refund_eur DESC NULLS FIRST, t.name ASC NULLS LAST, t.id NULLS LAST
LIMIT 20

Firebolt{ "errors": [ { "description": "Line 16, Column 30: 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": "d63b54cf-6485-4372-b18e-c5587fb27180", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.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. q300 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

Omar's whole session, 7 turns

The request, as Omar received it

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

Title: Late-refund concentration and trend by merchant
Question: Is the refund-after-payout-settled problem concentrated in a handful of merchants, and did it get bigger or smaller in the second half of 2025 versus the first?
Grain: tenant (merchant)
Columns:
- tenant_name
- h1_2025_late_refund_eur
- h2_2025_late_refund_eur
- change_pct
- total_2025_late_refund_eur
- pct_of_all_late_refunds_2025
Details: Population: payment_refunds with status='succeeded' and processed_at in 2025 (2025-01-01 to 2025-12-31) that are 'late' - i.e. processed_at is after the paid_at of the payout that settled the original payment (find the payout via payout_items.payment_id = payment_refunds.payment_id joined to payouts; exclude refunds whose payment has no matching payout_items row or where the refund is before/equal to the payout's paid_at). Group by tenant (join tenants on payment_refunds.tenant_id). h1_2025_late_refund_eur = sum for processed_at in Jan-Jun 2025, converted to EUR via exchange_rates using payment_refunds.currency_code and processed_at date. h2_2025_late_refund_eur = same for Jul-Dec 2025. change_pct = (h2 - h1) / h1 * 100, rounded to 1 decimal (NULL if h1 is 0). total_2025_late_refund_eur = h1 + h2. pct_of_all_late_refunds_2025 = total_2025_late_refund_eur / (sum of total_2025_late_refund_eur across ALL tenants with any late refunds in 2025, not just those shown) * 100, rounded to 1 decimal. Tables: payment_refunds, payout_items, payouts, tenants, exchange_rates. Show only the top 20 tenants by total_2025_late_refund_eur. Order by total_2025_late_refund_eur descending.

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

Omar calls dry_run

WITH late_refunds AS (
  SELECT pr.tenant_id, pr.processed_at, pr.amount, pr.currency_code
  FROM payment_refunds pr
  JOIN payout_items pi ON pi.payment_id = pr.payment_id
  JOIN payouts po ON po.id = pi.payout_id
  WHERE pr.status = 'succeeded'
    AND pr.processed_at >= DATE '2025-01-01'
    AND pr.processed_at < DATE '2026-01-01'
    AND pr.processed_at > po.paid_at
),
converted AS (
  SELECT
    lr.tenant_id,
    lr.processed_at,
    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount
         ELSE lr.amount / er.rate END AS eur_amount
  FROM late_refunds lr
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code
    AND er.as_of = CAST(lr.processed_at AS date)
),
tenant_totals AS (
  SELECT
    tenant_id,
    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,
    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur
  FROM converted
  GROUP BY tenant_id
),
totals AS (
  SELECT tenant_id,
    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,
    h1_2025_late_refund_eur, h2_2025_late_refund_eur
  FROM tenant_totals
),
grand_total AS (
  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals
)
SELECT
  t.name AS tenant_name,
  ROUND(tot.h1_2025_late_refund_eur, 2) AS h1_2025_late_refund_eur,
  ROUND(tot.h2_2025_late_refund_eur, 2) AS h2_2025_late_refund_eur,
  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL
       ELSE ROUND((tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100, 1)
  END AS change_pct,
  ROUND(tot.total_2025_late_refund_eur, 2) AS total_2025_late_refund_eur,
  ROUND(tot.total_2025_late_refund_eur / gt.grand_total_eur * 100, 1) AS pct_of_all_late_refunds_2025
FROM totals tot
JOIN tenants t ON t.id = tot.tenant_id
CROSS JOIN grand_total gt
ORDER BY tot.total_2025_late_refund_eur DESC, t.name ASC
LIMIT 20

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, h1_2025_late_refund_eur, h2_2025_late_refund_eur, change_pct, total_2025_late_refund_eur, pct_of_all_late_refunds_2025

OmarQuery compiles and returns the requested columns. Submitting.

Omar calls submit

WITH late_refunds AS (
  SELECT pr.tenant_id, pr.processed_at, pr.amount, pr.currency_code
  FROM payment_refunds pr
  JOIN payout_items pi ON pi.payment_id = pr.payment_id
  JOIN payouts po ON po.id = pi.payout_id
  WHERE pr.status = 'succeeded'
    AND pr.processed_at >= DATE '2025-01-01'
    AND pr.processed_at < DATE '2026-01-01'
    AND pr.processed_at > po.paid_at
),
converted AS (
  SELECT
    lr.tenant_id,
    lr.processed_at,
    CASE WHEN lr.currency_code = 'EUR' THEN lr.amount
         ELSE lr.amount / er.rate END AS eur_amount
  FROM late_refunds lr
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = lr.currency_code
    AND er.as_of = CAST(lr.processed_at AS date)
),
tenant_totals AS (
  SELECT
    tenant_id,
    SUM(eur_amount) FILTER (WHERE processed_at < DATE '2025-07-01') AS h1_2025_late_refund_eur,
    SUM(eur_amount) FILTER (WHERE processed_at >= DATE '2025-07-01') AS h2_2025_late_refund_eur
  FROM converted
  GROUP BY tenant_id
),
totals AS (
  SELECT tenant_id,
    COALESCE(h1_2025_late_refund_eur,0) + COALESCE(h2_2025_late_refund_eur,0) AS total_2025_late_refund_eur,
    h1_2025_late_refund_eur, h2_2025_late_refund_eur
  FROM tenant_totals
),
grand_total AS (
  SELECT SUM(total_2025_late_refund_eur) AS grand_total_eur FROM totals
)
SELECT
  t.name AS tenant_name,
  ROUND(tot.h1_2025_late_refund_eur, 2) AS h1_2025_late_refund_eur,
  ROUND(tot.h2_2025_late_refund_eur, 2) AS h2_2025_late_refund_eur,
  CASE WHEN COALESCE(tot.h1_2025_late_refund_eur,0) = 0 THEN NULL
       ELSE ROUND((tot.h2_2025_late_refund_eur - tot.h1_2025_late_refund_eur) / tot.h1_2025_late_refund_eur * 100, 1)
  END AS change_pct,
  ROUND(tot.total_2025_late_refund_eur, 2) AS total_2025_late_refund_eur,
  ROUND(tot.total_2025_late_refund_eur / gt.grand_total_eur * 100, 1) AS pct_of_all_late_refunds_2025
FROM totals tot
JOIN tenants t ON t.id = tot.tenant_id
CROSS JOIN grand_total gt
ORDER BY tot.total_2025_late_refund_eur DESC, t.name ASC
LIMIT 20

The warehouse answers

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

OmarSubmitted. The report computes late refunds (refunds succeeded in 2025 whose `processed_at` is after the settling payout's `paid_at`), splits them into H1/H2 2025 per tenant converted to EUR, and ranks the top 20 tenants by total late-refund value, with each tenant's share of the platform-wide 2025 late-refund total.

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

Omar's transcript (269 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 (7 KB) · the week's memo (3 KB)

Late-refund concentration and trend by merchant

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

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q300

Everything, as recorded