DB Olympics

Queries · q217 · Chief Executive Officer · week 2, night 4

Vertical unit economics, rate-based view, 2025

“Which verticals look efficient versus costly to serve, measured as comparable rates against gross payment volume rather than misleading absolute euros against our thin fee take?”

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 SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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 4, request 1 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.

    Vertical unit economics, rate-based view, 2025
    Question
    Which verticals look efficient versus costly to serve, measured as comparable rates against gross payment volume rather than misleading absolute euros against our thin fee take?
    One row per
    merchant vertical (tenants.industry)
    Columns
    • vertical
    • merchant count
    • 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)
    • order count (2025)
    • tickets per 100 orders

  4. Grace 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 tenant_industry AS (
    SELECT
        id AS tenant_id,
        COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical
    FROM tenants
),

payout_totals 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 = 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.tenant_id
),

refund_totals 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 = CAST(COALESCE(r.refunded_at, r.requested_at) AS date)
    WHERE r.status = 'refunded'
        AND COALESCE(r.refunded_at, r.requested_at) >= DATE '2025-01-01'
        AND COALESCE(r.refunded_at, r.requested_at) < DATE '2026-01-01'
    GROUP BY r.tenant_id
),

shipping_totals AS (
    SELECT
        o.tenant_id,
        SUM(s.shipping_cost / er.rate) AS shipping_eur
    FROM shipments s
    JOIN fulfillments f ON f.id = s.fulfillment_id
    JOIN orders o ON o.id = f.order_id
    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 o.tenant_id
),

order_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded')
        AND placed_at >= DATE '2025-01-01'
        AND placed_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

ticket_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS ticket_count
    FROM tickets
    WHERE created_at >= DATE '2025-01-01'
        AND created_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

merchant_counts AS (
    SELECT
        vertical,
        COUNT(*) AS merchant_count
    FROM tenant_industry
    GROUP BY vertical
),

tenant_metrics AS (
    SELECT
        ti.tenant_id,
        ti.vertical,
        COALESCE(pt.gross_eur, 0) AS gross_eur,
        COALESCE(pt.fee_eur, 0) AS fee_eur,
        COALESCE(rt.refund_eur, 0) AS refund_eur,
        COALESCE(st.shipping_eur, 0) AS shipping_eur,
        COALESCE(oc.order_count, 0) AS order_count,
        COALESCE(tc.ticket_count, 0) AS ticket_count
    FROM tenant_industry ti
    LEFT JOIN payout_totals pt ON pt.tenant_id = ti.tenant_id
    LEFT JOIN refund_totals rt ON rt.tenant_id = ti.tenant_id
    LEFT JOIN shipping_totals st ON st.tenant_id = ti.tenant_id
    LEFT JOIN order_counts oc ON oc.tenant_id = ti.tenant_id
    LEFT JOIN ticket_counts tc ON tc.tenant_id = ti.tenant_id
),

vertical_agg AS (
    SELECT
        vertical,
        SUM(gross_eur) AS gross_eur,
        SUM(fee_eur) AS fee_eur,
        SUM(refund_eur) AS refund_eur,
        SUM(shipping_eur) AS shipping_eur,
        SUM(order_count) AS order_count,
        SUM(ticket_count) AS ticket_count
    FROM tenant_metrics
    GROUP BY vertical
)

SELECT
    va.vertical AS vertical,
    mc.merchant_count AS merchant_count,
    ROUND(va.gross_eur, 2) AS gross_payment_volume_eur,
    ROUND(va.fee_eur, 2) AS fee_revenue_eur,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END, 2) AS take_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END, 2) AS refund_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END, 2) AS shipping_cost_rate_pct,
    va.order_count AS order_count_2025,
    ROUND(CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END, 2) AS tickets_per_100_orders
