Queries · q209 · Chief Executive Officer · week 2, night 1
“Is the Q4 2025 refund-rate increase concentrated in a handful of merchants, or spread broadly across the merchant base?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2; shared with Tobias Lindqvist, Chief Financial Officer
The board meets at the end of January and wants a clear view of unit economics: per merchant, vertical and country, what Tidewick earns and what it costs us to serve them. Which merchants are worth growing, and which cost more than they bring?
The manager, at the start of week 2
Subject: Great first week — here's where I want to go next
Hannah,
This was exactly the kind of grounding I needed — thank you. A few things surprised me and one big new ask from the board that I need us to lead together with Tobias.
What surprised me / needs explaining:
The new board ask — unit economics by merchant, vertical and country:
The board wants to know, plainly, what we earn from serving each merchant/vertical/country and what it costs us — so we can say which parts of the business are worth leaning into and which are quietly expensive. This is bigger than one report, so here's what I think it needs, and I want you and Tobias to scope it jointly this week rather than me prescribing the method:
My questions for this week:
Let's talk briefly before you lock in this week's requests so we spend the slots on the unit economics scoping rather than re-running what we already mostly know.
Thanks, Maren
Note to Hannah Brooks
Hannah — a few things from the finance side as you scope the board unit-economics piece:
If there's room, a rough read on how refund patterns differ by vertical or country would help round out the "which merchants are worth growing" question.
Happy to talk through before Friday's memo.
— Tobias
The analyst · week 2, night 1, request 2 of 3
Top priority tonight is giving Maren and Tobias's team concrete evidence to reconcile the revenue numbers, rather than just re-asserting my figure. I've cross-checked internally: my €53,186 fee revenue / €1,991,449 gross volume for 2025 is consistent across two independent reports (merchant-level and corrected vertical-level) built with an explicit, auditable method (status='paid', exact-date EUR conversion). Notably, Kofi's €93,583 figure is almost exactly 1.76x mine — the same ratio as the original buggy vertical report I caught and fixed in week 1 — which strongly suggests his team's query has a similar join fan-out (likely against exchange_rates or a one-to-many table), not a genuine methodology difference. A clean currency-level breakdown tonight will (a) give me hard evidence to bring to a joint session with Tobias's team, and (b) double as the FX-exposure view Tobias asked for. I'm also using tonight's other two slots on items Maren explicitly wants resolved this week outside the bigger unit-economics scoping: testing whether the refund uptick is concentrated (per Kofi's claim) or broad, and building the "accounts to watch" risk snapshot (which also answers Tobias's receivables-aging question). I'm deliberately not starting the cost-to-serve/unit-economics build yet — that needs a design conversation with Tobias before I spend slots on it.
The engineer · 1 dry run on the empty schema, then the submission
WITH t1 AS ( SELECT t10.tenant_id AS tenant_id, SUM(t10.amount / t11.rate) AS captured_eur_q4, COUNT(DISTINCT t10.order_id) AS order_count_q4 FROM payments AS t10 JOIN exchange_rates AS t11 ON t11.base_code = 'EUR' AND t11.quote_code = t10.currency_code AND t11.as_of = CAST(t10.captured_at AS date) WHERE t10.status IN ('captured', 'partially_refunded', 'refunded') AND t10.captured_at >= DATE '2025-10-01' AND t10.captured_at < DATE '2026-01-01' GROUP BY t10.tenant_id ), t2 AS ( SELECT t20.tenant_id AS tenant_id, SUM(t20.amount / t21.rate) AS captured_eur_baseline FROM payments AS t20 JOIN exchange_rates AS t21 ON t21.base_code = 'EUR' AND t21.quote_code = t20.currency_code AND t21.as_of = CAST(t20.captured_at AS date) WHERE t20.status IN ('captured', 'partially_refunded', 'refunded') AND t20.captured_at >= DATE '2025-01-01' AND t20.captured_at < DATE '2025-10-01' GROUP BY t20.tenant_id ), t3 AS ( SELECT t30.tenant_id AS tenant_id, SUM(t30.amount / t31.rate) AS refund_eur_q4 FROM payment_refunds AS t30 JOIN exchange_rates AS t31 ON t31.base_code = 'EUR' AND t31.quote_code = t30.currency_code AND t31.as_of = CAST(t30.processed_at AS date) WHERE t30.status = 'succeeded' AND t30.processed_at >= DATE '2025-10-01' AND t30.processed_at < DATE '2026-01-01' GROUP BY t30.tenant_id ), t4 AS ( SELECT t40.tenant_id AS tenant_id, SUM(t40.amount / t41.rate) AS refund_eur_baseline FROM payment_refunds AS t40 JOIN exchange_rates AS t41 ON t41.base_code = 'EUR' AND t41.quote_code = t40.currency_code AND t41.as_of = CAST(t40.processed_at AS date) WHERE t40.status = 'succeeded' AND t40.processed_at >= DATE '2025-01-01' AND t40.processed_at < DATE '2025-10-01' GROUP BY t40.tenant_id ) SELECT t0.name AS merchant_name, t0.industry AS vertical, ROUND(100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0), 2) AS refund_rate_q4_pct, ROUND(100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0), 2) AS refund_rate_baseline_pct, ROUND( (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) , 2) AS change_vs_baseline_pp, ROUND(COALESCE(t3.refund_eur_q4, 0), 2) AS refund_amount_q4_eur, t1.order_count_q4 AS order_count_q4 FROM tenants AS t0 JOIN t1 ON t1.tenant_id = t0.id LEFT JOIN t2 ON t2.tenant_id = t0.id LEFT JOIN t3 ON t3.tenant_id = t0.id LEFT JOIN t4 ON t4.tenant_id = t0.id WHERE t1.order_count_q4 >= 10 ORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC, t0.id ASC
“Query compiles cleanly. Submitting now.”
“Submitted. Summary of approach:
- Grain: one row per tenant/merchant, filtered to those with ≥10 qualifying orders in Q4 2025.
- Numerators/denominators: computed as four separate CTEs (Q4 captured EUR + order count, Q1–Q3 captured EUR, Q4 refund EUR, Q1–Q3 refund EUR), each converting local currency to EUR via exchange_rates joined on as_of = cast(date, …).
- Rates & change: refund_rate_q4_pct, refund_rate_baseline_pct, and change_vs_baseline_pp (the sort key, descending, tie-broken by Q4 refund EUR then tenant id for determinism).
- Deviation noted to the analyst: used the tenant_id columns already present on payments/payment_refunds directly rather than routing through orders, since both tables carry it natively — same attribution, simpler join. Baseline-less merchants get a null rate/change and sort last.”
As Hannah Brooks saw it, from the small dataset the agents worked on
merchant_name | vertical | refund_rate_q4_pct | refund_rate_baseline_pct | change_vs_baseline_pp | refund_amount_q4_eur | order_count_q4 -----------------------+---------------------------+--------------------+--------------------------+-----------------------+----------------------+--------------- Fell & Fjell | Sports & Outdoor | 83.02 | 3.38 | 79.64 | 4888.59 | 17 Bytefield | Consumer Electronics | 52.58 | 0.0 | 52.58 | 4487.99 | 11 Brick & Bobbin | Apparel & Footwear | 50.06 | 32.79 | 17.27 | 612.92 | 10 Lamplight Living | Home & Living | 20.44 | 6.9 | 13.54 | 494.36 | 20 Saffron & Sage | Beauty | 11.76 | 3.56 | 8.21 | 554.55 | 33 Feather & Fin | Pet Supplies | 8.76 | 1.88 | 6.88 | 290.54 | 48 Cinder Peak Sports Co. | Sports & Outdoor | 18.23 | 11.42 | 6.81 | 6920.67 | 105 Willow & Wren | Home & Living | 16.43 | 11.29 | 5.14 | 929.47 | 33 Purrfect Pantry | Pet Supplies | 6.62 | 2.38 | 4.24 | 54.47 | 17 Rosehip Lab | Beauty | 3.17 | 0.0 | 3.17 | 44.48 | 16 Hus & Hem | Home & Living | 9.48 | 6.81 | 2.66 | 172.0 | 15 Grind Collective | Food & Coffee | 3.16 | 0.74 | 2.43 | 367.54 | 166 Petalcourt Beauty | Beauty | 8.46 | 6.83 | 1.63 | 1775.91 | 250 The Balm Room | Beauty | 6.49 | 5.16 | 1.33 | 276.16 | 32 Oakhurst Tailoring | Apparel & Footwear | 14.91 | 13.66 | 1.25 | 2106.35 | 83 Nordic Bloom Cosmetics | Beauty | 8.67 | 7.62 | 1.05 | 468.27 | 45 Castell Fasteners | Industrial Supplies (B2B) | 0.86 | 0.23 | 0.63 | 885.79 | 47 Roast Republic | Food & Coffee | 0.0 | 0.0 | 0.0 | 0.0 | 21 Deskline Supplies | Office Supplies (B2B) | 0.0 | 0.37 | -0.37 | 0.0 | 20 Paperplane Press | Books & Stationery | 2.35 | 3.11 | -0.77 | 16.07 | 15 Kibblewick | Pet Supplies | 0.3 | 1.14 | -0.84 | 28.98 | 100 Tiny Atelier | Home & Living | 5.86 | 7.44 | -1.58 | 79.96 | 10 Honeycomb Larder | Food & Coffee | 0.06 | 1.81 | -1.75 | 6.21 | 91 Marrowby & Vane | Apparel & Footwear | 21.07 | 23.13 | -2.06 | 24695.74 | 642 Coastline Interiors | Home & Living | 6.07 | 8.39 | -2.31 | 548.01 | 67 Tail Wag Supply | Pet Supplies | 0.0 | 2.37 | -2.37 | 0.0 | 29 Cragsley Outfitters | Apparel & Footwear | 22.56 | 25.05 | -2.5 | 70042.32 | 1413 Fennel & Flint | Home & Living | 4.19 | 7.45 | -3.26 | 1288.42 | 173 Fjordline Wear | Apparel & Footwear | 25.8 | 29.07 | -3.27 | 851.8 | 24 Juniper Apothecary | Beauty | 2.5 | 6.78 | -4.28 | 153.65 | 55 Folio & Fern | Books & Stationery | 2.39 | 7.04 | -4.65 | 24.39 | 22 Stoneware Studio | Home & Living | 0.0 | 5.75 | -5.75 | 0.0 | 12 Kayak Corner | Sports & Outdoor | 3.36 | 9.76 | -6.39 | 259.69 | 22 Thistlecombe | Apparel & Footwear | 15.63 | 23.48 | -7.84 | 1029.61 | 42 OfficeHub Nordic | Office Supplies (B2B) | 0.0 | 9.16 | -9.16 | 0.0 | 55 Velvet Ash | Apparel & Footwear | 15.62 | 32.26 | -16.64 | 1784.84 | 51 (36 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH t1 AS ( SELECT t10.tenant_id AS tenant_id, SUM(t10.amount / t11.rate) AS captured_eur_q4, SUM(CAST(t10.amount / t11.rate AS NUMERIC(18,6))) AS captured_eur_q4, COUNT(DISTINCT t10.order_id) AS order_count_q4 FROM payments AS t10 JOIN exchange_rates AS t11 ON t11.base_code = 'EUR' AND t11.quote_code = t10.currency_code AND t11.as_of = CAST(t10.captured_at AS date) WHERE t10.status IN ('captured', 'partially_refunded', 'refunded') AND t10.captured_at >= DATE '2025-10-01' AND t10.captured_at < DATE '2026-01-01' GROUP BY t10.tenant_id),t2 AS ( SELECT t20.tenant_id AS tenant_id, SUM(t20.amount / t21.rate) AS captured_eur_baseline SUM(CAST(t20.amount / t21.rate AS NUMERIC(18,6))) AS captured_eur_baseline FROM payments AS t20 JOIN exchange_rates AS t21 ON t21.base_code = 'EUR' AND t21.quote_code = t20.currency_code AND t21.as_of = CAST(t20.captured_at AS date) WHERE t20.status IN ('captured', 'partially_refunded', 'refunded') AND t20.captured_at >= DATE '2025-01-01' AND t20.captured_at < DATE '2025-10-01' GROUP BY t20.tenant_id),t3 AS ( SELECT t30.tenant_id AS tenant_id, SUM(t30.amount / t31.rate) AS refund_eur_q4 SUM(CAST(t30.amount / t31.rate AS NUMERIC(18,6))) AS refund_eur_q4 FROM payment_refunds AS t30 JOIN exchange_rates AS t31 ON t31.base_code = 'EUR' AND t31.quote_code = t30.currency_code AND t31.as_of = CAST(t30.processed_at AS date) WHERE t30.status = 'succeeded' AND t30.processed_at >= DATE '2025-10-01' AND t30.processed_at < DATE '2026-01-01' GROUP BY t30.tenant_id),t4 AS ( SELECT t40.tenant_id AS tenant_id, SUM(t40.amount / t41.rate) AS refund_eur_baseline SUM(CAST(t40.amount / t41.rate AS NUMERIC(18,6))) AS refund_eur_baseline FROM payment_refunds AS t40 JOIN exchange_rates AS t41 ON t41.base_code = 'EUR' AND t41.quote_code = t40.currency_code AND t41.as_of = CAST(t40.processed_at AS date) WHERE t40.status = 'succeeded' AND t40.processed_at >= DATE '2025-01-01' AND t40.processed_at < DATE '2025-10-01' GROUP BY t40.tenant_id)SELECT t0.name AS merchant_name, t0.industry AS vertical, ROUND(100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0), 2) AS refund_rate_q4_pct, ROUND(100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0), 2) AS refund_rate_baseline_pct, ROUND( (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) , 2) AS change_vs_baseline_pp, ROUND(COALESCE(t3.refund_eur_q4, 0), 2) AS refund_amount_q4_eur, 100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0) AS refund_rate_q4_pct, 100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0) AS refund_rate_baseline_pct, (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) AS change_vs_baseline_pp, COALESCE(t3.refund_eur_q4, 0) AS refund_amount_q4_eur, t1.order_count_q4 AS order_count_q4FROM tenants AS t0JOIN t1 ON t1.tenant_id = t0.idLEFT JOIN t2 ON t2.tenant_id = t0.idLEFT JOIN t3 ON t3.tenant_id = t0.idLEFT JOIN t4 ON t4.tenant_id = t0.idWHERE t1.order_count_q4 >= 10ORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC, t0.id ASCORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC NULLS FIRST, t0.id ASC NULLS LAST
WITH t1 AS ( SELECT t10.tenant_id AS tenant_id, SUM(CAST(t10.amount / t11.rate AS NUMERIC(18,6))) AS captured_eur_q4, COUNT(DISTINCT t10.order_id) AS order_count_q4 FROM payments AS t10 JOIN exchange_rates AS t11 ON t11.base_code = 'EUR' AND t11.quote_code = t10.currency_code AND t11.as_of = CAST(t10.captured_at AS date) WHERE t10.status IN ('captured', 'partially_refunded', 'refunded') AND t10.captured_at >= DATE '2025-10-01' AND t10.captured_at < DATE '2026-01-01' GROUP BY t10.tenant_id ), t2 AS ( SELECT t20.tenant_id AS tenant_id, SUM(CAST(t20.amount / t21.rate AS NUMERIC(18,6))) AS captured_eur_baseline FROM payments AS t20 JOIN exchange_rates AS t21 ON t21.base_code = 'EUR' AND t21.quote_code = t20.currency_code AND t21.as_of = CAST(t20.captured_at AS date) WHERE t20.status IN ('captured', 'partially_refunded', 'refunded') AND t20.captured_at >= DATE '2025-01-01' AND t20.captured_at < DATE '2025-10-01' GROUP BY t20.tenant_id ), t3 AS ( SELECT t30.tenant_id AS tenant_id, SUM(CAST(t30.amount / t31.rate AS NUMERIC(18,6))) AS refund_eur_q4 FROM payment_refunds AS t30 JOIN exchange_rates AS t31 ON t31.base_code = 'EUR' AND t31.quote_code = t30.currency_code AND t31.as_of = CAST(t30.processed_at AS date) WHERE t30.status = 'succeeded' AND t30.processed_at >= DATE '2025-10-01' AND t30.processed_at < DATE '2026-01-01' GROUP BY t30.tenant_id ), t4 AS ( SELECT t40.tenant_id AS tenant_id, SUM(CAST(t40.amount / t41.rate AS NUMERIC(18,6))) AS refund_eur_baseline FROM payment_refunds AS t40 JOIN exchange_rates AS t41 ON t41.base_code = 'EUR' AND t41.quote_code = t40.currency_code AND t41.as_of = CAST(t40.processed_at AS date) WHERE t40.status = 'succeeded' AND t40.processed_at >= DATE '2025-01-01' AND t40.processed_at < DATE '2025-10-01' GROUP BY t40.tenant_id ) SELECT t0.name AS merchant_name, t0.industry AS vertical, 100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0) AS refund_rate_q4_pct, 100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0) AS refund_rate_baseline_pct, (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) AS change_vs_baseline_pp, COALESCE(t3.refund_eur_q4, 0) AS refund_amount_q4_eur, t1.order_count_q4 AS order_count_q4 FROM tenants AS t0 JOIN t1 ON t1.tenant_id = t0.id LEFT JOIN t2 ON t2.tenant_id = t0.id LEFT JOIN t3 ON t3.tenant_id = t0.id LEFT JOIN t4 ON t4.tenant_id = t0.id WHERE t1.order_count_q4 >= 10 ORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC NULLS FIRST, t0.id ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Hannah Brooks (analyst for the Chief Executive Officer): Title: Merchant refund-rate spike check, Q4 2025 vs 2025 baseline Question: Is the Q4 2025 refund-rate increase concentrated in a handful of merchants, or spread broadly across the merchant base? Grain: one row per merchant (tenant) Columns: - merchant name - vertical - refund rate Q4 2025 (%) - refund rate Q1-Q3 2025 baseline (%) - change vs own baseline (percentage points) - refund amount Q4 2025 (EUR) - order count Q4 2025 Details: Source: payment_refunds (status='succeeded', processed_at in quarter, amount converted to EUR via exchange_rates as_of processed_at date) as the numerator, divided by payments (status in 'captured','partially_refunded','refunded', captured_at in quarter, amount converted to EUR via exchange_rates as_of captured_at date) as the denominator, both joined to orders->tenants (or directly via payments.order_id->orders.tenant_id) to attribute to a merchant. Compute refund rate = 100 * refund EUR / captured payment EUR, once for Q4 2025 (2025-10-01 to 2026-01-01) and once pooled across Q1-Q3 2025 (2025-01-01 to 2025-10-01) as each merchant's own baseline. 'Change vs own baseline' lets us separate merchants with a structurally high rate (e.g. apparel, reportedly ~20% normally) from merchants whose rate genuinely jumped in Q4. Include only merchants with at least 10 orders in Q4 2025 to avoid noise from tiny volumes. Order by change vs own baseline descending (biggest jumps first), tie-break by refund amount Q4 2025 descending. Note total merchant count returned. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH t1 AS ( SELECT t10.tenant_id AS tenant_id, SUM(t10.amount / t11.rate) AS captured_eur_q4, COUNT(DISTINCT t10.order_id) AS order_count_q4 FROM payments AS t10 JOIN exchange_rates AS t11 ON t11.base_code = 'EUR' AND t11.quote_code = t10.currency_code AND t11.as_of = CAST(t10.captured_at AS date) WHERE t10.status IN ('captured', 'partially_refunded', 'refunded') AND t10.captured_at >= DATE '2025-10-01' AND t10.captured_at < DATE '2026-01-01' GROUP BY t10.tenant_id ), t2 AS ( SELECT t20.tenant_id AS tenant_id, SUM(t20.amount / t21.rate) AS captured_eur_baseline FROM payments AS t20 JOIN exchange_rates AS t21 ON t21.base_code = 'EUR' AND t21.quote_code = t20.currency_code AND t21.as_of = CAST(t20.captured_at AS date) WHERE t20.status IN ('captured', 'partially_refunded', 'refunded') AND t20.captured_at >= DATE '2025-01-01' AND t20.captured_at < DATE '2025-10-01' GROUP BY t20.tenant_id ), t3 AS ( SELECT t30.tenant_id AS tenant_id, SUM(t30.amount / t31.rate) AS refund_eur_q4 FROM payment_refunds AS t30 JOIN exchange_rates AS t31 ON t31.base_code = 'EUR' AND t31.quote_code = t30.currency_code AND t31.as_of = CAST(t30.processed_at AS date) WHERE t30.status = 'succeeded' AND t30.processed_at >= DATE '2025-10-01' AND t30.processed_at < DATE '2026-01-01' GROUP BY t30.tenant_id ), t4 AS ( SELECT t40.tenant_id AS tenant_id, SUM(t40.amount / t41.rate) AS refund_eur_baseline FROM payment_refunds AS t40 JOIN exchange_rates AS t41 ON t41.base_code = 'EUR' AND t41.quote_code = t40.currency_code AND t41.as_of = CAST(t40.processed_at AS date) WHERE t40.status = 'succeeded' AND t40.processed_at >= DATE '2025-01-01' AND t40.processed_at < DATE '2025-10-01' GROUP BY t40.tenant_id ) SELECT t0.name AS merchant_name, t0.industry AS vertical, ROUND(100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0), 2) AS refund_rate_q4_pct, ROUND(100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0), 2) AS refund_rate_baseline_pct, ROUND( (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) , 2) AS change_vs_baseline_pp, ROUND(COALESCE(t3.refund_eur_q4, 0), 2) AS refund_amount_q4_eur, t1.order_count_q4 AS order_count_q4 FROM tenants AS t0 JOIN t1 ON t1.tenant_id = t0.id LEFT JOIN t2 ON t2.tenant_id = t0.id LEFT JOIN t3 ON t3.tenant_id = t0.id LEFT JOIN t4 ON t4.tenant_id = t0.id WHERE t1.order_count_q4 >= 10 ORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC, t0.id ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, vertical, refund_rate_q4_pct, refund_rate_baseline_pct, change_vs_baseline_pp, refund_amount_q4_eur, order_count_q4
submitWITH t1 AS ( SELECT t10.tenant_id AS tenant_id, SUM(t10.amount / t11.rate) AS captured_eur_q4, COUNT(DISTINCT t10.order_id) AS order_count_q4 FROM payments AS t10 JOIN exchange_rates AS t11 ON t11.base_code = 'EUR' AND t11.quote_code = t10.currency_code AND t11.as_of = CAST(t10.captured_at AS date) WHERE t10.status IN ('captured', 'partially_refunded', 'refunded') AND t10.captured_at >= DATE '2025-10-01' AND t10.captured_at < DATE '2026-01-01' GROUP BY t10.tenant_id ), t2 AS ( SELECT t20.tenant_id AS tenant_id, SUM(t20.amount / t21.rate) AS captured_eur_baseline FROM payments AS t20 JOIN exchange_rates AS t21 ON t21.base_code = 'EUR' AND t21.quote_code = t20.currency_code AND t21.as_of = CAST(t20.captured_at AS date) WHERE t20.status IN ('captured', 'partially_refunded', 'refunded') AND t20.captured_at >= DATE '2025-01-01' AND t20.captured_at < DATE '2025-10-01' GROUP BY t20.tenant_id ), t3 AS ( SELECT t30.tenant_id AS tenant_id, SUM(t30.amount / t31.rate) AS refund_eur_q4 FROM payment_refunds AS t30 JOIN exchange_rates AS t31 ON t31.base_code = 'EUR' AND t31.quote_code = t30.currency_code AND t31.as_of = CAST(t30.processed_at AS date) WHERE t30.status = 'succeeded' AND t30.processed_at >= DATE '2025-10-01' AND t30.processed_at < DATE '2026-01-01' GROUP BY t30.tenant_id ), t4 AS ( SELECT t40.tenant_id AS tenant_id, SUM(t40.amount / t41.rate) AS refund_eur_baseline FROM payment_refunds AS t40 JOIN exchange_rates AS t41 ON t41.base_code = 'EUR' AND t41.quote_code = t40.currency_code AND t41.as_of = CAST(t40.processed_at AS date) WHERE t40.status = 'succeeded' AND t40.processed_at >= DATE '2025-01-01' AND t40.processed_at < DATE '2025-10-01' GROUP BY t40.tenant_id ) SELECT t0.name AS merchant_name, t0.industry AS vertical, ROUND(100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0), 2) AS refund_rate_q4_pct, ROUND(100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0), 2) AS refund_rate_baseline_pct, ROUND( (100.0 * COALESCE(t3.refund_eur_q4, 0) / NULLIF(t1.captured_eur_q4, 0)) - (100.0 * COALESCE(t4.refund_eur_baseline, 0) / NULLIF(t2.captured_eur_baseline, 0)) , 2) AS change_vs_baseline_pp, ROUND(COALESCE(t3.refund_eur_q4, 0), 2) AS refund_amount_q4_eur, t1.order_count_q4 AS order_count_q4 FROM tenants AS t0 JOIN t1 ON t1.tenant_id = t0.id LEFT JOIN t2 ON t2.tenant_id = t0.id LEFT JOIN t3 ON t3.tenant_id = t0.id LEFT JOIN t4 ON t4.tenant_id = t0.id WHERE t1.order_count_q4 >= 10 ORDER BY change_vs_baseline_pp DESC NULLS LAST, refund_amount_q4_eur DESC, t0.id ASC
Submitted to tonight's batch. Your work on this request is done.