Queries · q011 · Chief Executive Officer · week 1, night 4

Vertical health: merchant status mix and support load

“Which verticals have the most at-risk merchants or the heaviest support burden, independent of revenue growth?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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 4, request 2 of 3
Priority 1 is now fully resolved and consistent (corrected vertical report sums to ~€53,186, matching the merchant-level total exactly). Priority 3 is answered as far as the data allows: take rate is stable (~2.5-2.8%, no clear trend), and I've flagged that the platform-plan billing side genuinely isn't modeled in this warehouse. Priority 2 is covered from night 2. For this final night I'm using all 3 slots to close out priority 4 (vertical health) and priority 5 (warning signs), which haven't been touched yet, since they're explicitly what the Friday memo still needs. One thing jumped out while reviewing: the vertical revenue report only found 57 merchants with a settled payout in the trailing 12 months, versus 111 total tenants reported as 'cumulative active merchants' in night 2 (which only used signup date, not actual activity) — that's a big gap worth quantifying directly, since it could mean either a lot of merchants are still ramping up or a lot have gone quiet/churned. I'm building the status/activity report, a vertical-level health report (status mix + support load), and a company-wide operational trend report (payment failures, refunds, tickets, CSAT) to surface any board-level concerns.
Vertical health: merchant status mix and support load
Question
Which verticals have the most at-risk merchants or the heaviest support burden, independent of revenue growth?
One row per
merchant vertical (tenants.industry)
Columns
- vertical (industry)
- total merchant count
- active merchant count (status='active')
- at-risk merchant count (status in ('past_due','suspended','churned'))
- tickets per merchant (trailing 12 months)
- average CSAT score (trailing 12 months, 1-5)

Omar 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 tenant_vertical AS (
  SELECT id AS tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, status
  FROM tenants
  WHERE deleted_at IS NULL
),
merchant_counts AS (
  SELECT vertical,
    COUNT(*) AS total_merchants,
    COUNT(*) FILTER (WHERE status = 'active') AS active_merchants,
    COUNT(*) FILTER (WHERE status IN ('past_due','suspended','churned')) AS at_risk_merchants
  FROM tenant_vertical
  GROUP BY vertical
),
tickets_in_window AS (
  SELECT tv.vertical, COUNT(*) AS ticket_count
  FROM tickets t
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
),
csat_in_window AS (
  SELECT tv.vertical, AVG(c.score) AS avg_score
  FROM csat_responses c
  JOIN tickets t ON t.id = c.ticket_id
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
)
SELECT
  mc.vertical,
  mc.total_merchants,
  mc.active_merchants,
  mc.at_risk_merchants,
  ROUND(COALESCE(ti.ticket_count, 0)::numeric / mc.total_merchants, 2) AS tickets_per_merchant,
  ROUND(cw.avg_score, 2) AS avg_csat_score
FROM merchant_counts mc
LEFT JOIN tickets_in_window ti ON ti.vertical = mc.vertical
LEFT JOIN csat_in_window cw ON cw.vertical = mc.vertical
ORDER BY mc.at_risk_merchants DESC, mc.vertical ASC

“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 gives one row per vertical (with nulls/blanks grouped as 'Unspecified'), showing total/active/at-risk merchant counts and trailing-12-month ticket load and CSAT, ordered by at-risk count descending.”

The report comes back overnight

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

