DB Olympics

Queries · q002 · Chief Executive Officer · week 1, night 1

Revenue concentration trend by quarter

“Has Tidewick's revenue become more or less concentrated among its largest merchants over the last two years?”

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. Maren Okafor writes to Hannah Brooks

    The manager, at the start of week 1

    Subject: A few questions to get me oriented before the board meeting

    Hi Hannah,

    Thanks for taking these on this week — I know slots are tight, so please prioritize in the order below and don't force everything into this week if it doesn't fit cleanly. I'd rather get three solid answers than five rushed ones.

    1. Where does our revenue actually come from? I'd like a sense of how concentrated we are — which merchants and which verticals are driving the bulk of our volume and fees today, and how that's shifted over the last year or so. I'm trying to understand if we're overly dependent on a handful of accounts.
    2. How healthy is our growth? Are we gaining merchants and transaction volume, or is it flat/declining? I'd like to see the trend over the last several quarters, and ideally whether growth is coming from new merchants or from existing ones doing more.
    3. Where do we make our money, and how is that mix changing? Between platform fees and payment processing, I want to understand the balance, and whether one is growing faster than the other.
    4. What's working and what's not, vertical by vertical? I'd like a simple read on which verticals are thriving versus struggling — whether that's measured by growth, retention, or merchant satisfaction, whatever tells the clearest story.
    5. (If time allows) Are there any warning signs I should know about — merchants at risk of leaving, payment or fulfillment problems, support issues — anything that could become a board-level concern if it's not already on my radar.

    What I need this for: I'm building my mental model of the business ahead of the board meeting at the end of January, and I want my first real conversation with the board to show I understand where we're strong, where we're exposed, and what I'm watching closely. Your Friday memo will be the backbone of that.

    Let me know if any of these need narrowing once you've had a look at what's feasible — happy to discuss before you lock in the report requests.

    Thanks, Maren

  2. Hannah Brooks plans the night

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

    Maren's priority #1 is revenue concentration (which merchants/verticals drive volume and fees, and how that's shifted). I'm starting there with three complementary views at different grains, all built on payouts (gross/fee/net) since that's the cleanest, consistently-defined measure of money actually settled to/kept from merchants (the platform-plan billing of merchants isn't separately modeled in the warehouse as far as I can tell — I'll sanity-check that assumption before committing more slots to it). Report 1 gives merchant-level concentration today; Report 2 shows how that concentration has moved over the last 2 years; Report 3 rolls the same money up by vertical. Together these should let me describe dependency risk and set up next week's growth and fee-mix questions. Saving growth/fee-mix/vertical-health/risk questions for later nights per Maren's note that it's fine not to force everything into week one.

    Revenue concentration trend by quarter
    Question
    Has Tidewick's revenue become more or less concentrated among its largest merchants over the last two years?
    One row per
    calendar quarter
    Columns
    • quarter (e.g. 2024-Q1)
    • active merchants with a payout that quarter
    • total fee revenue (EUR)
    • fee revenue from top 5 merchants that quarter (EUR)
    • top-5 share of fee revenue (%)
    • fee revenue from top 10 merchants that quarter (EUR)
    • top-10 share of fee revenue (%)

  3. 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 payout_fees_eur AS (
  SELECT
    p.tenant_id,
    p.paid_at,
    p.fee_amount,
    p.currency_code,
    er.rate,
    p.fee_amount / er.rate AS fee_amount_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 IS NOT NULL
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
quarterly AS (
  SELECT
    cd.year_num,
    cd.quarter_num,
    cd.year_num || '-Q' || cd.quarter_num AS quarter_label,
    pf.tenant_id,
    SUM(pf.fee_amount_eur) AS fee_eur
  FROM payout_fees_eur pf
  JOIN calendar_days cd ON cd.calendar_date = CAST(pf.paid_at AS date)
  GROUP BY 1, 2, 3, 4
),
ranked AS (
  SELECT
    quarterly.*,
    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC, tenant_id) AS rnk
  FROM quarterly
),
quarter_totals AS (
  SELECT
    quarter_label,
    year_num,
    quarter_num,
    COUNT(DISTINCT tenant_id) AS active_merchants,
    SUM(fee_eur) AS total_fee_eur
  FROM quarterly
  GROUP BY 1, 2, 3
),
top_sums AS (
  SELECT
    quarter_label,
    SUM(fee_eur) FILTER (WHERE rnk <= 5) AS top5_fee_eur,
    SUM(fee_eur) FILTER (WHERE rnk <= 10) AS top10_fee_eur
  FROM ranked
  GROUP BY 1
)
SELECT
  qt.quarter_label AS quarter,
  qt.active_merchants,
  ROUND(qt.total_fee_eur, 2) AS total_fee_revenue_eur,
  ROUND(ts.top5_fee_eur, 2) AS top5_fee_revenue_eur,
  ROUND(100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top5_share_pct,
  ROUND(ts.top10_fee_eur, 2) AS top10_fee_revenue_eur,
  ROUND(100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top10_share_pct
FROM quarter_totals qt
JOIN top_sums ts ON ts.quarter_label = qt.quarter_label
ORDER BY qt.year_num, qt.quarter_num

    “Query compiles cleanly and returns exactly the 7 requested columns at one-row-per-quarter grain. Submitting.”

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

    “Submitted. The report computes, for each of the 8 quarters (2024-Q1 through 2025-Q4): distinct active merchants with settled payouts, total EUR fee revenue, top-5 and top-10 merchant fee revenue and their shares of the quarter total — ranked by each tenant's quarterly fee revenue after EUR conversion via exchange_rates (EUR base, payout currency quote, rate as of paid_at date).”

  4. The report comes back overnight

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

    quarter | active_merchants | total_fee_revenue_eur | top5_fee_revenue_eur | top5_share_pct | top10_fee_revenue_eur | top10_share_pct
--------+------------------+-----------------------+----------------------+----------------+-----------------------+----------------
2024-Q1 |               41 |               9224.35 |              7376.14 |           80.0 |               8325.44 |            90.3
2024-Q2 |               40 |               9506.65 |              7062.46 |           74.3 |               8360.34 |            87.9
2024-Q3 |               44 |               8562.88 |              6064.75 |           70.8 |               7289.39 |            85.1
2024-Q4 |               47 |              15633.97 |             11734.94 |           75.1 |              13312.84 |            85.2
2025-Q1 |               47 |               11429.2 |              7646.48 |           66.9 |               9485.42 |            83.0
2025-Q2 |               49 |              11612.46 |              8416.79 |           72.5 |              10001.83 |            86.1
2025-Q3 |               52 |              11047.02 |              8002.07 |           72.4 |               9260.04 |            83.8
2025-Q4 |               55 |              19097.17 |              14476.7 |           75.8 |              16429.07 |            86.0
(8 rows)

  5. What came of it

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

    Hannah Brooks's memo to Maren Okafor at the end of the week:

    • 1. Revenue is heavily concentrated in a handful of merchants and one vertical

      Trailing 12 months (TTM), Cragsley Outfitters alone drives 41.9% of fee revenue (€22.3k of €53.2k), and the top 5 merchants drive 72% (Merchant revenue concentration). At the vertical level, Apparel & Footwear is 54.6% of all fee revenue from just 12 merchants (Revenue by vertical, corrected). Concentration has been fairly stable over 8 quarters — top-5 share has ranged 67–80% with no clear trend (Revenue concentration trend by quarter). Confidence: high — the merchant and vertical totals now reconcile to the same €53,186 figure after I caught and fixed a vertical-report bug mid-week.

    It builds on:

  6. It becomes benchmark query q002

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (9×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
    The benchmark text, against the submitted SQL
    WITH payout_fees_eur AS (  SELECT    p.tenant_id,    p.paid_at,    p.fee_amount,    p.currency_code,    er.rate,    p.fee_amount / er.rate AS fee_amount_eur    p.tenant_id AS tenant_id,    p.paid_at AS paid_at,    p.fee_amount AS fee_amount,    p.currency_code AS currency_code,    er.rate AS rate,    CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) AS fee_amount_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 IS NOT NULL    AND p.paid_at >= DATE '2024-01-01'    AND p.paid_at < DATE '2026-01-01'),quarterly AS (  SELECT    cd.year_num,    cd.quarter_num,    cd.year_num AS year_num,    cd.quarter_num AS quarter_num,    cd.year_num || '-Q' || cd.quarter_num AS quarter_label,    pf.tenant_id,    pf.tenant_id AS tenant_id,    SUM(pf.fee_amount_eur) AS fee_eur  FROM payout_fees_eur pf  JOIN calendar_days cd ON cd.calendar_date = CAST(pf.paid_at AS date)  GROUP BY 1, 2, 3, 4),ranked AS (  SELECT    quarterly.*,    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC, tenant_id) AS rnk    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rnk  FROM quarterly),quarter_totals AS (  SELECT    quarter_label,    year_num,    quarter_num,    COUNT(DISTINCT tenant_id) AS active_merchants,    SUM(fee_eur) AS total_fee_eur  FROM quarterly  GROUP BY 1, 2, 3),top_sums AS (  SELECT    quarter_label,    SUM(fee_eur) FILTER (WHERE rnk <= 5) AS top5_fee_eur,    SUM(fee_eur) FILTER (WHERE rnk <= 10) AS top10_fee_eur  FROM ranked  GROUP BY 1)SELECT  qt.quarter_label AS quarter,  qt.active_merchants,  ROUND(qt.total_fee_eur, 2) AS total_fee_revenue_eur,  ROUND(ts.top5_fee_eur, 2) AS top5_fee_revenue_eur,  ROUND(100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top5_share_pct,  ROUND(ts.top10_fee_eur, 2) AS top10_fee_revenue_eur,  ROUND(100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top10_share_pct  qt.active_merchants AS active_merchants,  qt.total_fee_eur AS total_fee_revenue_eur,  ts.top5_fee_eur AS top5_fee_revenue_eur,  100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0) AS top5_share_pct,  ts.top10_fee_eur AS top10_fee_revenue_eur,  100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0) AS top10_share_pctFROM quarter_totals qtJOIN top_sums ts ON ts.quarter_label = qt.quarter_labelORDER BY qt.year_num, qt.quarter_numORDER BY qt.year_num NULLS LAST, qt.quarter_num NULLS LAST

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

    WITH payout_fees_eur AS (
  SELECT
    p.tenant_id AS tenant_id,
    p.paid_at AS paid_at,
    p.fee_amount AS fee_amount,
    p.currency_code AS currency_code,
    er.rate AS rate,
    CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) AS fee_amount_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 IS NOT NULL
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
quarterly AS (
  SELECT
    cd.year_num AS year_num,
    cd.quarter_num AS quarter_num,
    cd.year_num || '-Q' || cd.quarter_num AS quarter_label,
    pf.tenant_id AS tenant_id,
    SUM(pf.fee_amount_eur) AS fee_eur
  FROM payout_fees_eur pf
  JOIN calendar_days cd ON cd.calendar_date = CAST(pf.paid_at AS date)
  GROUP BY 1, 2, 3, 4
),
ranked AS (
  SELECT
    quarterly.*,
    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rnk
  FROM quarterly
),
quarter_totals AS (
  SELECT
    quarter_label,
    year_num,
    quarter_num,
    COUNT(DISTINCT tenant_id) AS active_merchants,
    SUM(fee_eur) AS total_fee_eur
  FROM quarterly
  GROUP BY 1, 2, 3
),
top_sums AS (
  SELECT
    quarter_label,
    SUM(fee_eur) FILTER (WHERE rnk <= 5) AS top5_fee_eur,
    SUM(fee_eur) FILTER (WHERE rnk <= 10) AS top10_fee_eur
  FROM ranked
  GROUP BY 1
)
SELECT
  qt.quarter_label AS quarter,
  qt.active_merchants AS active_merchants,
  qt.total_fee_eur AS total_fee_revenue_eur,
  ts.top5_fee_eur AS top5_fee_revenue_eur,
  100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0) AS top5_share_pct,
  ts.top10_fee_eur AS top10_fee_revenue_eur,
  100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0) AS top10_share_pct
