Queries · q208 · Chief Executive Officer · week 2, night 1
“Does our 2025 fee-revenue total hold up when broken down by currency, and which currencies carry our FX exposure?”
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 1 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 agg AS ( SELECT p.currency_code, COUNT(*) AS payout_count, SUM(p.gross_amount) AS total_gross_native, SUM(p.fee_amount) AS total_fee_native, SUM(p.gross_amount / er.rate) AS total_gross_eur, SUM(p.fee_amount / er.rate) AS total_fee_eur FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = date(p.paid_at) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.currency_code ) SELECT currency_code, payout_count, ROUND(total_gross_native, 2) AS total_gross_native, ROUND(total_fee_native, 2) AS total_fee_native, ROUND(total_gross_eur, 2) AS total_gross_eur, ROUND(total_fee_eur, 2) AS total_fee_eur, ROUND(total_fee_native / NULLIF(total_fee_eur,0), 4) AS implied_avg_rate_native_per_eur, ROUND(100 * total_fee_eur / SUM(total_fee_eur) OVER (), 2) AS share_of_total_fee_eur_pct FROM agg ORDER BY total_fee_eur DESC, currency_code ASC
“Query compiles cleanly. Submitting.”
“Submitted. The report aggregates payouts (status='paid', paid_at in 2025) by currency_code, converting gross and fee amounts to EUR row-by-row via exchange_rates (EUR→currency, as_of = date(paid_at)) before summing — no joins to one-to-many tables, per the validated methodology. Columns include payout count, native gross/fee totals, EUR gross/fee totals, the implied native-per-EUR rate, and each currency's share of total EUR fee revenue, ordered by fee revenue EUR descending.
Note: I flagged in the submission note that the sum of total_fee_eur across rows is the figure to check against Finance's €93,583 vs your €53,186 — since dry runs don't touch real data, that reconciliation will only be visible once tonight's batch actually runs.”
As Hannah Brooks saw it, from the small dataset the agents worked on
currency_code | payout_count | total_gross_native | total_fee_native | total_gross_eur | total_fee_eur | implied_avg_rate_native_per_eur | share_of_total_fee_eur_pct --------------+--------------+--------------------+------------------+-----------------+---------------+---------------------------------+--------------------------- USD | 1802 | 1175910.69 | 37454.41 | 1021107.73 | 32522.97 | 1.1516 | 61.15 GBP | 3165 | 576156.82 | 11796.21 | 669712.4 | 13707.65 | 0.8606 | 25.77 NOK | 238 | 1617567.26 | 48694.22 | 141252.38 | 4249.04 | 11.4601 | 7.99 DKK | 345 | 595621.21 | 10537.79 | 79924.93 | 1413.8 | 7.4535 | 2.66 SEK | 567 | 874215.76 | 14217.03 | 79451.65 | 1292.39 | 11.0006 | 2.43 (5 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH agg AS ( SELECT p.currency_code, p.currency_code AS currency_code, COUNT(*) AS payout_count, SUM(p.gross_amount) AS total_gross_native, SUM(p.fee_amount) AS total_fee_native, SUM(p.gross_amount / er.rate) AS total_gross_eur, SUM(p.fee_amount / er.rate) AS total_fee_eur SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS total_gross_eur, SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS total_fee_eur FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = date(p.paid_at) AND er.as_of = CAST(p.paid_at AS DATE) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.currency_code)SELECT currency_code, payout_count, ROUND(total_gross_native, 2) AS total_gross_native, ROUND(total_fee_native, 2) AS total_fee_native, ROUND(total_gross_eur, 2) AS total_gross_eur, ROUND(total_fee_eur, 2) AS total_fee_eur, ROUND(total_fee_native / NULLIF(total_fee_eur,0), 4) AS implied_avg_rate_native_per_eur, ROUND(100 * total_fee_eur / SUM(total_fee_eur) OVER (), 2) AS share_of_total_fee_eur_pct total_gross_native AS total_gross_native, total_fee_native AS total_fee_native, total_gross_eur AS total_gross_eur, total_fee_eur AS total_fee_eur, total_fee_native / NULLIF(total_fee_eur,0) AS implied_avg_rate_native_per_eur, 100 * total_fee_eur / SUM(total_fee_eur) OVER () AS share_of_total_fee_eur_pctFROM aggORDER BY total_fee_eur DESC, currency_code ASCORDER BY total_fee_eur DESC NULLS FIRST, currency_code ASC NULLS LAST
WITH agg AS ( SELECT p.currency_code AS currency_code, COUNT(*) AS payout_count, SUM(p.gross_amount) AS total_gross_native, SUM(p.fee_amount) AS total_fee_native, SUM(CAST(p.gross_amount / er.rate AS NUMERIC(18,6))) AS total_gross_eur, SUM(CAST(p.fee_amount / er.rate AS NUMERIC(18,6))) AS total_fee_eur FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.paid_at AS DATE) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.currency_code ) SELECT currency_code, payout_count, total_gross_native AS total_gross_native, total_fee_native AS total_fee_native, total_gross_eur AS total_gross_eur, total_fee_eur AS total_fee_eur, total_fee_native / NULLIF(total_fee_eur,0) AS implied_avg_rate_native_per_eur, 100 * total_fee_eur / SUM(total_fee_eur) OVER () AS share_of_total_fee_eur_pct FROM agg ORDER BY total_fee_eur DESC NULLS FIRST, currency_code 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: 2025 payout fee revenue and volume by currency Question: Does our 2025 fee-revenue total hold up when broken down by currency, and which currencies carry our FX exposure? Grain: one row per payout currency_code Columns: - currency_code - payout count - total gross amount (native currency, unconverted) - total fee amount (native currency, unconverted) - total gross volume (EUR) - total fee revenue (EUR) - implied average rate (native currency per EUR, = total fee native / total fee EUR) - share of total EUR fee revenue (%) Details: Source: payouts only, filtered to status='paid' and paid_at >= 2025-01-01 and paid_at < 2026-01-01 (the validated definition of settled payouts from last week's reconciliation). Convert each payout row to EUR individually via exchange_rates (base_code='EUR', quote_code=payouts.currency_code, as_of=date(paid_at)) before summing, exactly as in the validated merchant/vertical reports — no fan-out joins (aggregate at the payout row level first, do not join to any one-to-many table like payout_items, ledger_entries, or tenants before this aggregation). I want this to double-check against a competing figure from Finance (€93,583 fee revenue vs my €53,186 for the same 2025 window) — please confirm in the report notes that summing total_fee_revenue_eur across all currency rows reproduces €53,186 (if not, flag immediately, something changed). Order by fee revenue EUR descending, tie-break by currency_code ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH agg AS ( SELECT p.currency_code, COUNT(*) AS payout_count, SUM(p.gross_amount) AS total_gross_native, SUM(p.fee_amount) AS total_fee_native, SUM(p.gross_amount / er.rate) AS total_gross_eur, SUM(p.fee_amount / er.rate) AS total_fee_eur FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = date(p.paid_at) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.currency_code ) SELECT currency_code, payout_count, ROUND(total_gross_native, 2) AS total_gross_native, ROUND(total_fee_native, 2) AS total_fee_native, ROUND(total_gross_eur, 2) AS total_gross_eur, ROUND(total_fee_eur, 2) AS total_fee_eur, ROUND(total_fee_native / NULLIF(total_fee_eur,0), 4) AS implied_avg_rate_native_per_eur, ROUND(100 * total_fee_eur / SUM(total_fee_eur) OVER (), 2) AS share_of_total_fee_eur_pct FROM agg ORDER BY total_fee_eur DESC, currency_code ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: currency_code, payout_count, total_gross_native, total_fee_native, total_gross_eur, total_fee_eur, implied_avg_rate_native_per_eur, share_of_total_fee_eur_pct
submitWITH agg AS ( SELECT p.currency_code, COUNT(*) AS payout_count, SUM(p.gross_amount) AS total_gross_native, SUM(p.fee_amount) AS total_fee_native, SUM(p.gross_amount / er.rate) AS total_gross_eur, SUM(p.fee_amount / er.rate) AS total_fee_eur FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = date(p.paid_at) WHERE p.status = 'paid' AND p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.currency_code ) SELECT currency_code, payout_count, ROUND(total_gross_native, 2) AS total_gross_native, ROUND(total_fee_native, 2) AS total_fee_native, ROUND(total_gross_eur, 2) AS total_gross_eur, ROUND(total_fee_eur, 2) AS total_fee_eur, ROUND(total_fee_native / NULLIF(total_fee_eur,0), 4) AS implied_avg_rate_native_per_eur, ROUND(100 * total_fee_eur / SUM(total_fee_eur) OVER (), 2) AS share_of_total_fee_eur_pct FROM agg ORDER BY total_fee_eur DESC, currency_code ASC
Submitted to tonight's batch. Your work on this request is done.