FROM vertical_agg va
JOIN merchant_counts mc ON mc.vertical = va.vertical
ORDER BY va.gross_eur DESC, va.vertical ASC

    “The query compiles and returns the expected 9 columns, one row per vertical.”

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

    “Submitted. The report rolls up each tenant's 2025 payouts, refunds, shipping costs, orders and tickets by tenants.industry (null/blank → "Unspecified"), converts all monetary amounts to EUR using the exchange rate as of the relevant date, and expresses refund rate, shipping cost rate and take rate against gross payment volume (not fee revenue) so the verticals are comparable, ordered by gross payment volume descending.”

  5. The report comes back overnight

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

    vertical                  | merchant_count | gross_payment_volume_eur | fee_revenue_eur | take_rate_pct | refund_rate_pct | shipping_cost_rate_pct | order_count_2025 | tickets_per_100_orders
--------------------------+----------------+--------------------------+-----------------+---------------+-----------------+------------------------+------------------+-----------------------
Apparel & Footwear        |             25 |               1070404.44 |         29028.1 |          2.71 |           21.93 |                   2.66 |             6850 |                  14.12
Sports & Outdoor          |             13 |                209893.74 |         5796.45 |          2.76 |           12.25 |                   1.29 |              974 |                  12.53
Office Supplies (B2B)     |              4 |                176549.46 |         4592.96 |           2.6 |           14.37 |                  46.91 |              517 |                   7.93
Home & Living             |             16 |                128877.56 |         3085.55 |          2.39 |            7.21 |                   3.79 |             1597 |                   13.9
Beauty                    |             15 |                125191.99 |         2346.81 |          1.87 |             3.9 |                   4.66 |             1493 |                  14.07
Industrial Supplies (B2B) |              5 |                107753.57 |         3499.84 |          3.25 |            3.73 |                   5.52 |              762 |                  10.63
Food & Coffee             |             11 |                 77170.34 |         2597.96 |          3.37 |             0.4 |                  13.15 |             7108 |                   6.33
Pet Supplies              |             10 |                 55346.78 |         1468.94 |          2.65 |            1.21 |                  13.53 |             1409 |                   8.66
Consumer Electronics      |              9 |                 31713.49 |          529.66 |          1.67 |           14.33 |                   1.13 |              555 |                  13.51
Books & Stationery        |              6 |                   5565.7 |          145.03 |          2.61 |            2.29 |                   6.02 |              154 |                  14.94
Jewelry & Accessories     |              6 |                  2982.02 |           94.56 |          3.17 |             0.0 |                   1.59 |               48 |                  14.58
(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 q217

    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) (4×)
    • made explicita selected t.col gets AS col, its standard name made explicit (5×)
    • 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 tenant_industry AS (    SELECT        id AS tenant_id,        COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical    FROM tenants),payout_totals AS (    SELECT        p.tenant_id,        SUM(p.gross_amount / er.rate) AS gross_eur,        SUM(p.fee_amount / er.rate) AS fee_eur        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.status = 'paid'        AND p.paid_at >= DATE '2025-01-01'        AND p.paid_at < DATE '2026-01-01'    GROUP BY p.tenant_id),refund_totals AS (    SELECT        r.tenant_id,        SUM(r.refund_total / er.rate) AS refund_eur        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(COALESCE(r.refunded_at, r.requested_at) AS date)    WHERE r.status = 'refunded'        AND COALESCE(r.refunded_at, r.requested_at) >= DATE '2025-01-01'        AND COALESCE(r.refunded_at, r.requested_at) < DATE '2026-01-01'    GROUP BY r.tenant_id),shipping_totals AS (    SELECT        o.tenant_id,        SUM(s.shipping_cost / er.rate) AS shipping_eur        o.tenant_id AS tenant_id,        SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_eur    FROM shipments s    JOIN fulfillments f ON f.id = s.fulfillment_id    JOIN orders o ON o.id = f.order_id    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 o.tenant_id),order_counts AS (    SELECT        tenant_id,        COUNT(*) AS order_count    FROM orders    WHERE status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded')        AND placed_at >= DATE '2025-01-01'        AND placed_at < DATE '2026-01-01'    GROUP BY tenant_id),ticket_counts AS (    SELECT        tenant_id,        COUNT(*) AS ticket_count    FROM tickets    WHERE created_at >= DATE '2025-01-01'        AND created_at < DATE '2026-01-01'    GROUP BY tenant_id),merchant_counts AS (    SELECT        vertical,        COUNT(*) AS merchant_count    FROM tenant_industry    GROUP BY vertical),tenant_metrics AS (    SELECT        ti.tenant_id,        ti.vertical,        ti.tenant_id AS tenant_id,        ti.vertical AS vertical,        COALESCE(pt.gross_eur, 0) AS gross_eur,        COALESCE(pt.fee_eur, 0) AS fee_eur,        COALESCE(rt.refund_eur, 0) AS refund_eur,        COALESCE(st.shipping_eur, 0) AS shipping_eur,        COALESCE(oc.order_count, 0) AS order_count,        COALESCE(tc.ticket_count, 0) AS ticket_count    FROM tenant_industry ti    LEFT JOIN payout_totals pt ON pt.tenant_id = ti.tenant_id    LEFT JOIN refund_totals rt ON rt.tenant_id = ti.tenant_id    LEFT JOIN shipping_totals st ON st.tenant_id = ti.tenant_id    LEFT JOIN order_counts oc ON oc.tenant_id = ti.tenant_id    LEFT JOIN ticket_counts tc ON tc.tenant_id = ti.tenant_id),vertical_agg AS (    SELECT        vertical,        SUM(gross_eur) AS gross_eur,        SUM(fee_eur) AS fee_eur,        SUM(refund_eur) AS refund_eur,        SUM(shipping_eur) AS shipping_eur,        SUM(order_count) AS order_count,        SUM(ticket_count) AS ticket_count    FROM tenant_metrics    GROUP BY vertical)SELECT    va.vertical AS vertical,    mc.merchant_count AS merchant_count,    ROUND(va.gross_eur, 2) AS gross_payment_volume_eur,    ROUND(va.fee_eur, 2) AS fee_revenue_eur,    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END, 2) AS take_rate_pct,    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END, 2) AS refund_rate_pct,    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END, 2) AS shipping_cost_rate_pct,    va.gross_eur AS gross_payment_volume_eur,    va.fee_eur AS fee_revenue_eur,    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END AS take_rate_pct,    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END AS refund_rate_pct,    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END AS shipping_cost_rate_pct,    va.order_count AS order_count_2025,    ROUND(CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END, 2) AS tickets_per_100_orders    CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END AS tickets_per_100_ordersFROM vertical_agg vaJOIN merchant_counts mc ON mc.vertical = va.verticalORDER BY va.gross_eur DESC, va.vertical ASCORDER BY va.gross_eur DESC NULLS FIRST, va.vertical ASC NULLS LAST

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

    WITH tenant_industry AS (
    SELECT
        id AS tenant_id,
        COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical
    FROM tenants
),

payout_totals 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.status = 'paid'
        AND p.paid_at >= DATE '2025-01-01'
        AND p.paid_at < DATE '2026-01-01'
    GROUP BY p.tenant_id
),