vertical                  | total_merchants | active_merchants | at_risk_merchants | tickets_per_merchant | avg_csat_score
--------------------------+-----------------+------------------+-------------------+----------------------+---------------
Apparel & Footwear        |              22 |               21 |                 1 |                43.91 |           3.12
Beauty                    |              12 |               11 |                 1 |                 17.5 |           3.37
Food & Coffee             |              11 |               10 |                 1 |                40.91 |           3.45
Jewelry & Accessories     |               5 |                4 |                 1 |                  1.4 |            3.0
Books & Stationery        |               5 |                5 |                 0 |                  4.6 |            3.0
Consumer Electronics      |               8 |                8 |                 0 |                 9.38 |           3.28
Home & Living             |              16 |               16 |                 0 |                13.88 |           3.71
Industrial Supplies (B2B) |               5 |                5 |                 0 |                 16.2 |           3.27
Office Supplies (B2B)     |               4 |                4 |                 0 |                10.25 |           3.38
Pet Supplies              |              10 |               10 |                 0 |                 12.2 |           3.07
Sports & Outdoor          |              13 |               13 |                 0 |                 9.38 |           3.56
(11 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q009Vertical revenue growth, 2024 vs 2025
- q010Merchant status and payout activity, today

It becomes benchmark query q011

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

spellingcast
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
made explicita selected t.col gets AS col, its standard name made explicit (6×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)

The benchmark text, against the submitted SQL

WITH tenant_vertical AS (  SELECT id AS tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, status  FROM tenants  WHERE deleted_at IS NULL),merchant_counts AS (  SELECT vertical,    COUNT(*) AS total_merchants,    COUNT(*) FILTER (WHERE status = 'active') AS active_merchants,    COUNT(*) FILTER (WHERE status IN ('past_due','suspended','churned')) AS at_risk_merchants  FROM tenant_vertical  GROUP BY vertical),tickets_in_window AS (  SELECT tv.vertical, COUNT(*) AS ticket_count  SELECT tv.vertical AS vertical, COUNT(*) AS ticket_count  FROM tickets t  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'  GROUP BY tv.vertical),csat_in_window AS (  SELECT tv.vertical, AVG(c.score) AS avg_score  SELECT tv.vertical AS vertical, AVG(CAST(c.score AS NUMERIC(18,6))) AS avg_score  FROM csat_responses c  JOIN tickets t ON t.id = c.ticket_id  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'  GROUP BY tv.vertical)SELECT  mc.vertical,  mc.total_merchants,  mc.active_merchants,  mc.at_risk_merchants,  ROUND(COALESCE(ti.ticket_count, 0)::numeric / mc.total_merchants, 2) AS tickets_per_merchant,  ROUND(cw.avg_score, 2) AS avg_csat_score  mc.vertical AS vertical,  mc.total_merchants AS total_merchants,  mc.active_merchants AS active_merchants,  mc.at_risk_merchants AS at_risk_merchants,  CAST(COALESCE(ti.ticket_count, 0) AS numeric(18,6)) / mc.total_merchants AS tickets_per_merchant,  cw.avg_score AS avg_csat_scoreFROM merchant_counts mcLEFT JOIN tickets_in_window ti ON ti.vertical = mc.verticalLEFT JOIN csat_in_window cw ON cw.vertical = mc.verticalORDER BY mc.at_risk_merchants DESC, mc.vertical ASCORDER BY mc.at_risk_merchants DESC NULLS FIRST, mc.vertical ASC NULLS LAST

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

WITH tenant_vertical AS (
  SELECT id AS tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, status
  FROM tenants
  WHERE deleted_at IS NULL
),
merchant_counts AS (
  SELECT vertical,
    COUNT(*) AS total_merchants,
    COUNT(*) FILTER (WHERE status = 'active') AS active_merchants,
    COUNT(*) FILTER (WHERE status IN ('past_due','suspended','churned')) AS at_risk_merchants
  FROM tenant_vertical
  GROUP BY vertical
),
tickets_in_window AS (
  SELECT tv.vertical AS vertical, COUNT(*) AS ticket_count
  FROM tickets t
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
),
csat_in_window AS (
  SELECT tv.vertical AS vertical, AVG(CAST(c.score AS NUMERIC(18,6))) AS avg_score
  FROM csat_responses c
  JOIN tickets t ON t.id = c.ticket_id
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
)
SELECT
  mc.vertical AS vertical,
  mc.total_merchants AS total_merchants,
  mc.active_merchants AS active_merchants,
  mc.at_risk_merchants AS at_risk_merchants,
  CAST(COALESCE(ti.ticket_count, 0) AS numeric(18,6)) / mc.total_merchants AS tickets_per_merchant,
  cw.avg_score AS avg_csat_score
FROM merchant_counts mc
LEFT JOIN tickets_in_window ti ON ti.vertical = mc.vertical
LEFT JOIN csat_in_window cw ON cw.vertical = mc.vertical
ORDER BY mc.at_risk_merchants DESC NULLS FIRST, mc.vertical ASC NULLS LAST

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

Omar's whole session, 7 turns

The request, as Omar received it

Report request from Hannah Brooks (analyst for the Chief Executive Officer):

Title: Vertical health: merchant status mix and support load
Question: Which verticals have the most at-risk merchants or the heaviest support burden, independent of revenue growth?
Grain: one row per merchant vertical (tenants.industry)
Columns:
- vertical (industry)
- total merchant count
- active merchant count (status='active')
- at-risk merchant count (status in ('past_due','suspended','churned'))
- tickets per merchant (trailing 12 months)
- average CSAT score (trailing 12 months, 1-5)
Details: Source: tenants (for industry and status, excluding deleted_at not null) left joined to: (a) tickets where tickets.requester_user_id's user belongs to that tenant and tickets.created_at is in [2025-01-01, 2026-01-01), counted per tenant then averaged per vertical as total tickets / total merchant count in that vertical; (b) csat_responses joined via ticket_id to tickets in the same window, averaging csat_responses.score per vertical across all responses from merchants in that vertical. Group null/blank industry into 'Unspecified'. Order by at-risk merchant count descending, tie-break by vertical name ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Omar calls dry_run

WITH tenant_vertical AS (
  SELECT id AS tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, status
  FROM tenants
  WHERE deleted_at IS NULL
),
merchant_counts AS (
  SELECT vertical,
    COUNT(*) AS total_merchants,
    COUNT(*) FILTER (WHERE status = 'active') AS active_merchants,
    COUNT(*) FILTER (WHERE status IN ('past_due','suspended','churned')) AS at_risk_merchants
  FROM tenant_vertical
  GROUP BY vertical
),
tickets_in_window AS (
  SELECT tv.vertical, COUNT(*) AS ticket_count
  FROM tickets t
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
),
csat_in_window AS (
  SELECT tv.vertical, AVG(c.score) AS avg_score
  FROM csat_responses c
  JOIN tickets t ON t.id = c.ticket_id
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
)
SELECT
  mc.vertical,
  mc.total_merchants,
  mc.active_merchants,
  mc.at_risk_merchants,
  ROUND(COALESCE(ti.ticket_count, 0)::numeric / mc.total_merchants, 2) AS tickets_per_merchant,
  ROUND(cw.avg_score, 2) AS avg_csat_score
FROM merchant_counts mc
LEFT JOIN tickets_in_window ti ON ti.vertical = mc.vertical
LEFT JOIN csat_in_window cw ON cw.vertical = mc.vertical
ORDER BY mc.at_risk_merchants DESC, mc.vertical ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: vertical, total_merchants, active_merchants, at_risk_merchants, tickets_per_merchant, avg_csat_score

OmarQuery compiles and returns the expected columns. Submitting now.

Omar calls submit

WITH tenant_vertical AS (
  SELECT id AS tenant_id, COALESCE(NULLIF(TRIM(industry), ''), 'Unspecified') AS vertical, status
  FROM tenants
  WHERE deleted_at IS NULL
),
merchant_counts AS (
  SELECT vertical,
    COUNT(*) AS total_merchants,
    COUNT(*) FILTER (WHERE status = 'active') AS active_merchants,
    COUNT(*) FILTER (WHERE status IN ('past_due','suspended','churned')) AS at_risk_merchants
  FROM tenant_vertical
  GROUP BY vertical
),
tickets_in_window AS (
  SELECT tv.vertical, COUNT(*) AS ticket_count
  FROM tickets t
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
),
csat_in_window AS (
  SELECT tv.vertical, AVG(c.score) AS avg_score
  FROM csat_responses c
  JOIN tickets t ON t.id = c.ticket_id
  JOIN tenant_vertical tv ON tv.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01' AND t.created_at < TIMESTAMP '2026-01-01'
  GROUP BY tv.vertical
)
SELECT
  mc.vertical,
  mc.total_merchants,
  mc.active_merchants,
  mc.at_risk_merchants,
  ROUND(COALESCE(ti.ticket_count, 0)::numeric / mc.total_merchants, 2) AS tickets_per_merchant,
  ROUND(cw.avg_score, 2) AS avg_csat_score
FROM merchant_counts mc
LEFT JOIN tickets_in_window ti ON ti.vertical = mc.vertical
LEFT JOIN csat_in_window cw ON cw.vertical = mc.vertical
ORDER BY mc.at_risk_merchants DESC, mc.vertical ASC

The warehouse answers

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

OmarSubmitted. The report gives one row per vertical (with nulls/blanks grouped as 'Unspecified'), showing total/active/at-risk merchant counts and trailing-12-month ticket load and CSAT, ordered by at-risk count descending.

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

Omar's transcript (267 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)

Vertical health: merchant status mix and support load

Maren Okafor writes to Hannah Brooks

Hannah Brooks plans the night

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q011

Everything, as recorded