Queries · q218 · Chief Executive Officer · week 2, night 4
“Among our largest merchants, which look efficient versus costly to serve on a like-for-like rate basis?”
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 4, request 2 of 3
Big catch from last night: the "directional unit economics" report is mathematically misleading, not just rough — every vertical shows net contribution of -300% to -1450% of fee revenue, because it nets full order-value refunds and full shipping outlays against our thin processing-fee take (e.g. Apparel refunds of €281k vs. €33k fee revenue). Refunds and shipping are gross merchandise/logistics flows, not Tidewick's P&L costs on a 1:1 basis with our fee revenue — comparing them directly produces a result that would badly mislead the board if presented as-is. The Deskline Supplies shipping-cost check came back ambiguous rather than conclusive: costs are spread across many carrier/service lanes (not one bad row), but with extreme within-lane variance (€5 to €9,509 in the same lane) that's plausible for a B2B freight mix but still needs an Ops/Finance sanity check before I'd trust it in a model. On the platform-billing-risk side: only 4 merchants are past_due/suspended/churned, all small (<€1.3k TTM fee revenue each) — not a board-level concern on its own. Tonight I'm using all 3 slots to put the unit-economics scoping on solid footing before month-end: (1) redo the vertical view as comparable rates against gross volume rather than raw euros against fee revenue, (2) the same clean rate view at merchant grain for the top accounts (since the board wants merchant-level too), and (3) a direct check on whether shipping cost is actually a net cost or a wash once customer-paid shipping is netted against carrier cost — this resolves a real "whose cost is this" ambiguity I flagged rather than assuming an answer. I'm not spending a further slot chasing Finance's €93,583 discrepancy blindly; that needs a joint query walkthrough with Tobias's team, which I'll propose directly rather than guess at in another report.
The engineer · 1 dry run on the empty schema, then the submission
WITH payout_eur AS ( SELECT p.tenant_id, SUM(p.gross_amount / er.rate) AS gross_eur, SUM(p.fee_amount / er.rate) AS 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 = p.paid_at::date WHERE p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ), returns_eur AS ( SELECT r.tenant_id, SUM(r.refund_total / er.rate) AS refund_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 = r.requested_at::date WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id ), shipping_eur AS ( SELECT s.tenant_id, SUM(s.shipping_cost / er.rate) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = s.shipped_at::date WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id ), orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id ), tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS vertical, ROUND(pe.gross_eur, 2) AS gross_payment_volume_eur, ROUND(pe.fee_eur, 2) AS fee_revenue_eur, ROUND(100 * pe.fee_eur / NULLIF(pe.gross_eur, 0), 2) AS take_rate_pct, ROUND(100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS refund_rate_pct, ROUND(100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS shipping_cost_rate_pct, ROUND(100 * COALESCE(tc.tickets_2025, 0) / NULLIF(oc.orders_2025, 0)::numeric, 2) AS tickets_per_100_orders FROM payout_eur pe JOIN tenants t ON t.id = pe.tenant_id LEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_id LEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_id LEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_id LEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_id ORDER BY pe.gross_eur DESC, t.name LIMIT 20
“Query compiles cleanly and returns the 8 expected columns, one row per merchant. Submitting.”
“Submitted. Summary of what the report delivers: - Grain: one row per merchant (tenant), top 20 by 2025 gross payment volume (EUR) - Columns: merchant name, vertical, gross payment volume (EUR), fee revenue (EUR), take rate %, refund rate %, shipping cost rate %, tickets per 100 orders - Methodology: all rate numerators/denominators are in EUR, converted via the daily EUR exchange rate on each transaction's own date (payout date for volume/fees, return request date for refunds, ship date for shipping cost). Rates are computed against gross volume as requested, not against fee revenue. One caveat noted to the analyst: I didn't have the exact prior "vertical-level unit-economics" query to copy verbatim, so I used the most natural matching sources (payouts for gross/fee, order_returns for refunds, shipments for shipping cost, tickets vs. orders placed in 2025) — worth a quick cross-check against tonight's vertical report for consistency.”
As Hannah Brooks saw it, from the small dataset the agents worked on
merchant_name | vertical | gross_payment_volume_eur | fee_revenue_eur | take_rate_pct | refund_rate_pct | shipping_cost_rate_pct | tickets_per_100_orders -----------------------+---------------------------+--------------------------+-----------------+---------------+-----------------+------------------------+----------------------- Cragsley Outfitters | Apparel & Footwear | 702797.52 | 22263.37 | 3.17 | 22.42 | 2.16 | 11.0 Marrowby & Vane | Apparel & Footwear | 276394.66 | 4858.84 | 1.76 | 21.15 | 4.31 | 13.14 Cinder Peak Sports Co. | Sports & Outdoor | 126336.27 | 3877.43 | 3.07 | 12.87 | 1.33 | 12.72 OfficeHub Nordic | Office Supplies (B2B) | 111821.72 | 3618.19 | 3.24 | 19.8 | 4.43 | 8.82 Castell Fasteners | Industrial Supplies (B2B) | 107753.57 | 3499.84 | 3.25 | 3.77 | 5.52 | 8.18 Deskline Supplies | Office Supplies (B2B) | 64727.75 | 974.76 | 1.51 | 4.88 | 120.31 | 14.67 Fennel & Flint | Home & Living | 62508.63 | 1739.27 | 2.78 | 6.07 | 4.97 | 17.22 Petalcourt Beauty | Beauty | 61865.09 | 1225.61 | 1.98 | 5.81 | 7.64 | 16.86 Grind Collective | Food & Coffee | 39682.45 | 1265.15 | 3.19 | 0.1 | 18.1 | 10.98 Kayak Corner | Sports & Outdoor | 39521.7 | 1174.45 | 2.97 | 6.82 | 2.36 | 18.35 Fell & Fjell | Sports & Outdoor | 33288.47 | 468.76 | 1.41 | 17.21 | 0.21 | 9.41 Honeycomb Larder | Food & Coffee | 31808.64 | 1145.98 | 3.6 | 0.86 | 8.63 | 9.35 Kibblewick | Pet Supplies | 31741.18 | 732.3 | 2.31 | 0.1 | 17.66 | 10.71 Oakhurst Tailoring | Apparel & Footwear | 24619.8 | 628.93 | 2.55 | 12.83 | 0.46 | 16.36 Velvet Ash | Apparel & Footwear | 23605.9 | 333.41 | 1.41 | 23.14 | 0.35 | 14.18 Coastline Interiors | Home & Living | 20388.42 | 288.84 | 1.42 | 6.86 | 0.48 | 13.55 Willow & Wren | Home & Living | 18982.3 | 342.49 | 1.8 | 12.98 | 4.39 | 19.59 Circuit Harbor | Consumer Electronics | 18807.88 | 334.27 | 1.78 | 0.3 | 0.69 | 5.26 Nordic Bloom Cosmetics | Beauty | 17854.38 | 254.63 | 1.43 | 1.43 | 0.68 | 9.27 Juniper Apothecary | Beauty | 17593.14 | 263.63 | 1.5 | 2.44 | 0.42 | 10.83 (20 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 payout_eur AS ( SELECT p.tenant_id, SUM(p.gross_amount / er.rate) AS gross_eur, SUM(p.fee_amount / er.rate) AS fee_eur SELECT p.tenant_id AS tenant_id, 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 FROM payouts p JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = p.paid_at::date 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.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id),returns_eur AS ( SELECT r.tenant_id, SUM(r.refund_total / er.rate) AS refund_eur SELECT r.tenant_id AS tenant_id, SUM(CAST(r.refund_total / er.rate AS NUMERIC(18,6))) AS refund_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 = r.requested_at::date JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.requested_at AS date) WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id),shipping_eur AS ( SELECT s.tenant_id, SUM(s.shipping_cost / er.rate) AS shipping_eur SELECT s.tenant_id AS tenant_id, SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = s.shipped_at::date JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id),orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id),tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id)SELECT t.name AS merchant_name, t.industry AS vertical, ROUND(pe.gross_eur, 2) AS gross_payment_volume_eur, ROUND(pe.fee_eur, 2) AS fee_revenue_eur, ROUND(100 * pe.fee_eur / NULLIF(pe.gross_eur, 0), 2) AS take_rate_pct, ROUND(100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS refund_rate_pct, ROUND(100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS shipping_cost_rate_pct, ROUND(100 * COALESCE(tc.tickets_2025, 0) / NULLIF(oc.orders_2025, 0)::numeric, 2) AS tickets_per_100_orders pe.gross_eur AS gross_payment_volume_eur, pe.fee_eur AS fee_revenue_eur, 100 * pe.fee_eur / NULLIF(pe.gross_eur, 0) AS take_rate_pct, 100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0) AS refund_rate_pct, 100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0) AS shipping_cost_rate_pct, 100 * COALESCE(tc.tickets_2025, 0) / CAST(NULLIF(oc.orders_2025, 0) AS numeric(18,6)) AS tickets_per_100_ordersFROM payout_eur peJOIN tenants t ON t.id = pe.tenant_idLEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_idLEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_idLEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_idLEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_idORDER BY pe.gross_eur DESC, t.nameLIMIT 20ORDER BY pe.gross_eur DESC NULLS FIRST, t.name NULLS LAST, pe.tenant_id NULLS LAST{{fetch_first(20)}}
WITH payout_eur AS ( SELECT p.tenant_id AS tenant_id, 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 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.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ), returns_eur AS ( SELECT r.tenant_id AS tenant_id, SUM(CAST(r.refund_total / er.rate AS NUMERIC(18,6))) AS refund_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.requested_at AS date) WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id ), shipping_eur AS ( SELECT s.tenant_id AS tenant_id, SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id ), orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id ), tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS vertical, pe.gross_eur AS gross_payment_volume_eur, pe.fee_eur AS fee_revenue_eur, 100 * pe.fee_eur / NULLIF(pe.gross_eur, 0) AS take_rate_pct, 100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0) AS refund_rate_pct, 100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0) AS shipping_cost_rate_pct, 100 * COALESCE(tc.tickets_2025, 0) / CAST(NULLIF(oc.orders_2025, 0) AS numeric(18,6)) AS tickets_per_100_orders FROM payout_eur pe JOIN tenants t ON t.id = pe.tenant_id LEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_id LEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_id LEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_id LEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_id ORDER BY pe.gross_eur DESC NULLS FIRST, t.name NULLS LAST, pe.tenant_id NULLS LAST FETCH FIRST 20 ROWS ONLY
WITH payout_eur AS ( SELECT p.tenant_id AS tenant_id, 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 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.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ), returns_eur AS ( SELECT r.tenant_id AS tenant_id, SUM(CAST(r.refund_total / er.rate AS NUMERIC(18,6))) AS refund_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.requested_at AS date) WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id ), shipping_eur AS ( SELECT s.tenant_id AS tenant_id, SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = CAST(s.shipped_at AS date) WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id ), orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id ), tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS vertical, pe.gross_eur AS gross_payment_volume_eur, pe.fee_eur AS fee_revenue_eur, 100 * pe.fee_eur / NULLIF(pe.gross_eur, 0) AS take_rate_pct, 100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0) AS refund_rate_pct, 100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0) AS shipping_cost_rate_pct, 100 * COALESCE(tc.tickets_2025, 0) / CAST(NULLIF(oc.orders_2025, 0) AS numeric(18,6)) AS tickets_per_100_orders FROM payout_eur pe JOIN tenants t ON t.id = pe.tenant_id LEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_id LEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_id LEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_id LEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_id ORDER BY pe.gross_eur DESC NULLS FIRST, t.name NULLS LAST, pe.tenant_id NULLS LAST LIMIT 20
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 unit economics, rate-based view: top 20 by volume, 2025 Question: Among our largest merchants, which look efficient versus costly to serve on a like-for-like rate basis? Grain: one row per merchant (tenant), top 20 by gross payment volume Columns: - merchant name - vertical - gross payment volume (EUR, 2025) - fee revenue (EUR, 2025) - take rate (%, fee revenue / gross volume) - refund rate (%, order_returns.refund_total / gross volume) - shipping cost rate (%, shipments.shipping_cost / gross volume) - tickets per 100 orders Details: Same sources, filters, EUR conversion and rate definitions as tonight's vertical-level unit-economics report, but aggregated per tenant instead of per vertical, limited to the top 20 merchants ranked by 2025 gross payment volume (EUR). This gives the board a merchant-level cut using the corrected, comparable methodology (rates against gross volume, not against fee revenue). Order by gross payment volume EUR descending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH payout_eur AS ( SELECT p.tenant_id, SUM(p.gross_amount / er.rate) AS gross_eur, SUM(p.fee_amount / er.rate) AS 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 = p.paid_at::date WHERE p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ), returns_eur AS ( SELECT r.tenant_id, SUM(r.refund_total / er.rate) AS refund_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 = r.requested_at::date WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id ), shipping_eur AS ( SELECT s.tenant_id, SUM(s.shipping_cost / er.rate) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = s.shipped_at::date WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id ), orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id ), tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS vertical, ROUND(pe.gross_eur, 2) AS gross_payment_volume_eur, ROUND(pe.fee_eur, 2) AS fee_revenue_eur, ROUND(100 * pe.fee_eur / NULLIF(pe.gross_eur, 0), 2) AS take_rate_pct, ROUND(100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS refund_rate_pct, ROUND(100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS shipping_cost_rate_pct, ROUND(100 * COALESCE(tc.tickets_2025, 0) / NULLIF(oc.orders_2025, 0)::numeric, 2) AS tickets_per_100_orders FROM payout_eur pe JOIN tenants t ON t.id = pe.tenant_id LEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_id LEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_id LEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_id LEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_id ORDER BY pe.gross_eur DESC, t.name LIMIT 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, vertical, gross_payment_volume_eur, fee_revenue_eur, take_rate_pct, refund_rate_pct, shipping_cost_rate_pct, tickets_per_100_orders
submitWITH payout_eur AS ( SELECT p.tenant_id, SUM(p.gross_amount / er.rate) AS gross_eur, SUM(p.fee_amount / er.rate) AS 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 = p.paid_at::date WHERE p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' GROUP BY p.tenant_id ), returns_eur AS ( SELECT r.tenant_id, SUM(r.refund_total / er.rate) AS refund_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 = r.requested_at::date WHERE r.requested_at >= DATE '2025-01-01' AND r.requested_at < DATE '2026-01-01' GROUP BY r.tenant_id ), shipping_eur AS ( SELECT s.tenant_id, SUM(s.shipping_cost / er.rate) AS shipping_eur FROM shipments s JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of = s.shipped_at::date WHERE s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2026-01-01' AND s.shipping_cost IS NOT NULL GROUP BY s.tenant_id ), orders_cnt AS ( SELECT tenant_id, COUNT(*) AS orders_2025 FROM orders WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01' GROUP BY tenant_id ), tickets_cnt AS ( SELECT tenant_id, COUNT(*) AS tickets_2025 FROM tickets WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id ) SELECT t.name AS merchant_name, t.industry AS vertical, ROUND(pe.gross_eur, 2) AS gross_payment_volume_eur, ROUND(pe.fee_eur, 2) AS fee_revenue_eur, ROUND(100 * pe.fee_eur / NULLIF(pe.gross_eur, 0), 2) AS take_rate_pct, ROUND(100 * COALESCE(re.refund_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS refund_rate_pct, ROUND(100 * COALESCE(se.shipping_eur, 0) / NULLIF(pe.gross_eur, 0), 2) AS shipping_cost_rate_pct, ROUND(100 * COALESCE(tc.tickets_2025, 0) / NULLIF(oc.orders_2025, 0)::numeric, 2) AS tickets_per_100_orders FROM payout_eur pe JOIN tenants t ON t.id = pe.tenant_id LEFT JOIN returns_eur re ON re.tenant_id = pe.tenant_id LEFT JOIN shipping_eur se ON se.tenant_id = pe.tenant_id LEFT JOIN orders_cnt oc ON oc.tenant_id = pe.tenant_id LEFT JOIN tickets_cnt tc ON tc.tenant_id = pe.tenant_id ORDER BY pe.gross_eur DESC, t.name LIMIT 20
Submitted to tonight's batch. Your work on this request is done.