FROM quarter_totals qt
JOIN top_sums ts ON ts.quarter_label = qt.quarter_label
ORDER BY qt.year_num NULLS LAST, qt.quarter_num NULLS LAST
    1 ms10 ms100 ms1 sHyper8.0 msDuckDB26.9 msGizmoSQL31.7 msclickhouse_brew83.8 msFirebolterrorDataFusion104 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 8, Column 23: 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": "17b798af-132d-417b-b261-6df01c75c1b7", "query_labe
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.fee_amount Decimal(14, 4) Decimal64(size = 0), __table5.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

  7. 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: Revenue concentration trend by quarter
Question: Has Tidewick's revenue become more or less concentrated among its largest merchants over the last two years?
Grain: one row per calendar quarter
Columns:
- quarter (e.g. 2024-Q1)
- active merchants with a payout that quarter
- total fee revenue (EUR)
- fee revenue from top 5 merchants that quarter (EUR)
- top-5 share of fee revenue (%)
- fee revenue from top 10 merchants that quarter (EUR)
- top-10 share of fee revenue (%)
Details: Source: payouts joined to tenants, same inclusion rule and EUR conversion method as the merchant-concentration report (convert via exchange_rates, base_code='EUR', quote_code=payout currency_code, as_of=payout paid_at date). Window: quarters from 2024-Q1 through 2025-Q4 (8 quarters), using calendar_days or payout paid_at to assign the quarter. For each quarter, rank merchants (tenant_id) by that quarter's fee revenue EUR and sum the top 5 and top 10 merchants' fee revenue to get top5/top10 shares of that quarter's total. 'Active merchants' = distinct tenant_id with qualifying payouts in that quarter. Order by quarter ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH payout_fees_eur AS (
  SELECT
    p.tenant_id,
    p.paid_at,
    p.fee_amount,
    p.currency_code,
    er.rate,
    p.fee_amount / er.rate AS fee_amount_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 IS NOT NULL
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
quarterly AS (
  SELECT
    cd.year_num,
    cd.quarter_num,
    cd.year_num || '-Q' || cd.quarter_num AS quarter_label,
    pf.tenant_id,
    SUM(pf.fee_amount_eur) AS fee_eur
  FROM payout_fees_eur pf
  JOIN calendar_days cd ON cd.calendar_date = CAST(pf.paid_at AS date)
  GROUP BY 1, 2, 3, 4
),
ranked AS (
  SELECT
    quarterly.*,
    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC, tenant_id) AS rnk
  FROM quarterly
),
quarter_totals AS (
  SELECT
    quarter_label,
    year_num,
    quarter_num,
    COUNT(DISTINCT tenant_id) AS active_merchants,
    SUM(fee_eur) AS total_fee_eur
  FROM quarterly
  GROUP BY 1, 2, 3
),
top_sums AS (
  SELECT
    quarter_label,
    SUM(fee_eur) FILTER (WHERE rnk <= 5) AS top5_fee_eur,
    SUM(fee_eur) FILTER (WHERE rnk <= 10) AS top10_fee_eur
  FROM ranked
  GROUP BY 1
)
SELECT
  qt.quarter_label AS quarter,
  qt.active_merchants,
  ROUND(qt.total_fee_eur, 2) AS total_fee_revenue_eur,
  ROUND(ts.top5_fee_eur, 2) AS top5_fee_revenue_eur,
  ROUND(100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top5_share_pct,
  ROUND(ts.top10_fee_eur, 2) AS top10_fee_revenue_eur,
  ROUND(100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top10_share_pct
FROM quarter_totals qt
JOIN top_sums ts ON ts.quarter_label = qt.quarter_label
ORDER BY qt.year_num, qt.quarter_num
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: quarter, active_merchants, total_fee_revenue_eur, top5_fee_revenue_eur, top5_share_pct, top10_fee_revenue_eur, top10_share_pct
    • GraceQuery compiles cleanly and returns exactly the 7 requested columns at one-row-per-quarter grain. Submitting.
    • Grace calls submit
      WITH payout_fees_eur AS (
  SELECT
    p.tenant_id,
    p.paid_at,
    p.fee_amount,
    p.currency_code,
    er.rate,
    p.fee_amount / er.rate AS fee_amount_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 IS NOT NULL
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
quarterly AS (
  SELECT
    cd.year_num,
    cd.quarter_num,
    cd.year_num || '-Q' || cd.quarter_num AS quarter_label,
    pf.tenant_id,
    SUM(pf.fee_amount_eur) AS fee_eur
  FROM payout_fees_eur pf
  JOIN calendar_days cd ON cd.calendar_date = CAST(pf.paid_at AS date)
  GROUP BY 1, 2, 3, 4
),
ranked AS (
  SELECT
    quarterly.*,
    ROW_NUMBER() OVER (PARTITION BY quarter_label ORDER BY fee_eur DESC, tenant_id) AS rnk
  FROM quarterly
),
quarter_totals AS (
  SELECT
    quarter_label,
    year_num,
    quarter_num,
    COUNT(DISTINCT tenant_id) AS active_merchants,
    SUM(fee_eur) AS total_fee_eur
  FROM quarterly
  GROUP BY 1, 2, 3
),
top_sums AS (
  SELECT
    quarter_label,
    SUM(fee_eur) FILTER (WHERE rnk <= 5) AS top5_fee_eur,
    SUM(fee_eur) FILTER (WHERE rnk <= 10) AS top10_fee_eur
  FROM ranked
  GROUP BY 1
)
SELECT
  qt.quarter_label AS quarter,
  qt.active_merchants,
  ROUND(qt.total_fee_eur, 2) AS total_fee_revenue_eur,
  ROUND(ts.top5_fee_eur, 2) AS top5_fee_revenue_eur,
  ROUND(100.0 * ts.top5_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top5_share_pct,
  ROUND(ts.top10_fee_eur, 2) AS top10_fee_revenue_eur,
  ROUND(100.0 * ts.top10_fee_eur / NULLIF(qt.total_fee_eur, 0), 1) AS top10_share_pct
FROM quarter_totals qt
JOIN top_sums ts ON ts.quarter_label = qt.quarter_label
ORDER BY qt.year_num, qt.quarter_num
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. The report computes, for each of the 8 quarters (2024-Q1 through 2025-Q4): distinct active merchants with settled payouts, total EUR fee revenue, top-5 and top-10 merchant fee revenue and their shares of the quarter total — ranked by each tenant's quarterly fee revenue after EUR conversion via `exchange_rates` (EUR base, payout currency quote, rate as of `paid_at` date).

    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 (267 KB) · Hannah Brooks's transcript (872 KB) · Maren Okafor's transcript (278 KB) · the night's plan (4 KB) · the night's reports (12 KB) · the week's memo (3 KB)