DB Olympics

Queries · q216 · Chief Executive Officer · week 2, night 3

Directional unit economics by vertical, 2025 (first pass)

“Using only the revenue and cost signals we currently trust, which verticals look directionally profitable to grow versus costly to serve?”

Manager · asked the questionMaren OkaforChief Executive Officer. Joined two weeks ago from a European payments company. Board meeting at the end of January.
Analyst · planned the reportHannah BrooksMethodical: starts with an overview, then drills into whatever looks unusual.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  1. A business event lands on the Chief Executive Officer's desk

    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?

    One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events

  2. Maren Okafor writes to Hannah Brooks

    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:

    • Your fee-revenue figure (€53k trailing twelve months) and Tobias's (€93.6k for calendar 2025) don't match, and neither does your "72% from top 5, stable" concentration story line up cleanly with his "65% from top 10, declining as we add merchants." Before this goes near the board I need these reconciled into one number and one trend — I don't want finance and analytics telling different stories about the same business.
    • The refund story looks more nuanced than "refunds are creeping up broadly." Tobias's team says apparel's ~20% refund rate is just normal for the category, and the real spike this quarter came from a few smaller non-apparel merchants. I'd like your root-cause work to confirm or correct that before we flag it as a trend versus a one-off.
    • Operations found that delivery performance is almost entirely a carrier issue, not a merchant or warehouse issue — the same merchant looks great on one carrier and bad on another, and one carrier partner has clearly been underperforming since November. That changes how I think about "merchant health" — some of what looks like a merchant problem may really be a carrier problem.

    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:

    • On the earnings side, we seem to have good data (processing fees, volume). Platform-plan fees are apparently missing from the warehouse — Tobias, can your team supply that separately so we're not missing a real revenue line?
    • On the cost side, I don't think we've ever assembled this: fulfillment/carrier cost, refund and return handling, support load, and the engineering reliability issues (webhook failures, the cart-reminder bug) that generate extra support tickets. Some of this may not be cleanly attributable per merchant in the data — I want an honest read on what's knowable now versus what needs to be modeled or estimated.
    • The deliverable I need by month-end: a short ranked view of which merchants/verticals/countries are clearly profitable to grow, which are marginal, and which likely cost more to serve than they return — even if the first pass is directional rather than precise.

    My questions for this week:

    1. Can you and Tobias reconcile the revenue and concentration numbers into a single, agreed figure and trend I can quote to the board without contradiction?
    2. What would it take to build even a rough cost-to-serve picture per merchant/vertical/country — and what's your honest confidence in it, given the data gaps you've already flagged (platform fees, refund-to-payout matching, carrier cost attribution)?
    3. On refunds — is the increase really concentrated in a few merchants/verticals as Tobias's team suggests, or is it broader? I want to know whether this is a few accounts to manage or a trend to worry about.
    4. Separately, flag for me which of our top accounts (like Castell Fasteners, with its traffic drop and unpaid invoices) look at risk — I want a short "accounts to watch" list alongside the unit economics work, not buried inside it.

    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

    A note from the Chief Financial Officer, Tobias Lindqvist

    Note to Hannah Brooks

    Hannah — a few things from the finance side as you scope the board unit-economics piece:

    1. What does each merchant actually net us, after processing costs? We only see the economics clearly once payouts are broken into gross, fee, and net — a merchant with huge volume but thin margin on our fee side could look better on revenue than it really is on contribution.
    2. How exposed are we on currency? We earn and hold balances in many merchant currencies against a EUR view for the board — I want to know where FX swings could meaningfully move the 2025 numbers or the 2026 budget.
    3. Who's slow to pay us, and where? For the B2B wholesalers especially, I'd like a sense of outstanding receivables and how long they're taking to settle — that's a cost of serving them that doesn't show up in the headline revenue.

    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

  3. Hannah Brooks plans the night

    The analyst · week 2, night 3, request 3 of 3

    Big win on refunds: Q3 data confirms Kofi's example precisely — OfficeHub Nordic's refund rate jumped from a 2.99% baseline to 20.98% in Q3 (€23,268 refunded on just 45 orders), and it's already back to 0% by Q4, so it reads as a resolved one-off incident, not a trend. Combined with Q4's pattern (big Apparel merchants Cragsley/Marrowby actually improved), I'm confident the refund story is "a few concentrated incidents in small/mid merchants," not a broad trend — ready to write this up. On receivables, the aging report confirms ~€196.9k total outstanding, concentrated almost entirely in 7 B2B wholesalers, with Kontor24 Bürobedarf alone at €116.7k (59% of it, half of that in one single €49.4k invoice) — a much bigger story than Castell Fasteners alone. I also noticed something important for the "at risk" question: tenants.status itself (past_due/suspended/churned) looks like it reflects whether a merchant is behind on paying us (platform billing), distinct from the receivables report (which is their own customers owing them) — worth pulling directly as the cleanest churn signal. Separately, the cost-driver inventory showed Deskline Supplies at ~€1,038/shipment vs. Cragsley at ~€4/shipment — a 237x gap within similar/different verticals that's too extreme to use in a cost model without checking whether it's a real freight-cost pattern or a data outlier. Tonight: (1) the platform-billing-risk list, (2) a sanity check on the Deskline shipping-cost outlier, (3) a first directional vertical-level unit-economics rollup using only the inputs I already trust (fee revenue, shipping cost, refunds, tickets), explicitly caveated as a first pass.

    Directional unit economics by vertical, 2025 (first pass)
    Question
    Using only the revenue and cost signals we currently trust, which verticals look directionally profitable to grow versus costly to serve?
    One row per
    merchant vertical (tenants.industry)
    Columns
    • vertical
    • merchant count
    • fee revenue (EUR, 2025, our validated processing-fee figure)
    • shipping cost (EUR, 2025)
    • refund amount (EUR, 2025)
    • support ticket count (2025)
    • rough net contribution (EUR, = fee revenue − shipping cost − refund amount)
    • net contribution as % of fee revenue

  4. Bea 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 t_industry AS (
  SELECT
    t0.id AS tenant_id,
    COALESCE(NULLIF(TRIM(t0.industry), ''), 'Unspecified') AS vertical
  FROM tenants t0
),
d_vertical AS (
  SELECT DISTINCT ti0.vertical AS vertical
  FROM t_industry ti0
),
m_merchants AS (
  SELECT
    ti1.vertical AS vertical,
    COUNT(DISTINCT ti1.tenant_id) AS merchant_count
  FROM t_industry ti1
  GROUP BY ti1.vertical
),
m_fee AS (
  SELECT
    ti2.vertical AS vertical,
    SUM(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END)) AS fee_revenue_eur
  FROM payouts p0
  JOIN t_industry ti2 ON ti2.tenant_id = p0.tenant_id
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
    AND er0.quote_code = p0.currency_code
    AND er0.as_of = CAST(p0.paid_at AS date)
  WHERE p0.status = 'paid'
    AND p0.paid_at >= DATE '2025-01-01'
    AND p0.paid_at < DATE '2026-01-01'
  GROUP BY ti2.vertical
),
m_shipping AS (
  SELECT
    ti3.vertical AS vertical,
    SUM(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END)) AS shipping_cost_eur
  FROM shipments s0
  JOIN t_industry ti3 ON ti3.tenant_id = s0.tenant_id
  LEFT JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
    AND er1.quote_code = s0.currency_code
    AND er1.as_of = CAST(s0.shipped_at AS date)
  WHERE s0.shipped_at >= DATE '2025-01-01'
    AND s0.shipped_at < DATE '2026-01-01'
    AND s0.shipping_cost IS NOT NULL
  GROUP BY ti3.vertical
),
m_refund AS (
  SELECT
    ti4.vertical AS vertical,
    SUM(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END)) AS refund_amount_eur
  FROM order_returns r0
  JOIN t_industry ti4 ON ti4.tenant_id = r0.tenant_id
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
    AND er2.quote_code = r0.currency_code
    AND er2.as_of = CAST(COALESCE(r0.refunded_at, r0.requested_at) AS date)
  WHERE r0.status = 'refunded'
    AND COALESCE(r0.refunded_at, r0.requested_at) >= DATE '2025-01-01'
    AND COALESCE(r0.refunded_at, r0.requested_at) < DATE '2026-01-01'
  GROUP BY ti4.vertical
),
m_tickets AS (
  SELECT
    ti5.vertical AS vertical,
    COUNT(*) AS ticket_count
  FROM tickets tk0
  JOIN t_industry ti5 ON ti5.tenant_id = tk0.tenant_id
  WHERE tk0.created_at >= DATE '2025-01-01'
    AND tk0.created_at < DATE '2026-01-01'
  GROUP BY ti5.vertical
)
SELECT
  d0.vertical AS vertical,
  COALESCE(mm0.merchant_count, 0) AS merchant_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0), 2) AS fee_revenue_eur,
  ROUND(COALESCE(ms0.shipping_cost_eur, 0), 2) AS shipping_cost_eur,
  ROUND(COALESCE(mr0.refund_amount_eur, 0), 2) AS refund_amount_eur,
  COALESCE(mt0.ticket_count, 0) AS support_ticket_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0), 2) AS rough_net_contribution_eur,
  ROUND(
    CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL
    ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur
    END, 1
  ) AS net_contribution_pct_of_fee_revenue