refund_totals 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(COALESCE(r.refunded_at, r.requested_at) AS date)
    WHERE r.status = 'refunded'
        AND COALESCE(r.refunded_at, r.requested_at) >= DATE '2025-01-01'
        AND COALESCE(r.refunded_at, r.requested_at) < DATE '2026-01-01'
    GROUP BY r.tenant_id
),

shipping_totals AS (
    SELECT
        o.tenant_id AS tenant_id,
        SUM(CAST(s.shipping_cost / er.rate AS NUMERIC(18,6))) AS shipping_eur
    FROM shipments s
    JOIN fulfillments f ON f.id = s.fulfillment_id
    JOIN orders o ON o.id = f.order_id
    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 o.tenant_id
),

order_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded')
        AND placed_at >= DATE '2025-01-01'
        AND placed_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

ticket_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS ticket_count
    FROM tickets
    WHERE created_at >= DATE '2025-01-01'
        AND created_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

merchant_counts AS (
    SELECT
        vertical,
        COUNT(*) AS merchant_count
    FROM tenant_industry
    GROUP BY vertical
),

tenant_metrics AS (
    SELECT
        ti.tenant_id AS tenant_id,
        ti.vertical AS vertical,
        COALESCE(pt.gross_eur, 0) AS gross_eur,
        COALESCE(pt.fee_eur, 0) AS fee_eur,
        COALESCE(rt.refund_eur, 0) AS refund_eur,
        COALESCE(st.shipping_eur, 0) AS shipping_eur,
        COALESCE(oc.order_count, 0) AS order_count,
        COALESCE(tc.ticket_count, 0) AS ticket_count
    FROM tenant_industry ti
    LEFT JOIN payout_totals pt ON pt.tenant_id = ti.tenant_id
    LEFT JOIN refund_totals rt ON rt.tenant_id = ti.tenant_id
    LEFT JOIN shipping_totals st ON st.tenant_id = ti.tenant_id
    LEFT JOIN order_counts oc ON oc.tenant_id = ti.tenant_id
    LEFT JOIN ticket_counts tc ON tc.tenant_id = ti.tenant_id
),

