Queries · q009 · Chief Executive Officer · week 1, night 3
“Which verticals are growing and which are shrinking year over year, in both fee revenue and merchant count?”
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 3, request 3 of 3
The reconciliation confirms the correct answer: payouts.status only has two values, 'paid' (fully settled, paid_at always populated) and 'in_transit' (future-dated, paid_at always null). Filtering status='paid' with paid_at in [2025-01-01, 2026-01-01) gives fee revenue of €53,185.85 for the trailing 12 months — matching night 1's merchant-level report almost exactly. That means the merchant-concentration report (Report 1, night 1) was correct, and the vertical-level report (Report 3, night 1, €93.6k) has a bug, most likely a join fan-out against exchange_rates or tenants. I'm having that redone first since it's a wrong number already in front of me. I also want to note for Maren: I don't see any table in the catalog that represents Tidewick's own monthly platform-plan billing of merchants — billing/subscriptions/invoices tables are all tenant-scoped to the merchant's own business (their customers' subscriptions), not Tidewick-to-merchant billing. So priority #3 (platform fees vs. processing mix) likely can't be answered from this warehouse as a two-sided split; I'll reframe it around the processing take-rate trend, which is measurable, and flag the gap. Third slot starts priority #4 (vertical health) with a YoY growth view by vertical.
The engineer · 1 dry run on the empty schema, then the submission
WITH paid_payouts AS ( SELECT p.tenant_id, t.industry, p.fee_amount, p.currency_code, p.paid_at, CASE WHEN p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2025-01-01' THEN 2024 WHEN p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' THEN 2025 END AS year FROM payouts p JOIN tenants t ON t.id = p.tenant_id WHERE p.status = 'paid' AND p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2026-01-01' ), payout_fees_eur AS ( SELECT pp.tenant_id, pp.industry, pp.year, pp.fee_amount / er.rate AS fee_amount_eur FROM paid_payouts pp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = DATE(pp.paid_at) WHERE pp.year IS NOT NULL ), tenant_year_revenue AS ( SELECT tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, year, SUM(fee_amount_eur) AS fee_revenue_eur FROM payout_fees_eur GROUP BY tenant_id, vertical, year ), vertical_year AS ( SELECT vertical, year, SUM(fee_revenue_eur) AS fee_revenue_eur, COUNT(DISTINCT tenant_id) AS merchant_count FROM tenant_year_revenue GROUP BY vertical, year ), vertical_pivot AS ( SELECT vertical, SUM(CASE WHEN year=2024 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2024, SUM(CASE WHEN year=2025 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2025, SUM(CASE WHEN year=2024 THEN merchant_count ELSE 0 END) AS merchant_count_2024, SUM(CASE WHEN year=2025 THEN merchant_count ELSE 0 END) AS merchant_count_2025 FROM vertical_year GROUP BY vertical ) SELECT vertical, ROUND(fee_revenue_2024, 2) AS fee_revenue_2024_eur, ROUND(fee_revenue_2025, 2) AS fee_revenue_2025_eur, CASE WHEN fee_revenue_2024 = 0 THEN NULL ELSE ROUND(100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024, 1) END AS yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_2025 - merchant_count_2024 AS merchant_count_change FROM vertical_pivot ORDER BY fee_revenue_2025 DESC, vertical ASC
“Query compiles and matches the requested grain and columns.”
“Submitted. Summary of the approach:
- Filtered payouts to status='paid' within the two calendar-year windows, joined to tenants for industry.
- Converted fee_amount to EUR per payout using exchange_rates (base EUR → quote = payout currency, matched by date), dividing by the rate.
- Aggregated to tenant × vertical × year first (to avoid fan-out), then rolled up to vertical × year for revenue sums and distinct merchant counts.
- Pivoted into 2024/2025 columns, computed YoY % growth (null when 2024 revenue is zero), merchant counts, and the count change.
- Blank/null industries grouped as 'Unspecified'; ordered by 2025 fee revenue descending, then vertical name.”
As Hannah Brooks saw it, from the small dataset the agents worked on
vertical | fee_revenue_2024_eur | fee_revenue_2025_eur | yoy_fee_revenue_growth_pct | merchant_count_2024 | merchant_count_2025 | merchant_count_change --------------------------+----------------------+----------------------+----------------------------+---------------------+---------------------+---------------------- Apparel & Footwear | 25931.91 | 29028.1 | 11.9 | 10 | 12 | 2 Sports & Outdoor | 3814.07 | 5796.45 | 52.0 | 4 | 5 | 1 Office Supplies (B2B) | 3430.29 | 4592.96 | 33.9 | 2 | 2 | 0 Industrial Supplies (B2B) | 1946.58 | 3499.84 | 79.8 | 1 | 1 | 0 Home & Living | 2377.32 | 3085.55 | 29.8 | 9 | 9 | 0 Food & Coffee | 2178.41 | 2597.96 | 19.3 | 3 | 5 | 2 Beauty | 1811.72 | 2346.81 | 29.5 | 7 | 8 | 1 Pet Supplies | 906.35 | 1468.94 | 62.1 | 5 | 7 | 2 Consumer Electronics | 290.78 | 529.66 | 82.2 | 3 | 3 | 0 Books & Stationery | 99.92 | 145.03 | 45.1 | 3 | 4 | 1 Jewelry & Accessories | 140.49 | 94.56 | -32.7 | 2 | 1 | -1 (11 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH paid_payouts AS ( SELECT p.tenant_id, t.industry, p.fee_amount, p.currency_code, p.paid_at, SELECT p.tenant_id AS tenant_id, t.industry AS industry, p.fee_amount AS fee_amount, p.currency_code AS currency_code, p.paid_at AS paid_at, CASE WHEN p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2025-01-01' THEN 2024 WHEN p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' THEN 2025 END AS year FROM payouts p JOIN tenants t ON t.id = p.tenant_id WHERE p.status = 'paid' AND p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2026-01-01'),payout_fees_eur AS ( SELECT pp.tenant_id, pp.industry, pp.year, pp.fee_amount / er.rate AS fee_amount_eur SELECT pp.tenant_id AS tenant_id, pp.industry AS industry, pp.year AS year, CAST(pp.fee_amount / er.rate AS NUMERIC(18,6)) AS fee_amount_eur FROM paid_payouts pp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = DATE(pp.paid_at) ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = CAST(pp.paid_at AS DATE) WHERE pp.year IS NOT NULL),tenant_year_revenue AS ( SELECT tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, year, SUM(fee_amount_eur) AS fee_revenue_eur FROM payout_fees_eur GROUP BY tenant_id, vertical, year),vertical_year AS ( SELECT vertical, year, SUM(fee_revenue_eur) AS fee_revenue_eur, COUNT(DISTINCT tenant_id) AS merchant_count FROM tenant_year_revenue GROUP BY vertical, year),vertical_pivot AS ( SELECT vertical, SUM(CASE WHEN year=2024 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2024, SUM(CASE WHEN year=2025 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2025, SUM(CASE WHEN year=2024 THEN merchant_count ELSE 0 END) AS merchant_count_2024, SUM(CASE WHEN year=2025 THEN merchant_count ELSE 0 END) AS merchant_count_2025 FROM vertical_year GROUP BY vertical)SELECT vertical, ROUND(fee_revenue_2024, 2) AS fee_revenue_2024_eur, ROUND(fee_revenue_2025, 2) AS fee_revenue_2025_eur, fee_revenue_2024 AS fee_revenue_2024_eur, fee_revenue_2025 AS fee_revenue_2025_eur, CASE WHEN fee_revenue_2024 = 0 THEN NULL ELSE ROUND(100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024, 1) ELSE 100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024 END AS yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_2025 - merchant_count_2024 AS merchant_count_changeFROM vertical_pivotORDER BY fee_revenue_2025 DESC, vertical ASCORDER BY fee_revenue_2025 DESC NULLS FIRST, vertical ASC NULLS LAST
WITH paid_payouts AS ( SELECT p.tenant_id AS tenant_id, t.industry AS industry, p.fee_amount AS fee_amount, p.currency_code AS currency_code, p.paid_at AS paid_at, CASE WHEN p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2025-01-01' THEN 2024 WHEN p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' THEN 2025 END AS year FROM payouts p JOIN tenants t ON t.id = p.tenant_id WHERE p.status = 'paid' AND p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2026-01-01' ), payout_fees_eur AS ( SELECT pp.tenant_id AS tenant_id, pp.industry AS industry, pp.year AS year, CAST(pp.fee_amount / er.rate AS NUMERIC(18,6)) AS fee_amount_eur FROM paid_payouts pp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = CAST(pp.paid_at AS DATE) WHERE pp.year IS NOT NULL ), tenant_year_revenue AS ( SELECT tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, year, SUM(fee_amount_eur) AS fee_revenue_eur FROM payout_fees_eur GROUP BY tenant_id, vertical, year ), vertical_year AS ( SELECT vertical, year, SUM(fee_revenue_eur) AS fee_revenue_eur, COUNT(DISTINCT tenant_id) AS merchant_count FROM tenant_year_revenue GROUP BY vertical, year ), vertical_pivot AS ( SELECT vertical, SUM(CASE WHEN year=2024 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2024, SUM(CASE WHEN year=2025 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2025, SUM(CASE WHEN year=2024 THEN merchant_count ELSE 0 END) AS merchant_count_2024, SUM(CASE WHEN year=2025 THEN merchant_count ELSE 0 END) AS merchant_count_2025 FROM vertical_year GROUP BY vertical ) SELECT vertical, fee_revenue_2024 AS fee_revenue_2024_eur, fee_revenue_2025 AS fee_revenue_2025_eur, CASE WHEN fee_revenue_2024 = 0 THEN NULL ELSE 100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024 END AS yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_2025 - merchant_count_2024 AS merchant_count_change FROM vertical_pivot ORDER BY fee_revenue_2025 DESC NULLS FIRST, vertical ASC 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: Vertical revenue growth, 2024 vs 2025 Question: Which verticals are growing and which are shrinking year over year, in both fee revenue and merchant count? Grain: one row per merchant vertical (tenants.industry) Columns: - vertical (industry) - fee revenue 2024 (EUR) - fee revenue 2025 (EUR) - YoY fee revenue growth (%) - merchant count 2024 - merchant count 2025 - merchant count change Details: Source: payouts (status='paid') joined to tenants for industry, aggregated per tenant first and then grouped by industry (same anti-fan-out approach as the corrected vertical revenue report in this batch). Two calendar-year windows on paid_at: 2024 = [2024-01-01, 2025-01-01), 2025 = [2025-01-01, 2026-01-01). Convert gross/fee amounts to EUR via exchange_rates (base_code='EUR', quote_code=payouts.currency_code, as_of=date(paid_at)) before summing. 'Merchant count' per year = distinct tenant_id with at least one qualifying payout that year. YoY fee revenue growth % = 100 * (2025 fee revenue - 2024 fee revenue) / 2024 fee revenue (null/flag if 2024 revenue is zero for a vertical that only appears in 2025). Group null/blank industry into 'Unspecified'. Order by 2025 fee revenue descending, tie-break by vertical name ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH paid_payouts AS ( SELECT p.tenant_id, t.industry, p.fee_amount, p.currency_code, p.paid_at, CASE WHEN p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2025-01-01' THEN 2024 WHEN p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' THEN 2025 END AS year FROM payouts p JOIN tenants t ON t.id = p.tenant_id WHERE p.status = 'paid' AND p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2026-01-01' ), payout_fees_eur AS ( SELECT pp.tenant_id, pp.industry, pp.year, pp.fee_amount / er.rate AS fee_amount_eur FROM paid_payouts pp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = DATE(pp.paid_at) WHERE pp.year IS NOT NULL ), tenant_year_revenue AS ( SELECT tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, year, SUM(fee_amount_eur) AS fee_revenue_eur FROM payout_fees_eur GROUP BY tenant_id, vertical, year ), vertical_year AS ( SELECT vertical, year, SUM(fee_revenue_eur) AS fee_revenue_eur, COUNT(DISTINCT tenant_id) AS merchant_count FROM tenant_year_revenue GROUP BY vertical, year ), vertical_pivot AS ( SELECT vertical, SUM(CASE WHEN year=2024 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2024, SUM(CASE WHEN year=2025 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2025, SUM(CASE WHEN year=2024 THEN merchant_count ELSE 0 END) AS merchant_count_2024, SUM(CASE WHEN year=2025 THEN merchant_count ELSE 0 END) AS merchant_count_2025 FROM vertical_year GROUP BY vertical ) SELECT vertical, ROUND(fee_revenue_2024, 2) AS fee_revenue_2024_eur, ROUND(fee_revenue_2025, 2) AS fee_revenue_2025_eur, CASE WHEN fee_revenue_2024 = 0 THEN NULL ELSE ROUND(100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024, 1) END AS yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_2025 - merchant_count_2024 AS merchant_count_change FROM vertical_pivot ORDER BY fee_revenue_2025 DESC, vertical ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: vertical, fee_revenue_2024_eur, fee_revenue_2025_eur, yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_change
submitWITH paid_payouts AS ( SELECT p.tenant_id, t.industry, p.fee_amount, p.currency_code, p.paid_at, CASE WHEN p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2025-01-01' THEN 2024 WHEN p.paid_at >= DATE '2025-01-01' AND p.paid_at < DATE '2026-01-01' THEN 2025 END AS year FROM payouts p JOIN tenants t ON t.id = p.tenant_id WHERE p.status = 'paid' AND p.paid_at >= DATE '2024-01-01' AND p.paid_at < DATE '2026-01-01' ), payout_fees_eur AS ( SELECT pp.tenant_id, pp.industry, pp.year, pp.fee_amount / er.rate AS fee_amount_eur FROM paid_payouts pp JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = pp.currency_code AND er.as_of = DATE(pp.paid_at) WHERE pp.year IS NOT NULL ), tenant_year_revenue AS ( SELECT tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, year, SUM(fee_amount_eur) AS fee_revenue_eur FROM payout_fees_eur GROUP BY tenant_id, vertical, year ), vertical_year AS ( SELECT vertical, year, SUM(fee_revenue_eur) AS fee_revenue_eur, COUNT(DISTINCT tenant_id) AS merchant_count FROM tenant_year_revenue GROUP BY vertical, year ), vertical_pivot AS ( SELECT vertical, SUM(CASE WHEN year=2024 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2024, SUM(CASE WHEN year=2025 THEN fee_revenue_eur ELSE 0 END) AS fee_revenue_2025, SUM(CASE WHEN year=2024 THEN merchant_count ELSE 0 END) AS merchant_count_2024, SUM(CASE WHEN year=2025 THEN merchant_count ELSE 0 END) AS merchant_count_2025 FROM vertical_year GROUP BY vertical ) SELECT vertical, ROUND(fee_revenue_2024, 2) AS fee_revenue_2024_eur, ROUND(fee_revenue_2025, 2) AS fee_revenue_2025_eur, CASE WHEN fee_revenue_2024 = 0 THEN NULL ELSE ROUND(100.0 * (fee_revenue_2025 - fee_revenue_2024) / fee_revenue_2024, 1) END AS yoy_fee_revenue_growth_pct, merchant_count_2024, merchant_count_2025, merchant_count_2025 - merchant_count_2024 AS merchant_count_change FROM vertical_pivot ORDER BY fee_revenue_2025 DESC, vertical ASC
Submitted to tonight's batch. Your work on this request is done.