FROM d_vertical d0
LEFT JOIN m_merchants mm0 ON mm0.vertical = d0.vertical
LEFT JOIN m_fee mf0 ON mf0.vertical = d0.vertical
LEFT JOIN m_shipping ms0 ON ms0.vertical = d0.vertical
LEFT JOIN m_refund mr0 ON mr0.vertical = d0.vertical
LEFT JOIN m_tickets mt0 ON mt0.vertical = d0.vertical
ORDER BY rough_net_contribution_eur DESC, d0.vertical ASC

    “Looks good. Submitting.”

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

    “Submitted. Summary of what the report does: - Grain: one row per tenants.industry (nulls/blanks → "Unspecified"). - Fee revenue: payouts.fee_amount where status='paid' and paid_at in 2025, converted to EUR via exchange_rates (daily EUR→currency rate on the paid date). - Shipping cost: shipments.shipping_cost for shipments with shipped_at in 2025, converted to EUR the same way. - Refund amount: order_returns.refund_total where status='refunded', dated by refunded_at (falling back to requested_at) in 2025, converted to EUR — noted as the closest proxy since there's no direct FK from payment_refunds to order_returns. - Support tickets: count of tickets created in 2025. - Rough net contribution = fee revenue − shipping − refunds, plus that as a % of fee revenue. - Sorted by net contribution descending, tie-broken by vertical name. The submit note flags the refund-sourcing assumption and reiterates the directional, first-pass nature of the net contribution figure.”

  5. The report comes back overnight

    As Hannah Brooks saw it, from the small dataset the agents worked on

    vertical                  | merchant_count | fee_revenue_eur | shipping_cost_eur | refund_amount_eur | support_ticket_count | rough_net_contribution_eur | net_contribution_pct_of_fee_revenue