vertical_agg AS (
    SELECT
        vertical,
        SUM(gross_eur) AS gross_eur,
        SUM(fee_eur) AS fee_eur,
        SUM(refund_eur) AS refund_eur,
        SUM(shipping_eur) AS shipping_eur,
        SUM(order_count) AS order_count,
        SUM(ticket_count) AS ticket_count
    FROM tenant_metrics
    GROUP BY vertical
)

SELECT
    va.vertical AS vertical,
    mc.merchant_count AS merchant_count,
    va.gross_eur AS gross_payment_volume_eur,
    va.fee_eur AS fee_revenue_eur,
    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END AS take_rate_pct,
    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END AS refund_rate_pct,
    CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END AS shipping_cost_rate_pct,
    va.order_count AS order_count_2025,
    CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END AS tickets_per_100_orders
FROM vertical_agg va
JOIN merchant_counts mc ON mc.vertical = va.vertical
ORDER BY va.gross_eur DESC NULLS FIRST, va.vertical ASC NULLS LAST
    100 ms1 sHyper429 msDuckDB193 msGizmoSQL161 msclickhouse_brew678 msFirebolterrorDataFusion545 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 11, Column 33: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "005ceba1-5f34-4c72-9029-fa2331e58069", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table6.gross_amount Decimal(14, 4) Decimal64(size = 0), __table7.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q217 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

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

Title: Vertical unit economics, rate-based view, 2025
Question: Which verticals look efficient versus costly to serve, measured as comparable rates against gross payment volume rather than misleading absolute euros against our thin fee take?
Grain: one row per merchant vertical (tenants.industry)
Columns:
- vertical
- merchant count
- 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)
- order count (2025)
- tickets per 100 orders
Details: Source: payouts (status='paid', paid_at in [2025-01-01,2026-01-01), EUR via exchange_rates as_of paid_at) for gross volume and fee revenue, rolled up from tenant to tenants.industry. order_returns (status='refunded', dated by refunded_at falling back to requested_at, refund_total converted to EUR via exchange_rates as_of that date) for refund amount — same source as last night's report, for consistency. shipments (shipped_at in 2025, shipping_cost converted to EUR as_of shipped_at) joined via fulfillments->orders->tenant_id for shipping cost. orders (status in 'paid','partially_fulfilled','fulfilled','refunded', placed_at in 2025) for order count. tickets (created_at in 2025) for ticket count, giving tickets per 100 orders = 100*tickets/orders. All three rate columns (refund rate, shipping cost rate, and implicitly ticket rate) should be expressed against gross payment volume or order count — not against fee revenue — so verticals are comparable on a like-for-like basis. Group null/blank industry into 'Unspecified'. Order by gross payment volume EUR descending, tie-break by vertical name ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH tenant_industry AS (
    SELECT
        id AS tenant_id,
        COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical
    FROM tenants
),

