DB Olympics

Queries · q015 · Chief Financial Officer · week 1, night 1

Merchant Concentration Trend: 2024 vs 2025

“Is Tidewick's volume and revenue concentration among its largest merchants growing or shrinking year over year?”

Manager · asked the questionTobias LindqvistChief Financial Officer. New this month, previously finance director at a retailer. Preparing the 2025 annual accounts and the 2026 budget.
Analyst · planned the reportKofi MensahHypothesis-driven: states what he expects and designs reports that would prove him wrong.
Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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

  1. Tobias Lindqvist writes to Kofi Mensah

    The manager, at the start of week 1

    Subject: Finding my bearings – first set of questions for the week

    Hi Kofi,

    Good to be working with you. I'm still building my mental map of the business, so I'd rather ask a few broad questions well than a lot of narrow ones badly. Given the slot budget, here's what I'd prioritize this week, roughly in order:

    1. How did we actually do in 2025, and where is the money coming from? I want to see our own revenue split between the subscription/plan fees and the payment-processing fees, over the year, and ideally how that's trending month to month. This is the backbone of the annual accounts narrative and will shape how I talk about 2025 to the board.
    2. How concentrated is our business in a few large merchants, and how healthy are they? I keep hearing "a handful of large merchants drive most of the volume" — I'd like to understand who they are, how much of our gross volume and revenue they represent, and whether that concentration is growing or shrinking. This matters for risk assessment going into the budget.
    3. What do refunds and returns look like across the merchant base? I want a sense of refund rates — overall and whether certain verticals or merchants are outliers — since this affects both our net revenue and how much cash we actually end up paying out.
    4. How exposed are we to currency movements? Merchants bill in their own currencies, and shoppers come from ~40 countries. I'd like a picture of how our volume and revenue break down by currency, so I can gauge our FX exposure for the 2026 budget and think about whether we need any hedging conversation.
    5. Are our B2B merchants paying us and their own customers on time? I know a few of our merchants are wholesalers running on B2B terms. I'd like to understand how their receivables look — how much is outstanding and how overdue it tends to get — since this could be a real cash-flow risk if it's worse than I'd expect.

    For this week, let's focus on #1 and #2 — those are the two I most need before I can speak credibly about 2025 and the 2026 budget. If there's room, #3 would be next; #4 and #5 can wait for a follow-up week if slots are tight.

    Let me know if any of these need me to narrow scope further before you brief the data engineers.

    Thanks, Tobias

  2. Kofi Mensah plans the night

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

    Tobias ranked #1 (2025 revenue mix) and #2 (merchant concentration) as this week's priorities, so I'm spending all 3 slots there tonight.

    Scoping note on #1: I looked for a table that records merchants paying Tidewick its own monthly platform-plan fee. The plans/subscriptions/invoices tables are tenant-scoped and model each merchant's own subscription business toward their end customers (invoice.order_id/subscription_id point at the merchant's own orders/users) — they are not Tidewick billing the merchant. The only piece of Tidewick's own revenue that is actually observable in this warehouse is the processing fee Tidewick keeps on settled payments, captured explicitly in payouts.fee_amount per the handbook. So tonight I'm only requesting the processing-fee side of #1, trended monthly, and I'll flag to Tobias in the memo that platform-plan fee revenue isn't in this warehouse (likely lives in Tidewick's own internal billing/finance system) before promising a full split.

    For #2, I expect a small number of merchants to dominate — my hypothesis is the top ~10% of merchants account for well over half of gross volume. I'm requesting one snapshot (who the big merchants are and their share in 2025) and one two-point trend (2024 vs 2025) to directly test whether that concentration is growing or shrinking, which is the risk question Tobias actually asked.

    All three reports use payouts (gross/fee/net already split there) converted to EUR via exchange_rates, joined on the payout's currency and settlement date.

    Merchant Concentration Trend: 2024 vs 2025
    Question
    Is Tidewick's volume and revenue concentration among its largest merchants growing or shrinking year over year?
    One row per
    one calendar year (2024, 2025)
    Columns
    • year
    • active merchant count (tenants with >=1 completed payout that year)
    • total gross volume (EUR)
    • top 5 merchants' gross volume (EUR)
    • top 5 share of total gross volume (%)
    • top 10 merchants' gross volume (EUR)
    • top 10 share of total gross volume (%)
    • top 10 share of total fee revenue (%)

  3. Ines 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_eur AS (
  SELECT p.tenant_id,
         EXTRACT(YEAR FROM p.paid_at) AS yr,
         CASE WHEN p.currency_code = 'EUR' THEN p.gross_amount
              ELSE p.gross_amount / er.rate END AS gross_eur,
         CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount
              ELSE p.fee_amount / er.rate END AS fee_eur
  FROM payouts p
  LEFT 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 IN ('completed', 'paid')
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
tenant_year AS (
  SELECT tenant_id, yr, SUM(gross_eur) AS gross_eur, SUM(fee_eur) AS fee_eur
  FROM payout_eur
  GROUP BY tenant_id, yr
),
ranked AS (
  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC) AS rnk
  FROM tenant_year ty
),
year_totals AS (
  SELECT yr, COUNT(*) AS active_merchants, SUM(gross_eur) AS total_gross_eur, SUM(fee_eur) AS total_fee_eur
  FROM tenant_year
  GROUP BY yr
),
top_agg AS (
  SELECT yr,
    SUM(CASE WHEN rnk <= 5 THEN gross_eur ELSE 0 END) AS top5_gross,
    SUM(CASE WHEN rnk <= 5 THEN fee_eur ELSE 0 END) AS top5_fee,
    SUM(CASE WHEN rnk <= 10 THEN gross_eur ELSE 0 END) AS top10_gross,
    SUM(CASE WHEN rnk <= 10 THEN fee_eur ELSE 0 END) AS top10_fee
  FROM ranked
  GROUP BY yr
)
SELECT
  yt.yr AS year,
  yt.active_merchants AS active_merchant_count,
  ROUND(yt.total_gross_eur, 2) AS total_gross_volume_eur,
  ROUND(ta.top5_gross, 2) AS top5_gross_volume_eur,
  ROUND(100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top5_share_pct,
  ROUND(ta.top10_gross, 2) AS top10_gross_volume_eur,
  ROUND(100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top10_share_pct,
  ROUND(100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0), 1) AS top10_fee_share_pct
FROM year_totals yt
JOIN top_agg ta ON ta.yr = yt.yr
ORDER BY yt.yr ASC

    “Query compiles and returns the requested columns. 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 2024 and 2025 separately: active merchant count, total gross volume (EUR), top-5 and top-10 merchants' gross volume and their share of total gross volume, and top-10's share of total fee revenue — ranked by each tenant's annual gross volume in EUR, converted via daily exchange_rates.”

  4. The report comes back overnight

    As Kofi Mensah saw it, from the small dataset the agents worked on

    year | active_merchant_count | total_gross_volume_eur | top5_gross_volume_eur | top5_share_pct | top10_gross_volume_eur | top10_share_pct | top10_fee_share_pct
-----+-----------------------+------------------------+-----------------------+----------------+------------------------+-----------------+--------------------
2024 |                    92 |             3090208.98 |            1622754.42 |           52.5 |             2028894.53 |            65.7 |                67.3
2025 |                   113 |             3972987.99 |            1885475.85 |           47.5 |             2563609.72 |            64.5 |                65.1
(2 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q015

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

    • determinismtie-breaker: tenant_year is keyed by (tenant_id, yr)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
    • made explicita selected t.col gets AS col, its standard name made explicit
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
    The benchmark text, against the submitted SQL
    WITH payout_eur AS (  SELECT p.tenant_id,  SELECT p.tenant_id AS tenant_id,         EXTRACT(YEAR FROM p.paid_at) AS yr,         CASE WHEN p.currency_code = 'EUR' THEN p.gross_amount              ELSE p.gross_amount / er.rate END AS gross_eur,              ELSE CAST(p.gross_amount / er.rate AS NUMERIC(18,6)) END AS gross_eur,         CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount              ELSE p.fee_amount / er.rate END AS fee_eur              ELSE CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) END AS fee_eur  FROM payouts p  LEFT 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 IN ('completed', 'paid')    AND p.paid_at >= DATE '2024-01-01'    AND p.paid_at < DATE '2026-01-01'),tenant_year AS (  SELECT tenant_id, yr, SUM(gross_eur) AS gross_eur, SUM(fee_eur) AS fee_eur  FROM payout_eur  GROUP BY tenant_id, yr),ranked AS (  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC) AS rnk  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rnk  FROM tenant_year ty),year_totals AS (  SELECT yr, COUNT(*) AS active_merchants, SUM(gross_eur) AS total_gross_eur, SUM(fee_eur) AS total_fee_eur  FROM tenant_year  GROUP BY yr),top_agg AS (  SELECT yr,    SUM(CASE WHEN rnk <= 5 THEN gross_eur ELSE 0 END) AS top5_gross,    SUM(CASE WHEN rnk <= 5 THEN fee_eur ELSE 0 END) AS top5_fee,    SUM(CASE WHEN rnk <= 10 THEN gross_eur ELSE 0 END) AS top10_gross,    SUM(CASE WHEN rnk <= 10 THEN fee_eur ELSE 0 END) AS top10_fee  FROM ranked  GROUP BY yr)SELECT  yt.yr AS year,  yt.active_merchants AS active_merchant_count,  ROUND(yt.total_gross_eur, 2) AS total_gross_volume_eur,  ROUND(ta.top5_gross, 2) AS top5_gross_volume_eur,  ROUND(100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top5_share_pct,  ROUND(ta.top10_gross, 2) AS top10_gross_volume_eur,  ROUND(100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top10_share_pct,  ROUND(100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0), 1) AS top10_fee_share_pct  yt.total_gross_eur AS total_gross_volume_eur,  ta.top5_gross AS top5_gross_volume_eur,  100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0) AS top5_share_pct,  ta.top10_gross AS top10_gross_volume_eur,  100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0) AS top10_share_pct,  100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0) AS top10_fee_share_pctFROM year_totals ytJOIN top_agg ta ON ta.yr = yt.yrORDER BY yt.yr ASCORDER BY yt.yr ASC NULLS LAST

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

    WITH payout_eur AS (
  SELECT p.tenant_id AS tenant_id,
         EXTRACT(YEAR FROM p.paid_at) AS yr,
         CASE WHEN p.currency_code = 'EUR' THEN p.gross_amount
              ELSE CAST(p.gross_amount / er.rate AS NUMERIC(18,6)) END AS gross_eur,
         CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount
              ELSE CAST(p.fee_amount / er.rate AS NUMERIC(18,6)) END AS fee_eur
  FROM payouts p
  LEFT 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 IN ('completed', 'paid')
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
tenant_year AS (
  SELECT tenant_id, yr, SUM(gross_eur) AS gross_eur, SUM(fee_eur) AS fee_eur
  FROM payout_eur
  GROUP BY tenant_id, yr
),
ranked AS (
  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC NULLS FIRST, tenant_id NULLS LAST) AS rnk
  FROM tenant_year ty
),
year_totals AS (
  SELECT yr, COUNT(*) AS active_merchants, SUM(gross_eur) AS total_gross_eur, SUM(fee_eur) AS total_fee_eur
  FROM tenant_year
  GROUP BY yr
),
top_agg AS (
  SELECT yr,
    SUM(CASE WHEN rnk <= 5 THEN gross_eur ELSE 0 END) AS top5_gross,
    SUM(CASE WHEN rnk <= 5 THEN fee_eur ELSE 0 END) AS top5_fee,
    SUM(CASE WHEN rnk <= 10 THEN gross_eur ELSE 0 END) AS top10_gross,
    SUM(CASE WHEN rnk <= 10 THEN fee_eur ELSE 0 END) AS top10_fee
  FROM ranked
  GROUP BY yr
)
SELECT
  yt.yr AS year,
  yt.active_merchants AS active_merchant_count,
  yt.total_gross_eur AS total_gross_volume_eur,
  ta.top5_gross AS top5_gross_volume_eur,
  100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0) AS top5_share_pct,
  ta.top10_gross AS top10_gross_volume_eur,
  100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0) AS top10_share_pct,
  100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0) AS top10_fee_share_pct
