Queries · q005 · Chief Executive Officer · week 1, night 2

Merchant and order growth by quarter

“Are we gaining merchants and order volume, and how has that trended 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

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
Hannah Brooks plans the night
The analyst · week 1, night 2, request 2 of 3
Before building anything else on top of payouts data, I need to resolve a real problem: Report 1 (merchant-level) implies total trailing-12-month fee revenue of ~€53.2k, but Report 3 (vertical-level) sums to ~€93.6k for the same window — a 1.76x gap. The two engineers used different status filters (Omar: status NOT IN bad list + paid_at not null; Bea: status = 'paid' only), and a narrower filter producing a LARGER total than a broader one is backwards, so something else is off too (possibly which date field anchors the window, or paid_at integrity by status). I'm spending one slot tonight on a reconciliation report to pin down the correct definition before I trust any revenue numbers further. The other two slots move to Maren's priority #2 (growth), using orders (which has a documented status enum, so no ambiguity) rather than payouts, so this work isn't blocked by the reconciliation.
Merchant and order growth by quarter
Question
Are we gaining merchants and order volume, and how has that trended over the last two years?
One row per
calendar quarter
Columns
- quarter
- new merchants (tenants created that quarter)
- cumulative active merchants (tenants created on or before quarter end, not deleted)
- order count
- GMV EUR
- average order value EUR

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 quarters AS (
  SELECT
    quarter_start,
    MAX(calendar_date) AS quarter_end,
    year_num,
    quarter_num
  FROM calendar_days
  WHERE quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-12-31'
  GROUP BY quarter_start, year_num, quarter_num
),
new_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS new_merchant_count
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at >= q.quarter_start
   AND t.created_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
),
cumulative_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS cumulative_active_merchants
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at < q.quarter_end + INTERVAL '1 day'
   AND t.deleted_at IS NULL
  GROUP BY q.quarter_start
),
order_amounts_eur AS (
  SELECT
    o.id,
    o.placed_at,
    o.grand_total / er.rate AS amount_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.status IN ('paid','partially_fulfilled','fulfilled','refunded')
    AND o.placed_at >= DATE '2024-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
orders_in_quarter AS (
  SELECT
    q.quarter_start,
    COUNT(oa.id) AS order_count,
    SUM(oa.amount_eur) AS gmv_eur
  FROM quarters q
  LEFT JOIN order_amounts_eur oa
    ON oa.placed_at >= q.quarter_start
   AND oa.placed_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
)
SELECT
  q.year_num || '-Q' || q.quarter_num AS quarter,
  nm.new_merchant_count AS new_merchants,
  cm.cumulative_active_merchants,
  oiq.order_count,
  ROUND(oiq.gmv_eur, 2) AS gmv_eur,
  ROUND(oiq.gmv_eur / NULLIF(oiq.order_count, 0), 2) AS avg_order_value_eur
FROM quarters q
JOIN new_merchants nm ON nm.quarter_start = q.quarter_start
JOIN cumulative_merchants cm ON cm.quarter_start = q.quarter_start
JOIN orders_in_quarter oiq ON oiq.quarter_start = q.quarter_start
ORDER BY q.quarter_start ASC

“The query compiles and returns the expected columns. Submitting now.”

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

“Submitted. The report covers the 8 requested quarters (2024-Q1 → 2025-Q4) with new merchants, cumulative active merchants, order count, GMV EUR, and average order value EUR, built from tenants and orders per the spec, with EUR conversion via same-day exchange_rates.”

The report comes back overnight

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

quarter | new_merchants | cumulative_active_merchants | order_count |   gmv_eur | avg_order_value_eur
--------+---------------+-----------------------------+-------------+-----------+--------------------
2024-Q1 |             4 |                          76 |        1717 | 442030.82 |              257.44
2024-Q2 |             3 |                          78 |        1909 | 477592.82 |              250.18
2024-Q3 |             6 |                          83 |        1976 | 532608.59 |              269.54
2024-Q4 |             8 |                          88 |        3461 | 779322.62 |              225.17
2025-Q1 |             2 |                          90 |        2417 |  721097.1 |              298.34
2025-Q2 |            10 |                         100 |        2424 | 549427.23 |              226.66
2025-Q3 |             9 |                         109 |        2557 | 542407.57 |              212.13
2025-Q4 |             2 |                         111 |        4259 |  894406.3 |               210.0
(8 rows)

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:
- 2. Growth is real and broad-based, driven by existing merchants, not new ones
  
  Active merchant count grew from 76 to 111 over 2 years; order count roughly doubled (1,717 → 4,259/quarter) (Merchant and order growth by quarter). But in every quarter, >99% of GMV comes from merchants that joined in a prior quarter — new signups contribute almost nothing in their first quarter (GMV growth by cohort). Confidence: high. Watch item: average order value has declined steadily, €257 → €210, as order count has outpaced GMV growth.
Later queries that build on this report:

It becomes benchmark query q005

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

determinismtie-breaker: completes the quarters group key
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 (7×)
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 quarters AS (  SELECT    quarter_start,    MAX(calendar_date) AS quarter_end,    year_num,    quarter_num  FROM calendar_days  WHERE quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-12-31'  GROUP BY quarter_start, year_num, quarter_num),new_merchants AS (  SELECT    q.quarter_start,    q.quarter_start AS quarter_start,    COUNT(t.id) AS new_merchant_count  FROM quarters q  LEFT JOIN tenants t    ON t.created_at >= q.quarter_start   AND t.created_at < q.quarter_end + INTERVAL '1 day'  GROUP BY q.quarter_start),cumulative_merchants AS (  SELECT    q.quarter_start,    q.quarter_start AS quarter_start,    COUNT(t.id) AS cumulative_active_merchants  FROM quarters q  LEFT JOIN tenants t    ON t.created_at < q.quarter_end + INTERVAL '1 day'   AND t.deleted_at IS NULL  GROUP BY q.quarter_start),order_amounts_eur AS (  SELECT    o.id,    o.placed_at,    o.grand_total / er.rate AS amount_eur    o.id AS id,    o.placed_at AS placed_at,    CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS amount_eur  FROM orders o  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS DATE)  WHERE o.status IN ('paid','partially_fulfilled','fulfilled','refunded')    AND o.placed_at >= DATE '2024-01-01'    AND o.placed_at < DATE '2026-01-01'),orders_in_quarter AS (  SELECT    q.quarter_start,    q.quarter_start AS quarter_start,    COUNT(oa.id) AS order_count,    SUM(oa.amount_eur) AS gmv_eur  FROM quarters q  LEFT JOIN order_amounts_eur oa    ON oa.placed_at >= q.quarter_start   AND oa.placed_at < q.quarter_end + INTERVAL '1 day'  GROUP BY q.quarter_start)SELECT  q.year_num || '-Q' || q.quarter_num AS quarter,  nm.new_merchant_count AS new_merchants,  cm.cumulative_active_merchants,  oiq.order_count,  ROUND(oiq.gmv_eur, 2) AS gmv_eur,  ROUND(oiq.gmv_eur / NULLIF(oiq.order_count, 0), 2) AS avg_order_value_eur  cm.cumulative_active_merchants AS cumulative_active_merchants,  oiq.order_count AS order_count,  oiq.gmv_eur AS gmv_eur,  oiq.gmv_eur / NULLIF(oiq.order_count, 0) AS avg_order_value_eurFROM quarters qJOIN new_merchants nm ON nm.quarter_start = q.quarter_startJOIN cumulative_merchants cm ON cm.quarter_start = q.quarter_startJOIN orders_in_quarter oiq ON oiq.quarter_start = q.quarter_startORDER BY q.quarter_start ASCORDER BY q.quarter_start ASC NULLS LAST, q.year_num NULLS LAST, q.quarter_num NULLS LAST

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

WITH quarters AS (
  SELECT
    quarter_start,
    MAX(calendar_date) AS quarter_end,
    year_num,
    quarter_num
  FROM calendar_days
  WHERE quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-12-31'
  GROUP BY quarter_start, year_num, quarter_num
),
new_merchants AS (
  SELECT
    q.quarter_start AS quarter_start,
    COUNT(t.id) AS new_merchant_count
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at >= q.quarter_start
   AND t.created_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
),
cumulative_merchants AS (
  SELECT
    q.quarter_start AS quarter_start,
    COUNT(t.id) AS cumulative_active_merchants
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at < q.quarter_end + INTERVAL '1 day'
   AND t.deleted_at IS NULL
  GROUP BY q.quarter_start
),
order_amounts_eur AS (
  SELECT
    o.id AS id,
    o.placed_at AS placed_at,
    CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS amount_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.status IN ('paid','partially_fulfilled','fulfilled','refunded')
    AND o.placed_at >= DATE '2024-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
orders_in_quarter AS (
  SELECT
    q.quarter_start AS quarter_start,
    COUNT(oa.id) AS order_count,
    SUM(oa.amount_eur) AS gmv_eur
  FROM quarters q
  LEFT JOIN order_amounts_eur oa
    ON oa.placed_at >= q.quarter_start
   AND oa.placed_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
)
SELECT
  q.year_num || '-Q' || q.quarter_num AS quarter,
  nm.new_merchant_count AS new_merchants,
  cm.cumulative_active_merchants AS cumulative_active_merchants,
  oiq.order_count AS order_count,
  oiq.gmv_eur AS gmv_eur,
  oiq.gmv_eur / NULLIF(oiq.order_count, 0) AS avg_order_value_eur
FROM quarters q
JOIN new_merchants nm ON nm.quarter_start = q.quarter_start
JOIN cumulative_merchants cm ON cm.quarter_start = q.quarter_start
JOIN orders_in_quarter oiq ON oiq.quarter_start = q.quarter_start
ORDER BY q.quarter_start ASC NULLS LAST, q.year_num NULLS LAST, q.quarter_num NULLS LAST

Firebolt{ "errors": [ { "description": "Line 35, Column 24: 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": "39be0a17-a0c7-48bd-85ea-0337af231d06", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table15.grand_total Decimal(14, 4) Decimal64(size = 0), __table16.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

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: Merchant and order growth by quarter
Question: Are we gaining merchants and order volume, and how has that trended over the last two years?
Grain: one row per calendar quarter
Columns:
- quarter
- new merchants (tenants created that quarter)
- cumulative active merchants (tenants created on or before quarter end, not deleted)
- order count
- GMV EUR
- average order value EUR
Details: Source: tenants (for merchant counts, using created_at and deleted_at) and orders (for order count and GMV, using placed_at). Window: quarters 2024-Q1 through 2025-Q4 (8 quarters). 'New merchants' = count of tenants whose created_at falls within that quarter. 'Cumulative active merchants' = count of tenants with created_at <= the end of that quarter and deleted_at is null (regardless of current status field, since status can change independent of tenure). 'Order count' and 'GMV' should only include orders with status in ('paid','partially_fulfilled','fulfilled','refunded') and placed_at within the quarter (exclude 'pending' and 'cancelled' orders, since those never represent realized volume). Convert orders.grand_total to EUR using exchange_rates (amount_eur = amount / rate, base_code='EUR', quote_code=orders.currency_code, as_of = orders.placed_at date). Average order value = GMV EUR / order count. 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 quarters AS (
  SELECT
    quarter_start,
    MAX(calendar_date) AS quarter_end,
    year_num,
    quarter_num
  FROM calendar_days
  WHERE quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-12-31'
  GROUP BY quarter_start, year_num, quarter_num
),
new_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS new_merchant_count
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at >= q.quarter_start
   AND t.created_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
),
cumulative_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS cumulative_active_merchants
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at < q.quarter_end + INTERVAL '1 day'
   AND t.deleted_at IS NULL
  GROUP BY q.quarter_start
),
order_amounts_eur AS (
  SELECT
    o.id,
    o.placed_at,
    o.grand_total / er.rate AS amount_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.status IN ('paid','partially_fulfilled','fulfilled','refunded')
    AND o.placed_at >= DATE '2024-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
orders_in_quarter AS (
  SELECT
    q.quarter_start,
    COUNT(oa.id) AS order_count,
    SUM(oa.amount_eur) AS gmv_eur
  FROM quarters q
  LEFT JOIN order_amounts_eur oa
    ON oa.placed_at >= q.quarter_start
   AND oa.placed_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
)
SELECT
  q.year_num || '-Q' || q.quarter_num AS quarter,
  nm.new_merchant_count AS new_merchants,
  cm.cumulative_active_merchants,
  oiq.order_count,
  ROUND(oiq.gmv_eur, 2) AS gmv_eur,
  ROUND(oiq.gmv_eur / NULLIF(oiq.order_count, 0), 2) AS avg_order_value_eur
FROM quarters q
JOIN new_merchants nm ON nm.quarter_start = q.quarter_start
JOIN cumulative_merchants cm ON cm.quarter_start = q.quarter_start
JOIN orders_in_quarter oiq ON oiq.quarter_start = q.quarter_start
ORDER BY q.quarter_start ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: quarter, new_merchants, cumulative_active_merchants, order_count, gmv_eur, avg_order_value_eur

GraceThe query compiles and returns the expected columns. Submitting now.

Grace calls submit

WITH quarters AS (
  SELECT
    quarter_start,
    MAX(calendar_date) AS quarter_end,
    year_num,
    quarter_num
  FROM calendar_days
  WHERE quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-12-31'
  GROUP BY quarter_start, year_num, quarter_num
),
new_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS new_merchant_count
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at >= q.quarter_start
   AND t.created_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
),
cumulative_merchants AS (
  SELECT
    q.quarter_start,
    COUNT(t.id) AS cumulative_active_merchants
  FROM quarters q
  LEFT JOIN tenants t
    ON t.created_at < q.quarter_end + INTERVAL '1 day'
   AND t.deleted_at IS NULL
  GROUP BY q.quarter_start
),
order_amounts_eur AS (
  SELECT
    o.id,
    o.placed_at,
    o.grand_total / er.rate AS amount_eur
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.status IN ('paid','partially_fulfilled','fulfilled','refunded')
    AND o.placed_at >= DATE '2024-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
orders_in_quarter AS (
  SELECT
    q.quarter_start,
    COUNT(oa.id) AS order_count,
    SUM(oa.amount_eur) AS gmv_eur
  FROM quarters q
  LEFT JOIN order_amounts_eur oa
    ON oa.placed_at >= q.quarter_start
   AND oa.placed_at < q.quarter_end + INTERVAL '1 day'
  GROUP BY q.quarter_start
)
SELECT
  q.year_num || '-Q' || q.quarter_num AS quarter,
  nm.new_merchant_count AS new_merchants,
  cm.cumulative_active_merchants,
  oiq.order_count,
  ROUND(oiq.gmv_eur, 2) AS gmv_eur,
  ROUND(oiq.gmv_eur / NULLIF(oiq.order_count, 0), 2) AS avg_order_value_eur
FROM quarters q
JOIN new_merchants nm ON nm.quarter_start = q.quarter_start
JOIN cumulative_merchants cm ON cm.quarter_start = q.quarter_start
JOIN orders_in_quarter oiq ON oiq.quarter_start = q.quarter_start
ORDER BY q.quarter_start ASC

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

GraceSubmitted. The report covers the 8 requested quarters (2024-Q1 → 2025-Q4) with new merchants, cumulative active merchants, order count, GMV EUR, and average order value EUR, built from `tenants` and `orders` per the spec, with EUR conversion via same-day `exchange_rates`.

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

Merchant and order growth by quarter

Maren Okafor writes to Hannah Brooks

Hannah Brooks plans the night

Grace writes the SQL

The report comes back overnight

What came of it

2. Growth is real and broad-based, driven by existing merchants, not new ones

It becomes benchmark query q005

Everything, as recorded