payout_totals 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 = 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.tenant_id
),

refund_totals 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 = CAST(COALESCE(r.refunded_at, r.requested_at) AS date)
    WHERE r.status = 'refunded'
        AND COALESCE(r.refunded_at, r.requested_at) >= DATE '2025-01-01'
        AND COALESCE(r.refunded_at, r.requested_at) < DATE '2026-01-01'
    GROUP BY r.tenant_id
),

shipping_totals AS (
    SELECT
        o.tenant_id,
        SUM(s.shipping_cost / er.rate) AS shipping_eur
    FROM shipments s
    JOIN fulfillments f ON f.id = s.fulfillment_id
    JOIN orders o ON o.id = f.order_id
    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 o.tenant_id
),

order_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded')
        AND placed_at >= DATE '2025-01-01'
        AND placed_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

ticket_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS ticket_count
    FROM tickets
    WHERE created_at >= DATE '2025-01-01'
        AND created_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

merchant_counts AS (
    SELECT
        vertical,
        COUNT(*) AS merchant_count
    FROM tenant_industry
    GROUP BY vertical
),

tenant_metrics AS (
    SELECT
        ti.tenant_id,
        ti.vertical,
        COALESCE(pt.gross_eur, 0) AS gross_eur,
        COALESCE(pt.fee_eur, 0) AS fee_eur,
        COALESCE(rt.refund_eur, 0) AS refund_eur,
        COALESCE(st.shipping_eur, 0) AS shipping_eur,
        COALESCE(oc.order_count, 0) AS order_count,
        COALESCE(tc.ticket_count, 0) AS ticket_count
    FROM tenant_industry ti
    LEFT JOIN payout_totals pt ON pt.tenant_id = ti.tenant_id
    LEFT JOIN refund_totals rt ON rt.tenant_id = ti.tenant_id
    LEFT JOIN shipping_totals st ON st.tenant_id = ti.tenant_id
    LEFT JOIN order_counts oc ON oc.tenant_id = ti.tenant_id
    LEFT JOIN ticket_counts tc ON tc.tenant_id = ti.tenant_id
),

vertical_agg AS (
    SELECT
        vertical,
        SUM(gross_eur) AS gross_eur,
        SUM(fee_eur) AS fee_eur,
        SUM(refund_eur) AS refund_eur,
        SUM(shipping_eur) AS shipping_eur,
        SUM(order_count) AS order_count,
        SUM(ticket_count) AS ticket_count
    FROM tenant_metrics
    GROUP BY vertical
)

SELECT
    va.vertical AS vertical,
    mc.merchant_count AS merchant_count,
    ROUND(va.gross_eur, 2) AS gross_payment_volume_eur,
    ROUND(va.fee_eur, 2) AS fee_revenue_eur,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END, 2) AS take_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END, 2) AS refund_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END, 2) AS shipping_cost_rate_pct,
    va.order_count AS order_count_2025,
    ROUND(CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END, 2) AS tickets_per_100_orders
FROM vertical_agg va
JOIN merchant_counts mc ON mc.vertical = va.vertical
ORDER BY va.gross_eur DESC, va.vertical ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: vertical, merchant_count, gross_payment_volume_eur, fee_revenue_eur, take_rate_pct, refund_rate_pct, shipping_cost_rate_pct, order_count_2025, tickets_per_100_orders
    • GraceThe query compiles and returns the expected 9 columns, one row per vertical.
    • Grace calls submit
      WITH tenant_industry AS (
    SELECT
        id AS tenant_id,
        COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical
    FROM tenants
),

payout_totals 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 = 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.tenant_id
),

refund_totals 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 = CAST(COALESCE(r.refunded_at, r.requested_at) AS date)
    WHERE r.status = 'refunded'
        AND COALESCE(r.refunded_at, r.requested_at) >= DATE '2025-01-01'
        AND COALESCE(r.refunded_at, r.requested_at) < DATE '2026-01-01'
    GROUP BY r.tenant_id
),