FROM year_totals yt
JOIN top_agg ta ON ta.yr = yt.yr
ORDER BY yt.yr ASC NULLS LAST
    1 ms10 ms100 msHyper9.5 msDuckDB11.9 msGizmoSQL17.7 msclickhouse_brew64.5 msFirebolterrorDataFusion98.4 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 5, Column 40: 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": "e2e6f242-b9d7-4b7c-a413-10591329568e", "query_labe
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.gross_amount Decimal(14, 4) Decimal64(size = 0), __table5.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

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

    Ines's whole session, 7 turns
    • The request, as Ines received it
      Report request from Kofi Mensah (analyst for the Chief Financial Officer):

Title: Merchant Concentration Trend: 2024 vs 2025
Question: Is Tidewick's volume and revenue concentration among its largest merchants growing or shrinking year over year?
Grain: one calendar year (2024, 2025)
Columns:
- year
- active merchant count (tenants with >=1 completed payout that year)
- total gross volume (EUR)
- top 5 merchants' gross volume (EUR)
- top 5 share of total gross volume (%)
- top 10 merchants' gross volume (EUR)
- top 10 share of total gross volume (%)
- top 10 share of total fee revenue (%)
Details: Source: payouts joined to tenants. For each of 2024 and 2025 separately, aggregate gross_amount and fee_amount (converted to EUR via exchange_rates as in the other two reports) per tenant_id for payouts with completed/paid status and paid_at in that year. Rank tenants within each year by gross volume EUR descending to identify that year's top 5 and top 10, then sum their gross volume and fee revenue and express as a percentage of that year's total across all active tenants. Round shares to 1 decimal place. Order by year ascending (2 rows total).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH payout_eur AS (
  SELECT p.tenant_id,
         EXTRACT(YEAR FROM p.paid_at) AS yr,
         CASE WHEN p.currency_code = 'EUR' THEN p.gross_amount
              ELSE p.gross_amount / er.rate END AS gross_eur,
         CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount
              ELSE p.fee_amount / er.rate END AS fee_eur
  FROM payouts p
  LEFT 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 IN ('completed', 'paid')
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
tenant_year AS (
  SELECT tenant_id, yr, SUM(gross_eur) AS gross_eur, SUM(fee_eur) AS fee_eur
  FROM payout_eur
  GROUP BY tenant_id, yr
),
ranked AS (
  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC) AS rnk
  FROM tenant_year ty
),
year_totals AS (
  SELECT yr, COUNT(*) AS active_merchants, SUM(gross_eur) AS total_gross_eur, SUM(fee_eur) AS total_fee_eur
  FROM tenant_year
  GROUP BY yr
),
top_agg AS (
  SELECT yr,
    SUM(CASE WHEN rnk <= 5 THEN gross_eur ELSE 0 END) AS top5_gross,
    SUM(CASE WHEN rnk <= 5 THEN fee_eur ELSE 0 END) AS top5_fee,
    SUM(CASE WHEN rnk <= 10 THEN gross_eur ELSE 0 END) AS top10_gross,
    SUM(CASE WHEN rnk <= 10 THEN fee_eur ELSE 0 END) AS top10_fee
  FROM ranked
  GROUP BY yr
)
SELECT
  yt.yr AS year,
  yt.active_merchants AS active_merchant_count,
  ROUND(yt.total_gross_eur, 2) AS total_gross_volume_eur,
  ROUND(ta.top5_gross, 2) AS top5_gross_volume_eur,
  ROUND(100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top5_share_pct,
  ROUND(ta.top10_gross, 2) AS top10_gross_volume_eur,
  ROUND(100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top10_share_pct,
  ROUND(100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0), 1) AS top10_fee_share_pct
FROM year_totals yt
JOIN top_agg ta ON ta.yr = yt.yr
ORDER BY yt.yr ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: year, active_merchant_count, total_gross_volume_eur, top5_gross_volume_eur, top5_share_pct, top10_gross_volume_eur, top10_share_pct, top10_fee_share_pct
    • InesQuery compiles and returns the requested columns. Submitting.
    • Ines calls submit
      WITH payout_eur AS (
  SELECT p.tenant_id,
         EXTRACT(YEAR FROM p.paid_at) AS yr,
         CASE WHEN p.currency_code = 'EUR' THEN p.gross_amount
              ELSE p.gross_amount / er.rate END AS gross_eur,
         CASE WHEN p.currency_code = 'EUR' THEN p.fee_amount
              ELSE p.fee_amount / er.rate END AS fee_eur
  FROM payouts p
  LEFT 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 IN ('completed', 'paid')
    AND p.paid_at >= DATE '2024-01-01'
    AND p.paid_at < DATE '2026-01-01'
),
tenant_year AS (
  SELECT tenant_id, yr, SUM(gross_eur) AS gross_eur, SUM(fee_eur) AS fee_eur
  FROM payout_eur
  GROUP BY tenant_id, yr
),
ranked AS (
  SELECT ty.*, ROW_NUMBER() OVER (PARTITION BY yr ORDER BY gross_eur DESC) AS rnk
  FROM tenant_year ty
),
year_totals AS (
  SELECT yr, COUNT(*) AS active_merchants, SUM(gross_eur) AS total_gross_eur, SUM(fee_eur) AS total_fee_eur
  FROM tenant_year
  GROUP BY yr
),
top_agg AS (
  SELECT yr,
    SUM(CASE WHEN rnk <= 5 THEN gross_eur ELSE 0 END) AS top5_gross,
    SUM(CASE WHEN rnk <= 5 THEN fee_eur ELSE 0 END) AS top5_fee,
    SUM(CASE WHEN rnk <= 10 THEN gross_eur ELSE 0 END) AS top10_gross,
    SUM(CASE WHEN rnk <= 10 THEN fee_eur ELSE 0 END) AS top10_fee
  FROM ranked
  GROUP BY yr
)
SELECT
  yt.yr AS year,
  yt.active_merchants AS active_merchant_count,
  ROUND(yt.total_gross_eur, 2) AS total_gross_volume_eur,
  ROUND(ta.top5_gross, 2) AS top5_gross_volume_eur,
  ROUND(100.0 * ta.top5_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top5_share_pct,
  ROUND(ta.top10_gross, 2) AS top10_gross_volume_eur,
  ROUND(100.0 * ta.top10_gross / NULLIF(yt.total_gross_eur, 0), 1) AS top10_share_pct,
  ROUND(100.0 * ta.top10_fee / NULLIF(yt.total_fee_eur, 0), 1) AS top10_fee_share_pct
FROM year_totals yt
JOIN top_agg ta ON ta.yr = yt.yr
ORDER BY yt.yr ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report computes, for 2024 and 2025 separately: active merchant count, total gross volume (EUR), top-5 and top-10 merchants' gross volume and their share of total gross volume, and top-10's share of total fee revenue — ranked by each tenant's annual gross volume in EUR, converted via daily exchange_rates.

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

    Ines's transcript (268 KB) · Kofi Mensah's transcript (906 KB) · Tobias Lindqvist's transcript (269 KB) · the night's plan (5 KB) · the night's reports (9 KB) · the week's memo (3 KB)