--------------------------+----------------+-----------------+-------------------+-------------------+----------------------+----------------------------+------------------------------------
Jewelry & Accessories     |              6 |          250.26 |            244.79 |            235.72 |                    7 |                    -230.25 |                               -92.0
Books & Stationery        |              6 |          192.51 |             605.3 |            153.57 |                   23 |                    -566.37 |                              -294.2
Pet Supplies              |             10 |         1771.11 |           9886.73 |            711.02 |                  122 |                   -8826.65 |                              -498.4
Beauty                    |             15 |         2887.84 |           7436.49 |           5470.44 |                  210 |                  -10019.09 |                              -346.9
Home & Living             |             16 |         5173.66 |          10045.28 |          16906.26 |                  222 |                  -21777.89 |                              -420.9
Food & Coffee             |             11 |          9692.1 |          42462.84 |           2572.59 |                  450 |                  -35343.33 |                              -364.7
Sports & Outdoor          |             13 |         9262.09 |           5727.65 |          45282.46 |                  122 |                  -41748.02 |                              -450.7
Industrial Supplies (B2B) |              5 |        12722.73 |          33078.36 |          23398.42 |                   81 |                  -43754.05 |                              -343.9
Consumer Electronics      |              9 |        10116.22 |           3339.43 |          58469.88 |                   75 |                  -51693.09 |                              -511.0
Office Supplies (B2B)     |              4 |         8633.49 |         102264.92 |          31843.41 |                   41 |                 -125474.84 |                             -1453.4
Apparel & Footwear        |             25 |         32881.2 |          35048.53 |         280952.14 |                  967 |                 -283119.47 |                              -861.0
(11 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q216

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

    • 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) (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 (2×)
    The benchmark text, against the submitted SQL
    WITH t_industry AS (  SELECT    t0.id AS tenant_id,    COALESCE(NULLIF(TRIM(t0.industry), ''), 'Unspecified') AS vertical  FROM tenants t0),d_vertical AS (  SELECT DISTINCT ti0.vertical AS vertical  FROM t_industry ti0),m_merchants AS (  SELECT    ti1.vertical AS vertical,    COUNT(DISTINCT ti1.tenant_id) AS merchant_count  FROM t_industry ti1  GROUP BY ti1.vertical),m_fee AS (  SELECT    ti2.vertical AS vertical,    SUM(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END)) AS fee_revenue_eur    SUM(CAST(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS fee_revenue_eur  FROM payouts p0  JOIN t_industry ti2 ON ti2.tenant_id = p0.tenant_id  LEFT JOIN exchange_rates er0    ON er0.base_code = 'EUR'    AND er0.quote_code = p0.currency_code    AND er0.as_of = CAST(p0.paid_at AS date)  WHERE p0.status = 'paid'    AND p0.paid_at >= DATE '2025-01-01'    AND p0.paid_at < DATE '2026-01-01'  GROUP BY ti2.vertical),m_shipping AS (  SELECT    ti3.vertical AS vertical,    SUM(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END)) AS shipping_cost_eur    SUM(CAST(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS shipping_cost_eur  FROM shipments s0  JOIN t_industry ti3 ON ti3.tenant_id = s0.tenant_id  LEFT JOIN exchange_rates er1    ON er1.base_code = 'EUR'    AND er1.quote_code = s0.currency_code    AND er1.as_of = CAST(s0.shipped_at AS date)  WHERE s0.shipped_at >= DATE '2025-01-01'    AND s0.shipped_at < DATE '2026-01-01'    AND s0.shipping_cost IS NOT NULL  GROUP BY ti3.vertical),m_refund AS (  SELECT    ti4.vertical AS vertical,    SUM(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END)) AS refund_amount_eur    SUM(CAST(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS refund_amount_eur  FROM order_returns r0  JOIN t_industry ti4 ON ti4.tenant_id = r0.tenant_id  LEFT JOIN exchange_rates er2    ON er2.base_code = 'EUR'    AND er2.quote_code = r0.currency_code    AND er2.as_of = CAST(COALESCE(r0.refunded_at, r0.requested_at) AS date)  WHERE r0.status = 'refunded'    AND COALESCE(r0.refunded_at, r0.requested_at) >= DATE '2025-01-01'    AND COALESCE(r0.refunded_at, r0.requested_at) < DATE '2026-01-01'  GROUP BY ti4.vertical),m_tickets AS (  SELECT    ti5.vertical AS vertical,    COUNT(*) AS ticket_count  FROM tickets tk0  JOIN t_industry ti5 ON ti5.tenant_id = tk0.tenant_id  WHERE tk0.created_at >= DATE '2025-01-01'    AND tk0.created_at < DATE '2026-01-01'  GROUP BY ti5.vertical)SELECT  d0.vertical AS vertical,  COALESCE(mm0.merchant_count, 0) AS merchant_count,  ROUND(COALESCE(mf0.fee_revenue_eur, 0), 2) AS fee_revenue_eur,  ROUND(COALESCE(ms0.shipping_cost_eur, 0), 2) AS shipping_cost_eur,  ROUND(COALESCE(mr0.refund_amount_eur, 0), 2) AS refund_amount_eur,  COALESCE(mf0.fee_revenue_eur, 0) AS fee_revenue_eur,  COALESCE(ms0.shipping_cost_eur, 0) AS shipping_cost_eur,  COALESCE(mr0.refund_amount_eur, 0) AS refund_amount_eur,  COALESCE(mt0.ticket_count, 0) AS support_ticket_count,  ROUND(COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0), 2) AS rough_net_contribution_eur,  ROUND(    CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL    ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur    END, 1  ) AS net_contribution_pct_of_fee_revenue  COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0) AS rough_net_contribution_eur,  CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL  ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur  END AS net_contribution_pct_of_fee_revenueFROM d_vertical d0LEFT JOIN m_merchants mm0 ON mm0.vertical = d0.verticalLEFT JOIN m_fee mf0 ON mf0.vertical = d0.verticalLEFT JOIN m_shipping ms0 ON ms0.vertical = d0.verticalLEFT JOIN m_refund mr0 ON mr0.vertical = d0.verticalLEFT JOIN m_tickets mt0 ON mt0.vertical = d0.verticalORDER BY rough_net_contribution_eur DESC, d0.vertical ASCORDER BY rough_net_contribution_eur DESC NULLS FIRST, d0.vertical ASC NULLS LAST

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

    WITH t_industry AS (
  SELECT
    t0.id AS tenant_id,
    COALESCE(NULLIF(TRIM(t0.industry), ''), 'Unspecified') AS vertical
  FROM tenants t0
),
d_vertical AS (
  SELECT DISTINCT ti0.vertical AS vertical
  FROM t_industry ti0
),
m_merchants AS (
  SELECT
    ti1.vertical AS vertical,
    COUNT(DISTINCT ti1.tenant_id) AS merchant_count
  FROM t_industry ti1
  GROUP BY ti1.vertical
),
m_fee AS (
  SELECT
    ti2.vertical AS vertical,
    SUM(CAST(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS fee_revenue_eur
  FROM payouts p0
  JOIN t_industry ti2 ON ti2.tenant_id = p0.tenant_id
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
    AND er0.quote_code = p0.currency_code
    AND er0.as_of = CAST(p0.paid_at AS date)
  WHERE p0.status = 'paid'
    AND p0.paid_at >= DATE '2025-01-01'
    AND p0.paid_at < DATE '2026-01-01'
  GROUP BY ti2.vertical
),
m_shipping AS (
  SELECT
    ti3.vertical AS vertical,
    SUM(CAST(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS shipping_cost_eur
  FROM shipments s0
  JOIN t_industry ti3 ON ti3.tenant_id = s0.tenant_id
  LEFT JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
    AND er1.quote_code = s0.currency_code
    AND er1.as_of = CAST(s0.shipped_at AS date)
  WHERE s0.shipped_at >= DATE '2025-01-01'
    AND s0.shipped_at < DATE '2026-01-01'
    AND s0.shipping_cost IS NOT NULL
  GROUP BY ti3.vertical
),
m_refund AS (
  SELECT
    ti4.vertical AS vertical,
    SUM(CAST(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END) AS NUMERIC(18,6))) AS refund_amount_eur
  FROM order_returns r0
  JOIN t_industry ti4 ON ti4.tenant_id = r0.tenant_id
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
    AND er2.quote_code = r0.currency_code
    AND er2.as_of = CAST(COALESCE(r0.refunded_at, r0.requested_at) AS date)
  WHERE r0.status = 'refunded'
    AND COALESCE(r0.refunded_at, r0.requested_at) >= DATE '2025-01-01'
    AND COALESCE(r0.refunded_at, r0.requested_at) < DATE '2026-01-01'
  GROUP BY ti4.vertical
),
m_tickets AS (
  SELECT
    ti5.vertical AS vertical,
    COUNT(*) AS ticket_count
  FROM tickets tk0
  JOIN t_industry ti5 ON ti5.tenant_id = tk0.tenant_id
  WHERE tk0.created_at >= DATE '2025-01-01'
    AND tk0.created_at < DATE '2026-01-01'
  GROUP BY ti5.vertical
)
SELECT
  d0.vertical AS vertical,
  COALESCE(mm0.merchant_count, 0) AS merchant_count,
  COALESCE(mf0.fee_revenue_eur, 0) AS fee_revenue_eur,
  COALESCE(ms0.shipping_cost_eur, 0) AS shipping_cost_eur,
  COALESCE(mr0.refund_amount_eur, 0) AS refund_amount_eur,
  COALESCE(mt0.ticket_count, 0) AS support_ticket_count,
  COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0) AS rough_net_contribution_eur,
  CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL
  ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur
  END AS net_contribution_pct_of_fee_revenue
FROM d_vertical d0
LEFT JOIN m_merchants mm0 ON mm0.vertical = d0.vertical
LEFT JOIN m_fee mf0 ON mf0.vertical = d0.vertical
LEFT JOIN m_shipping ms0 ON ms0.vertical = d0.vertical
LEFT JOIN m_refund mr0 ON mr0.vertical = d0.vertical
LEFT JOIN m_tickets mt0 ON mt0.vertical = d0.vertical
ORDER BY rough_net_contribution_eur DESC NULLS FIRST, d0.vertical ASC NULLS LAST
    10 ms100 ms1 sHyper103 msDuckDB51.4 msGizmoSQL55.4 msclickhouse_brew295 msFirebolt204 msDataFusion201 msClickHouse127 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q216 on the start page, with every configuration.

  8. 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Hannah Brooks (analyst for the Chief Executive Officer):

Title: Directional unit economics by vertical, 2025 (first pass)
Question: Using only the revenue and cost signals we currently trust, which verticals look directionally profitable to grow versus costly to serve?
Grain: one row per merchant vertical (tenants.industry)
Columns:
- vertical
- merchant count
- fee revenue (EUR, 2025, our validated processing-fee figure)
- shipping cost (EUR, 2025)
- refund amount (EUR, 2025)
- support ticket count (2025)
- rough net contribution (EUR, = fee revenue − shipping cost − refund amount)
- net contribution as % of fee revenue
Details: Source: aggregate the same underlying data as the validated 'Revenue by vertical' report (payouts, status='paid', paid_at in 2025, EUR via exchange_rates) and the 'Cost-driver volume inventory by merchant' report (shipments, order_returns-linked payment_refunds, tickets), rolled up from merchant to tenants.industry. This is explicitly a first-pass, directional view, not a final cost-to-serve model: 'rough net contribution' nets only the cost signals we have EUR amounts for (shipping, refunds) against fee revenue, and deliberately excludes support cost, carrier-performance penalties, engineering-reliability cost, and the still-missing platform-plan revenue line — please label it as such in the report notes. Group null/blank industry into 'Unspecified'. Order by rough net contribution EUR descending, tie-break by vertical name ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH t_industry AS (
  SELECT
    t0.id AS tenant_id,
    COALESCE(NULLIF(TRIM(t0.industry), ''), 'Unspecified') AS vertical
  FROM tenants t0
),
d_vertical AS (
  SELECT DISTINCT ti0.vertical AS vertical
  FROM t_industry ti0
),
m_merchants AS (
  SELECT
    ti1.vertical AS vertical,
    COUNT(DISTINCT ti1.tenant_id) AS merchant_count
  FROM t_industry ti1
  GROUP BY ti1.vertical
),
m_fee AS (
  SELECT
    ti2.vertical AS vertical,
    SUM(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END)) AS fee_revenue_eur
  FROM payouts p0
  JOIN t_industry ti2 ON ti2.tenant_id = p0.tenant_id
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
    AND er0.quote_code = p0.currency_code
    AND er0.as_of = CAST(p0.paid_at AS date)
  WHERE p0.status = 'paid'
    AND p0.paid_at >= DATE '2025-01-01'
    AND p0.paid_at < DATE '2026-01-01'
  GROUP BY ti2.vertical
),
m_shipping AS (
  SELECT
    ti3.vertical AS vertical,
    SUM(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END)) AS shipping_cost_eur
  FROM shipments s0
  JOIN t_industry ti3 ON ti3.tenant_id = s0.tenant_id
  LEFT JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
    AND er1.quote_code = s0.currency_code
    AND er1.as_of = CAST(s0.shipped_at AS date)
  WHERE s0.shipped_at >= DATE '2025-01-01'
    AND s0.shipped_at < DATE '2026-01-01'
    AND s0.shipping_cost IS NOT NULL
  GROUP BY ti3.vertical
),
m_refund AS (
  SELECT
    ti4.vertical AS vertical,
    SUM(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END)) AS refund_amount_eur
  FROM order_returns r0
  JOIN t_industry ti4 ON ti4.tenant_id = r0.tenant_id
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
    AND er2.quote_code = r0.currency_code
    AND er2.as_of = CAST(COALESCE(r0.refunded_at, r0.requested_at) AS date)
  WHERE r0.status = 'refunded'
    AND COALESCE(r0.refunded_at, r0.requested_at) >= DATE '2025-01-01'
    AND COALESCE(r0.refunded_at, r0.requested_at) < DATE '2026-01-01'
  GROUP BY ti4.vertical
),
m_tickets AS (
  SELECT
    ti5.vertical AS vertical,
    COUNT(*) AS ticket_count
  FROM tickets tk0
  JOIN t_industry ti5 ON ti5.tenant_id = tk0.tenant_id
  WHERE tk0.created_at >= DATE '2025-01-01'
    AND tk0.created_at < DATE '2026-01-01'
  GROUP BY ti5.vertical
)
SELECT
  d0.vertical AS vertical,
  COALESCE(mm0.merchant_count, 0) AS merchant_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0), 2) AS fee_revenue_eur,
  ROUND(COALESCE(ms0.shipping_cost_eur, 0), 2) AS shipping_cost_eur,
  ROUND(COALESCE(mr0.refund_amount_eur, 0), 2) AS refund_amount_eur,
  COALESCE(mt0.ticket_count, 0) AS support_ticket_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0), 2) AS rough_net_contribution_eur,
  ROUND(
    CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL
    ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur
    END, 1
  ) AS net_contribution_pct_of_fee_revenue
