Queries · q006 · Chief Executive Officer · week 1, night 2
“Is Tidewick's order-volume growth coming from newly-joined merchants or from existing merchants transacting more?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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.
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
The analyst · week 1, night 2, request 3 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.
The engineer · 1 dry run on the empty schema, then the submission
WITH classified AS ( SELECT o.tenant_id, o.id as order_id, o.grand_total, o.currency_code, o.placed_at, cd.quarter_start, cd.year_num, cd.quarter_num, CASE WHEN t.created_at >= cd.quarter_start AND t.created_at < cd.quarter_start + interval '3 months' THEN 'new' ELSE 'existing' END AS cohort, o.grand_total / er.rate AS amount_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS date) 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 cd.quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-10-01' ), agg AS ( SELECT year_num, quarter_num, quarter_start, cohort, COUNT(DISTINCT tenant_id) AS merchant_count, COUNT(*) AS order_count, SUM(amount_eur) AS gmv_eur FROM classified GROUP BY year_num, quarter_num, quarter_start, cohort ) SELECT year_num || '-Q' || quarter_num AS quarter, cohort, merchant_count, order_count, ROUND(gmv_eur, 2) AS gmv_eur, ROUND(100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num), 2) AS share_of_quarter_gmv_pct FROM agg ORDER BY year_num, quarter_num, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END)
“Query compiles and returns exactly the requested columns. Submitting.”
“Submitted. The report builds one row per quarter (2024‑Q1–2025‑Q4) × cohort (new/existing), classifying each tenant by whether tenants.created_at falls inside that quarter, converts orders.grand_total to EUR via the daily exchange_rates as specified, and computes each cohort's share of that quarter's combined GMV.”
As Hannah Brooks saw it, from the small dataset the agents worked on
quarter | cohort | merchant_count | order_count | gmv_eur | share_of_quarter_gmv_pct --------+----------+----------------+-------------+-----------+------------------------- 2024-Q1 | new | 2 | 22 | 1454.77 | 0.33 2024-Q1 | existing | 39 | 1695 | 440576.05 | 99.67 2024-Q2 | existing | 40 | 1909 | 477592.82 | 100.0 2024-Q3 | new | 2 | 3 | 205.51 | 0.04 2024-Q3 | existing | 42 | 1973 | 532403.08 | 99.96 2024-Q4 | new | 1 | 1 | 42.36 | 0.01 2024-Q4 | existing | 45 | 3460 | 779280.27 | 99.99 2025-Q1 | existing | 47 | 2417 | 721097.1 | 100.0 2025-Q2 | new | 2 | 10 | 700.98 | 0.13 2025-Q2 | existing | 47 | 2414 | 548726.25 | 99.87 2025-Q3 | new | 2 | 4 | 189.05 | 0.03 2025-Q3 | existing | 50 | 2553 | 542218.52 | 99.97 2025-Q4 | existing | 55 | 4259 | 894406.3 | 100.0 (13 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH classified AS ( SELECT o.tenant_id, o.id as order_id, o.grand_total, o.currency_code, o.placed_at, cd.quarter_start, cd.year_num, cd.quarter_num, SELECT o.tenant_id AS tenant_id, o.id as order_id, o.grand_total AS grand_total, o.currency_code AS currency_code, o.placed_at AS placed_at, cd.quarter_start AS quarter_start, cd.year_num AS year_num, cd.quarter_num AS quarter_num, CASE WHEN t.created_at >= cd.quarter_start AND t.created_at < cd.quarter_start + interval '3 months' THEN 'new' ELSE 'existing' END AS cohort, o.grand_total / er.rate AS amount_eur CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS amount_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS date) 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 cd.quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-10-01'),agg AS ( SELECT year_num, quarter_num, quarter_start, cohort, COUNT(DISTINCT tenant_id) AS merchant_count, COUNT(*) AS order_count, SUM(amount_eur) AS gmv_eur FROM classified GROUP BY year_num, quarter_num, quarter_start, cohort)SELECT year_num || '-Q' || quarter_num AS quarter, cohort, merchant_count, order_count, ROUND(gmv_eur, 2) AS gmv_eur, ROUND(100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num), 2) AS share_of_quarter_gmv_pct gmv_eur AS gmv_eur, 100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num) AS share_of_quarter_gmv_pctFROM aggORDER BY year_num, quarter_num, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END)ORDER BY year_num NULLS LAST, quarter_num NULLS LAST, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END) NULLS LAST, quarter_start NULLS LAST
WITH classified AS ( SELECT o.tenant_id AS tenant_id, o.id as order_id, o.grand_total AS grand_total, o.currency_code AS currency_code, o.placed_at AS placed_at, cd.quarter_start AS quarter_start, cd.year_num AS year_num, cd.quarter_num AS quarter_num, CASE WHEN t.created_at >= cd.quarter_start AND t.created_at < cd.quarter_start + interval '3 months' THEN 'new' ELSE 'existing' END AS cohort, CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS amount_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS date) 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 cd.quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-10-01' ), agg AS ( SELECT year_num, quarter_num, quarter_start, cohort, COUNT(DISTINCT tenant_id) AS merchant_count, COUNT(*) AS order_count, SUM(amount_eur) AS gmv_eur FROM classified GROUP BY year_num, quarter_num, quarter_start, cohort ) SELECT year_num || '-Q' || quarter_num AS quarter, cohort, merchant_count, order_count, gmv_eur AS gmv_eur, 100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num) AS share_of_quarter_gmv_pct FROM agg ORDER BY year_num NULLS LAST, quarter_num NULLS LAST, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END) NULLS LAST, quarter_start NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Hannah Brooks (analyst for the Chief Executive Officer):
Title: GMV growth by merchant cohort: new vs existing, per quarter
Question: Is Tidewick's order-volume growth coming from newly-joined merchants or from existing merchants transacting more?
Grain: one row per calendar quarter and cohort (new vs existing)
Columns:
- quarter
- cohort (new or existing)
- merchant count (distinct tenants with a qualifying order that quarter)
- order count
- GMV EUR
- share of that quarter's total GMV (%)
Details: Source: orders joined to tenants (on tenant_id) to classify each merchant as 'new' or 'existing' for a given quarter: a tenant is 'new' in a quarter if tenants.created_at falls within that same quarter, otherwise 'existing' (tenants.created_at before quarter start). Window: quarters 2024-Q1 through 2025-Q4 (8 quarters), using orders.placed_at to assign the quarter. Include only orders with status in ('paid','partially_fulfilled','fulfilled','refunded'), matching the growth-by-quarter report in this same batch. Convert orders.grand_total to EUR the same way (amount_eur = amount / rate, base_code='EUR', quote_code=orders.currency_code, as_of = orders.placed_at date). For each quarter there should be exactly two rows (new, existing); share of total GMV = that cohort's GMV EUR divided by the quarter's combined (new+existing) GMV EUR. Order by quarter ascending, then cohort (new before existing).
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH classified AS ( SELECT o.tenant_id, o.id as order_id, o.grand_total, o.currency_code, o.placed_at, cd.quarter_start, cd.year_num, cd.quarter_num, CASE WHEN t.created_at >= cd.quarter_start AND t.created_at < cd.quarter_start + interval '3 months' THEN 'new' ELSE 'existing' END AS cohort, o.grand_total / er.rate AS amount_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS date) 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 cd.quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-10-01' ), agg AS ( SELECT year_num, quarter_num, quarter_start, cohort, COUNT(DISTINCT tenant_id) AS merchant_count, COUNT(*) AS order_count, SUM(amount_eur) AS gmv_eur FROM classified GROUP BY year_num, quarter_num, quarter_start, cohort ) SELECT year_num || '-Q' || quarter_num AS quarter, cohort, merchant_count, order_count, ROUND(gmv_eur, 2) AS gmv_eur, ROUND(100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num), 2) AS share_of_quarter_gmv_pct FROM agg ORDER BY year_num, quarter_num, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: quarter, cohort, merchant_count, order_count, gmv_eur, share_of_quarter_gmv_pct
submitWITH classified AS ( SELECT o.tenant_id, o.id as order_id, o.grand_total, o.currency_code, o.placed_at, cd.quarter_start, cd.year_num, cd.quarter_num, CASE WHEN t.created_at >= cd.quarter_start AND t.created_at < cd.quarter_start + interval '3 months' THEN 'new' ELSE 'existing' END AS cohort, o.grand_total / er.rate AS amount_eur FROM orders o JOIN tenants t ON t.id = o.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS date) 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 cd.quarter_start BETWEEN DATE '2024-01-01' AND DATE '2025-10-01' ), agg AS ( SELECT year_num, quarter_num, quarter_start, cohort, COUNT(DISTINCT tenant_id) AS merchant_count, COUNT(*) AS order_count, SUM(amount_eur) AS gmv_eur FROM classified GROUP BY year_num, quarter_num, quarter_start, cohort ) SELECT year_num || '-Q' || quarter_num AS quarter, cohort, merchant_count, order_count, ROUND(gmv_eur, 2) AS gmv_eur, ROUND(100.0 * gmv_eur / SUM(gmv_eur) OVER (PARTITION BY year_num, quarter_num), 2) AS share_of_quarter_gmv_pct FROM agg ORDER BY year_num, quarter_num, (CASE WHEN cohort = 'new' THEN 0 ELSE 1 END)
Submitted to tonight's batch. Your work on this request is done.