shipping_totals AS (
    SELECT
        o.tenant_id,
        SUM(s.shipping_cost / er.rate) AS shipping_eur
    FROM shipments s
    JOIN fulfillments f ON f.id = s.fulfillment_id
    JOIN orders o ON o.id = f.order_id
    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 o.tenant_id
),

order_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status IN ('paid', 'partially_fulfilled', 'fulfilled', 'refunded')
        AND placed_at >= DATE '2025-01-01'
        AND placed_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

ticket_counts AS (
    SELECT
        tenant_id,
        COUNT(*) AS ticket_count
    FROM tickets
    WHERE created_at >= DATE '2025-01-01'
        AND created_at < DATE '2026-01-01'
    GROUP BY tenant_id
),

merchant_counts AS (
    SELECT
        vertical,
        COUNT(*) AS merchant_count
    FROM tenant_industry
    GROUP BY vertical
),

tenant_metrics AS (
    SELECT
        ti.tenant_id,
        ti.vertical,
        COALESCE(pt.gross_eur, 0) AS gross_eur,
        COALESCE(pt.fee_eur, 0) AS fee_eur,
        COALESCE(rt.refund_eur, 0) AS refund_eur,
        COALESCE(st.shipping_eur, 0) AS shipping_eur,
        COALESCE(oc.order_count, 0) AS order_count,
        COALESCE(tc.ticket_count, 0) AS ticket_count
    FROM tenant_industry ti
    LEFT JOIN payout_totals pt ON pt.tenant_id = ti.tenant_id
    LEFT JOIN refund_totals rt ON rt.tenant_id = ti.tenant_id
    LEFT JOIN shipping_totals st ON st.tenant_id = ti.tenant_id
    LEFT JOIN order_counts oc ON oc.tenant_id = ti.tenant_id
    LEFT JOIN ticket_counts tc ON tc.tenant_id = ti.tenant_id
),

vertical_agg AS (
    SELECT
        vertical,
        SUM(gross_eur) AS gross_eur,
        SUM(fee_eur) AS fee_eur,
        SUM(refund_eur) AS refund_eur,
        SUM(shipping_eur) AS shipping_eur,
        SUM(order_count) AS order_count,
        SUM(ticket_count) AS ticket_count
    FROM tenant_metrics
    GROUP BY vertical
)

SELECT
    va.vertical AS vertical,
    mc.merchant_count AS merchant_count,
    ROUND(va.gross_eur, 2) AS gross_payment_volume_eur,
    ROUND(va.fee_eur, 2) AS fee_revenue_eur,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.fee_eur / va.gross_eur END, 2) AS take_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.refund_eur / va.gross_eur END, 2) AS refund_rate_pct,
    ROUND(CASE WHEN va.gross_eur = 0 THEN NULL ELSE 100.0 * va.shipping_eur / va.gross_eur END, 2) AS shipping_cost_rate_pct,
    va.order_count AS order_count_2025,
    ROUND(CASE WHEN va.order_count = 0 THEN NULL ELSE 100.0 * va.ticket_count / va.order_count END, 2) AS tickets_per_100_orders
FROM vertical_agg va
JOIN merchant_counts mc ON mc.vertical = va.vertical
ORDER BY va.gross_eur DESC, va.vertical ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. The report rolls up each tenant's 2025 payouts, refunds, shipping costs, orders and tickets by `tenants.industry` (null/blank → "Unspecified"), converts all monetary amounts to EUR using the exchange rate as of the relevant date, and expresses refund rate, shipping cost rate and take rate against gross payment volume (not fee revenue) so the verticals are comparable, ordered by gross payment volume descending.

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

    Grace's transcript (281 KB) · Hannah Brooks's transcript (872 KB) · Maren Okafor's transcript (278 KB) · the night's plan (6 KB) · the night's reports (10 KB) · the week's memo (3 KB)