FROM d_vertical d0
LEFT JOIN m_merchants mm0 ON mm0.vertical = d0.vertical
LEFT JOIN m_fee mf0 ON mf0.vertical = d0.vertical
LEFT JOIN m_shipping ms0 ON ms0.vertical = d0.vertical
LEFT JOIN m_refund mr0 ON mr0.vertical = d0.vertical
LEFT JOIN m_tickets mt0 ON mt0.vertical = d0.vertical
ORDER BY rough_net_contribution_eur DESC, d0.vertical ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: vertical, merchant_count, fee_revenue_eur, shipping_cost_eur, refund_amount_eur, support_ticket_count, rough_net_contribution_eur, net_contribution_pct_of_fee_revenue
    • BeaLooks good. Submitting.
    • Bea calls submit
      WITH t_industry AS (
  SELECT
    t0.id AS tenant_id,
    COALESCE(NULLIF(TRIM(t0.industry), ''), 'Unspecified') AS vertical
  FROM tenants t0
),
d_vertical AS (
  SELECT DISTINCT ti0.vertical AS vertical
  FROM t_industry ti0
),
m_merchants AS (
  SELECT
    ti1.vertical AS vertical,
    COUNT(DISTINCT ti1.tenant_id) AS merchant_count
  FROM t_industry ti1
  GROUP BY ti1.vertical
),
m_fee AS (
  SELECT
    ti2.vertical AS vertical,
    SUM(p0.fee_amount / COALESCE(er0.rate, CASE WHEN p0.currency_code = 'EUR' THEN 1.0 END)) AS fee_revenue_eur
  FROM payouts p0
  JOIN t_industry ti2 ON ti2.tenant_id = p0.tenant_id
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
    AND er0.quote_code = p0.currency_code
    AND er0.as_of = CAST(p0.paid_at AS date)
  WHERE p0.status = 'paid'
    AND p0.paid_at >= DATE '2025-01-01'
    AND p0.paid_at < DATE '2026-01-01'
  GROUP BY ti2.vertical
),
m_shipping AS (
  SELECT
    ti3.vertical AS vertical,
    SUM(s0.shipping_cost / COALESCE(er1.rate, CASE WHEN s0.currency_code = 'EUR' THEN 1.0 END)) AS shipping_cost_eur
  FROM shipments s0
  JOIN t_industry ti3 ON ti3.tenant_id = s0.tenant_id
  LEFT JOIN exchange_rates er1
    ON er1.base_code = 'EUR'
    AND er1.quote_code = s0.currency_code
    AND er1.as_of = CAST(s0.shipped_at AS date)
  WHERE s0.shipped_at >= DATE '2025-01-01'
    AND s0.shipped_at < DATE '2026-01-01'
    AND s0.shipping_cost IS NOT NULL
  GROUP BY ti3.vertical
),
m_refund AS (
  SELECT
    ti4.vertical AS vertical,
    SUM(r0.refund_total / COALESCE(er2.rate, CASE WHEN r0.currency_code = 'EUR' THEN 1.0 END)) AS refund_amount_eur
  FROM order_returns r0
  JOIN t_industry ti4 ON ti4.tenant_id = r0.tenant_id
  LEFT JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
    AND er2.quote_code = r0.currency_code
    AND er2.as_of = CAST(COALESCE(r0.refunded_at, r0.requested_at) AS date)
  WHERE r0.status = 'refunded'
    AND COALESCE(r0.refunded_at, r0.requested_at) >= DATE '2025-01-01'
    AND COALESCE(r0.refunded_at, r0.requested_at) < DATE '2026-01-01'
  GROUP BY ti4.vertical
),
m_tickets AS (
  SELECT
    ti5.vertical AS vertical,
    COUNT(*) AS ticket_count
  FROM tickets tk0
  JOIN t_industry ti5 ON ti5.tenant_id = tk0.tenant_id
  WHERE tk0.created_at >= DATE '2025-01-01'
    AND tk0.created_at < DATE '2026-01-01'
  GROUP BY ti5.vertical
)
SELECT
  d0.vertical AS vertical,
  COALESCE(mm0.merchant_count, 0) AS merchant_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0), 2) AS fee_revenue_eur,
  ROUND(COALESCE(ms0.shipping_cost_eur, 0), 2) AS shipping_cost_eur,
  ROUND(COALESCE(mr0.refund_amount_eur, 0), 2) AS refund_amount_eur,
  COALESCE(mt0.ticket_count, 0) AS support_ticket_count,
  ROUND(COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0), 2) AS rough_net_contribution_eur,
  ROUND(
    CASE WHEN COALESCE(mf0.fee_revenue_eur, 0) = 0 THEN NULL
    ELSE 100.0 * (COALESCE(mf0.fee_revenue_eur, 0) - COALESCE(ms0.shipping_cost_eur, 0) - COALESCE(mr0.refund_amount_eur, 0)) / mf0.fee_revenue_eur
    END, 1
  ) AS net_contribution_pct_of_fee_revenue
FROM d_vertical d0
LEFT JOIN m_merchants mm0 ON mm0.vertical = d0.vertical
LEFT JOIN m_fee mf0 ON mf0.vertical = d0.vertical
LEFT JOIN m_shipping ms0 ON ms0.vertical = d0.vertical
LEFT JOIN m_refund mr0 ON mr0.vertical = d0.vertical
LEFT JOIN m_tickets mt0 ON mt0.vertical = d0.vertical
ORDER BY rough_net_contribution_eur DESC, d0.vertical ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of what the report does: - **Grain:** one row per `tenants.industry` (nulls/blanks → "Unspecified"). - **Fee revenue:** `payouts.fee_amount` where `status='paid'` and `paid_at` in 2025, converted to EUR via `exchange_rates` (daily EUR→currency rate on the paid date). - **Shipping cost:** `shipments.shipping_cost` for shipments with `shipped_at` in 2025, converted to EUR the same way. - **Refund amount:** `order_returns.refund_total` where `status='refunded'`, dated by `refunded_at` (falling back to `requested_at`) in 2025, converted to EUR — noted as the closest proxy since there's no direct FK from `payment_refunds` to `order_returns`. - **Support tickets:** count of `tickets` created in 2025. - **Rough net contribution** = fee revenue − shipping − refunds, plus that as a % of fee revenue. - Sorted by net contribution descending, tie-broken by vertical name. The submit note flags the refund-sourcing assumption and reiterates the directional, first-pass nature of the net contribution figure.

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

    Bea's transcript (284 KB) · Hannah Brooks's transcript (872 KB) · Maren Okafor's transcript (278 KB) · the night's plan (6 KB) · the night's reports (7 KB) · the week's memo (3 KB)