Queries · q275 · Head of Merchant Success · week 2, night 4

Support experience for our top 15 merchants, and whether it tracks with CSAT

“How much support volume do our top merchants generate, how fast is our response, and does that show up in their CSAT?”

Manager · asked the questionSamuel WhitcombeHead of Merchant Success. New in the role, owns the relationship with the largest merchants and quarterly business reviews with them in January.

Analyst · planned the reportArjun PatelSegmenter: breaks totals down by merchant size, vertical, country and channel.

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

A business event lands on the Head of Merchant Success's desk
Week 2; shared with Chloé Marchetti, Head of Growth Marketing and Daniel Achterberg, VP Customer Experience
Churn has become a board topic, for merchants and for their shoppers. A cross-functional task force wants to understand which experiences precede leaving, and which early signals would let us act before it happens.
One of the 35 events we wrote for the role play; the manager heard of it at the start of week 2 and had to cover it in that week's note. How events arise · All the events
Samuel Whitcombe writes to Arjun Patel
The manager, at the start of week 2
Subject: Next week's priorities — QBR follow-ups + the churn task force

Hi Arjun,

Really useful memo — thank you. A few things jumped out that I want to understand better, plus a new cross-functional ask that's going to need a slot or two this week.

What surprised me / needs explaining first:
- Castell Fasteners is a contradiction — our #4 merchant, highly sticky customer base (88.6% repeat), but the weakest YoY growth of the top tier and a steep traffic drop. I want to know whether that traffic drop is a real warning sign or just how B2B industrial buying seasonally looks, before I raise it with them in their QBR.
- Good catch on the refund-rate math — please don't let anything with the >100% problem anywhere near a client-facing page until it's fixed.
My questions for this week:
1. Is Castell's traffic decline a real risk or seasonal noise? I want a clear enough answer that I either flag it to them directly in the QBR or leave it alone.
2. How are our top ~15 merchants' shoppers being supported, and does it show up in how those merchants feel about us? This was cut last week — please pick it back up.
3. For the task force below: which merchants have actually left the platform (or are clearly heading that way), and what did their last few months look like beforehand — their sales trend, their shoppers' buying behavior, how much support friction they had? I'm less interested in a single root cause and more in whatever pattern shows up consistently across the merchants who left, since that's what would let us catch the next one early.
New context — the churn task force: Churn is now a board topic, both merchant churn and end-shopper churn, and I'm leading this jointly with Chloé Marchetti and Daniel Achterberg. The deliverable the three of us owe the board is a shortlist of early-warning signals — things we can observe weeks or months before a merchant (or a merchant's shoppers) leaves — and a recommendation on which of those signals is reliable enough to act on. It feeds directly into two decisions: whether Merchant Success should get a proactive outreach trigger for at-risk accounts, and whether Growth Marketing and CX get an equivalent retention trigger for end-shoppers. So anything you can tell me this week about what preceded past departures — on the merchant side especially, since that's my piece — is directly useful input, even if it's partial. I'll compare notes with Chloé's and Daniel's analysts before we draft anything for the board.

Given the slot limits, prioritize in this order: Castell explanation, then the task-force churn pattern, then support/CSAT if a slot remains.

Thanks, Samuel
A note from the VP Customer Experience, Daniel Achterberg
Subject: CX signals for the churn task force

Arjun,

A few things I'd want the task force to look at from the shopper/support side:
1. Does ticket experience precede merchant churn? For merchants who later left, did their shoppers see slower first responses, more reopened tickets, or more escalations in the months before? I'd want to know how far out a warning shows up.
2. Do returns/refunds friction and churn move together? Where refunds get delayed, disputed, or partially handled, do we see both unhappy shoppers (low ratings/CSAT) and merchants churning shortly after? I'm curious whether refund delay is a leading indicator or just a symptom.
3. Are complaint themes shifting before a merchant leaves? Is there a recognizable pattern in what shoppers are complaining about (e.g. shipping, product quality, billing) in the weeks before a merchant churns, versus a stable merchant?
If we can pin down even one or two of these as reliable early signals, it changes what Samuel's team and I should be monitoring week to week rather than finding out after the fact.

Daniel
A note from the Head of Growth Marketing, Chloé Marchetti
Subject: Churn task force — what I'd like us to look at from Growth

Hi Arjun,

Glad this is getting cross-functional attention. From the marketing side, here's what I think matters:
1. Do shoppers who stop engaging with our marketing touches (emails, campaigns, loyalty) churn sooner than the ones who stay engaged? If disengagement shows up weeks before a churn, that's an early signal worth acting on.
2. Does a merchant's own marketing activity level relate to whether their shoppers stick around? I want to know if merchants who run fewer campaigns, promotions, or loyalty programs are the ones seeing higher shopper churn — that would tell us whether under-investment in marketing tools is part of the story.
3. Is there a pattern in how promo- or discount-driven shoppers behave right before leaving — e.g. do they go quiet after a coupon or loyalty redemption, or does spend drop off without any further marketing response from us?
Happy to jump on a call if useful — mainly want to make sure whatever "early warning" signals come out of this include the marketing-touch side, not just support or payment signals.

Best, Chloé
Arjun Patel plans the night
The analyst · week 2, night 4, request 3 of 3
Key read from last night: no merchant with 20+ lifetime orders has gone silent for 180+ days (0 rows) - established merchants aren't quietly churning, narrowing real merchant churn to the 9 small/new accounts already found. Marketing engagement barely differs between shoppers who later lapse and those who stay active, and support response times before a lapse track the same general degradation active shoppers also saw (likely the holiday ticket surge other teams flagged) rather than a lapse-specific pattern - both look like weak signals so far. Tonight (last night): (A) loosen the silent-churn threshold to check for a softer "slowing down" tier among established merchants, (B) test the refund-friction hypothesis directly, and (C) finally deliver the deferred top-15 support/CSAT read.
Support experience for our top 15 merchants, and whether it tracks with CSAT
Question
How much support volume do our top merchants generate, how fast is our response, and does that show up in their CSAT?
One row per
merchant (tenant), limited to the top 15 by last-90-day revenue
Columns
- merchant name
- industry
- tickets last 90 days
- tickets per 100 orders
- avg first response hours
- avg resolution hours
- csat avg score
- csat response count

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 orders_window AS (
  SELECT o.tenant_id, o.id AS order_id, o.currency_code, o.grand_total, o.placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id, ow.order_id, ow.grand_total / fx.rate AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC
  LIMIT 15
),
tickets_window AS (
  SELECT t.tenant_id, t.id AS ticket_id,
    EXTRACT(EPOCH FROM (t.first_response_at - t.created_at))/3600.0 AS first_response_hours,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id, c.score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(score) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  ROUND(COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0), 2) AS tickets_per_100_orders,
  ROUND(ta.avg_first_response_hours, 2) AS avg_first_response_hours,
  ROUND(ta.avg_resolution_hours, 2) AS avg_resolution_hours,
  ROUND(ca.csat_avg_score, 2) AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC, top15.tenant_id ASC
LIMIT 50

“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 ranks the top 15 tenants by Oct 3–Dec 31 2025 EUR order revenue, then joins ticket volume/speed and CSAT metrics for that same window, with zero/NULL shown where a merchant had no tickets or no CSAT responses in the period.”

The report comes back overnight

As Arjun Patel saw it, from the small dataset the agents worked on

merchant_name          | industry                  | tickets_last_90_days | tickets_per_100_orders | avg_first_response_hours | avg_resolution_hours | csat_avg_score | csat_response_count
-----------------------+---------------------------+----------------------+------------------------+--------------------------+----------------------+----------------+--------------------
Cragsley Outfitters    | Apparel & Footwear        |                  149 |                  10.88 |                    25.53 |                42.68 |           2.91 |                  44
Marrowby & Vane        | Apparel & Footwear        |                  101 |                  16.13 |                    78.95 |               136.59 |           2.68 |                  34
Grind Collective       | Food & Coffee             |                   70 |                  20.65 |                    59.03 |               103.56 |           2.41 |                  22
Petalcourt Beauty      | Beauty                    |                   64 |                  26.78 |                    47.05 |                63.37 |           2.87 |                  15
Fennel & Flint         | Home & Living             |                   46 |                  27.22 |                    18.51 |                44.05 |            3.9 |                  10
Kibblewick             | Pet Supplies              |                   43 |                  22.16 |                    25.52 |                55.62 |           2.38 |                  13
Oakhurst Tailoring     | Apparel & Footwear        |                   18 |                   22.5 |                    25.47 |                33.14 |            2.6 |                   5
Cinder Peak Sports Co. | Sports & Outdoor          |                   16 |                  15.53 |                      7.0 |                21.84 |           3.67 |                   3
Honeycomb Larder       | Food & Coffee             |                   12 |                   6.63 |                    21.92 |                32.65 |            2.4 |                   5
Coastline Interiors    | Home & Living             |                    9 |                  13.85 |                    12.46 |                15.72 |            4.0 |                   1
OfficeHub Nordic       | Office Supplies (B2B)     |                    6 |                  11.11 |                     6.51 |                14.61 |            4.0 |                   2
Velvet Ash             | Apparel & Footwear        |                    6 |                   12.0 |                    24.27 |                33.08 |            3.5 |                   2
Bytefield              | Consumer Electronics      |                    6 |                  54.55 |                    19.44 |                32.36 |           3.33 |                   3
Castell Fasteners      | Industrial Supplies (B2B) |                    5 |                   12.5 |                    33.49 |                 50.1 |           2.33 |                   3
Deskline Supplies      | Office Supplies (B2B)     |                    5 |                  21.74 |                    27.59 |                 31.9 |           2.67 |                   3
(15 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q062Merchant size and recent growth trend

It becomes benchmark query q275

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

spellingfetch first (2×)
determinismtie-breaker at the FETCH 15 cut: tenant_id is the group key
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
numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6) (2×)
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
made explicita selected t.col gets AS col, its standard name made explicit (10×)
placeholderseconds between (2×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
placeholderfetch first (2×)

The benchmark text, against the submitted SQL

WITH orders_window AS (  SELECT o.tenant_id, o.id AS order_id, o.currency_code, o.grand_total, o.placed_at  SELECT o.tenant_id AS tenant_id, o.id AS order_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at  FROM orders o  WHERE o.placed_at >= TIMESTAMP '2025-10-03'    AND o.placed_at < TIMESTAMP '2026-01-01'    AND o.status <> 'cancelled'),fx_rates AS (  SELECT quote_code, as_of, rate  FROM exchange_rates  WHERE base_code = 'EUR'),orders_eur AS (  SELECT ow.tenant_id, ow.order_id, ow.grand_total / fx.rate AS revenue_eur  SELECT ow.tenant_id AS tenant_id, ow.order_id AS order_id, CAST(ow.grand_total / fx.rate AS NUMERIC(18,6)) AS revenue_eur  FROM orders_window ow  JOIN fx_rates fx    ON fx.quote_code = ow.currency_code   AND fx.as_of = CAST(ow.placed_at AS date)),tenant_revenue AS (  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count  FROM orders_eur  GROUP BY tenant_id),top15 AS (  SELECT tenant_id, revenue_eur_total, order_count  FROM tenant_revenue  ORDER BY revenue_eur_total DESC  LIMIT 15  ORDER BY revenue_eur_total DESC NULLS FIRST, tenant_id NULLS LAST  {{fetch_first(15)}}),tickets_window AS (  SELECT t.tenant_id, t.id AS ticket_id,    EXTRACT(EPOCH FROM (t.first_response_at - t.created_at))/3600.0 AS first_response_hours,    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at))/3600.0 AS resolution_hours  SELECT t.tenant_id AS tenant_id, t.id AS ticket_id,    {{seconds_between(t.created_at, t.first_response_at)}}/3600.0 AS first_response_hours,    {{seconds_between(t.created_at, t.resolved_at)}}/3600.0 AS resolution_hours  FROM tickets t  JOIN top15 ON top15.tenant_id = t.tenant_id  WHERE t.created_at >= TIMESTAMP '2025-10-03'    AND t.created_at < TIMESTAMP '2026-01-01'),tickets_agg AS (  SELECT tenant_id,    COUNT(*) AS ticket_count,    AVG(first_response_hours) AS avg_first_response_hours,    AVG(resolution_hours) AS avg_resolution_hours  FROM tickets_window  GROUP BY tenant_id),csat_window AS (  SELECT c.tenant_id, c.score  SELECT c.tenant_id AS tenant_id, c.score AS score  FROM csat_responses c  JOIN top15 ON top15.tenant_id = c.tenant_id  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'    AND c.submitted_at < TIMESTAMP '2026-01-01'),csat_agg AS (  SELECT tenant_id, AVG(score) AS csat_avg_score, COUNT(*) AS csat_response_count  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS csat_avg_score, COUNT(*) AS csat_response_count  FROM csat_window  GROUP BY tenant_id)SELECT  tn.name AS merchant_name,  tn.industry,  tn.industry AS industry,  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,  ROUND(COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0), 2) AS tickets_per_100_orders,  ROUND(ta.avg_first_response_hours, 2) AS avg_first_response_hours,  ROUND(ta.avg_resolution_hours, 2) AS avg_resolution_hours,  ROUND(ca.csat_avg_score, 2) AS csat_avg_score,  COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0) AS tickets_per_100_orders,  ta.avg_first_response_hours AS avg_first_response_hours,  ta.avg_resolution_hours AS avg_resolution_hours,  ca.csat_avg_score AS csat_avg_score,  COALESCE(ca.csat_response_count, 0) AS csat_response_countFROM top15JOIN tenants tn ON tn.id = top15.tenant_idLEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_idLEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_idORDER BY tickets_last_90_days DESC, top15.tenant_id ASCLIMIT 50ORDER BY tickets_last_90_days DESC NULLS FIRST, top15.tenant_id ASC NULLS LAST{{fetch_first(50)}}

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

WITH orders_window AS (
  SELECT o.tenant_id AS tenant_id, o.id AS order_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id AS tenant_id, ow.order_id AS order_id, CAST(ow.grand_total / fx.rate AS NUMERIC(18,6)) AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC NULLS FIRST, tenant_id NULLS LAST
  FETCH FIRST 15 ROWS ONLY
),
tickets_window AS (
  SELECT t.tenant_id AS tenant_id, t.id AS ticket_id,
    CAST(EXTRACT(EPOCH FROM (t.first_response_at - t.created_at)) AS NUMERIC(18,6))/3600.0 AS first_response_hours,
    CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id AS tenant_id, c.score AS score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry AS industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0) AS tickets_per_100_orders,
  ta.avg_first_response_hours AS avg_first_response_hours,
  ta.avg_resolution_hours AS avg_resolution_hours,
  ca.csat_avg_score AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC NULLS FIRST, top15.tenant_id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY

WITH orders_window AS (
  SELECT o.tenant_id AS tenant_id, o.id AS order_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id AS tenant_id, ow.order_id AS order_id, CAST(ow.grand_total / fx.rate AS NUMERIC(18,6)) AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC NULLS FIRST, tenant_id NULLS LAST
  LIMIT 15
),
tickets_window AS (
  SELECT t.tenant_id AS tenant_id, t.id AS ticket_id,
    CAST(EXTRACT(EPOCH FROM (t.first_response_at - t.created_at)) AS NUMERIC(18,6))/3600.0 AS first_response_hours,
    CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id AS tenant_id, c.score AS score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry AS industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0) AS tickets_per_100_orders,
  ta.avg_first_response_hours AS avg_first_response_hours,
  ta.avg_resolution_hours AS avg_resolution_hours,
  ca.csat_avg_score AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC NULLS FIRST, top15.tenant_id ASC NULLS LAST
LIMIT 50

WITH orders_window AS (
  SELECT o.tenant_id AS tenant_id, o.id AS order_id, o.currency_code AS currency_code, o.grand_total AS grand_total, o.placed_at AS placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id AS tenant_id, ow.order_id AS order_id, CAST(ow.grand_total / fx.rate AS NUMERIC(18,6)) AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC NULLS FIRST, tenant_id NULLS LAST
  FETCH FIRST 15 ROWS ONLY
),
tickets_window AS (
  SELECT t.tenant_id AS tenant_id, t.id AS ticket_id,
    CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600.0 AS first_response_hours,
    CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id AS tenant_id, c.score AS score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(CAST(score AS NUMERIC(18,6))) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry AS industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0) AS tickets_per_100_orders,
  ta.avg_first_response_hours AS avg_first_response_hours,
  ta.avg_resolution_hours AS avg_resolution_hours,
  ca.csat_avg_score AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC NULLS FIRST, top15.tenant_id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY

Firebolt{ "errors": [ { "description": "Line 14, Column 82: 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": "d54b0218-3e8d-475d-a241-e48ab37d5ebb", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.grand_total Decimal(14, 4) Decimal64(size = 0), __table6.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

Scale factor 1000, c8a.24xlarge, after the last load. q275 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 Arjun Patel (analyst for the Head of Merchant Success):

Title: Support experience for our top 15 merchants, and whether it tracks with CSAT
Question: How much support volume do our top merchants generate, how fast is our response, and does that show up in their CSAT?
Grain: one row per merchant (tenant), limited to the top 15 by last-90-day revenue
Columns:
- merchant name
- industry
- tickets last 90 days
- tickets per 100 orders
- avg first response hours
- avg resolution hours
- csat avg score
- csat response count
Details: Top 15 ranked by last-90-day gross revenue EUR from orders (placed_at Oct 3 through Dec 31 2025, excluding cancelled, converted to EUR via exchange_rates), matching the earlier size and trend report's definition. For those tenants, from tickets (created_at in the same window): ticket count, avg first response hours (first_response_at minus created_at), avg resolution hours (resolved_at minus created_at); tickets per 100 orders is ticket count divided by the tenant's order count in the window, times 100; from csat_responses (submitted_at in the same window): avg score and response count. Order by tickets last 90 days descending, tie-break tenant_id ascending. Cap 50 rows (expect 15).

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

Grace calls dry_run

WITH orders_window AS (
  SELECT o.tenant_id, o.id AS order_id, o.currency_code, o.grand_total, o.placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id, ow.order_id, ow.grand_total / fx.rate AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC
  LIMIT 15
),
tickets_window AS (
  SELECT t.tenant_id, t.id AS ticket_id,
    EXTRACT(EPOCH FROM (t.first_response_at - t.created_at))/3600.0 AS first_response_hours,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id, c.score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(score) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  ROUND(COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0), 2) AS tickets_per_100_orders,
  ROUND(ta.avg_first_response_hours, 2) AS avg_first_response_hours,
  ROUND(ta.avg_resolution_hours, 2) AS avg_resolution_hours,
  ROUND(ca.csat_avg_score, 2) AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC, top15.tenant_id ASC
LIMIT 50

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, industry, tickets_last_90_days, tickets_per_100_orders, avg_first_response_hours, avg_resolution_hours, csat_avg_score, csat_response_count

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

Grace calls submit

WITH orders_window AS (
  SELECT o.tenant_id, o.id AS order_id, o.currency_code, o.grand_total, o.placed_at
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP '2025-10-03'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    AND o.status <> 'cancelled'
),
fx_rates AS (
  SELECT quote_code, as_of, rate
  FROM exchange_rates
  WHERE base_code = 'EUR'
),
orders_eur AS (
  SELECT ow.tenant_id, ow.order_id, ow.grand_total / fx.rate AS revenue_eur
  FROM orders_window ow
  JOIN fx_rates fx
    ON fx.quote_code = ow.currency_code
   AND fx.as_of = CAST(ow.placed_at AS date)
),
tenant_revenue AS (
  SELECT tenant_id, SUM(revenue_eur) AS revenue_eur_total, COUNT(*) AS order_count
  FROM orders_eur
  GROUP BY tenant_id
),
top15 AS (
  SELECT tenant_id, revenue_eur_total, order_count
  FROM tenant_revenue
  ORDER BY revenue_eur_total DESC
  LIMIT 15
),
tickets_window AS (
  SELECT t.tenant_id, t.id AS ticket_id,
    EXTRACT(EPOCH FROM (t.first_response_at - t.created_at))/3600.0 AS first_response_hours,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at))/3600.0 AS resolution_hours
  FROM tickets t
  JOIN top15 ON top15.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-10-03'
    AND t.created_at < TIMESTAMP '2026-01-01'
),
tickets_agg AS (
  SELECT tenant_id,
    COUNT(*) AS ticket_count,
    AVG(first_response_hours) AS avg_first_response_hours,
    AVG(resolution_hours) AS avg_resolution_hours
  FROM tickets_window
  GROUP BY tenant_id
),
csat_window AS (
  SELECT c.tenant_id, c.score
  FROM csat_responses c
  JOIN top15 ON top15.tenant_id = c.tenant_id
  WHERE c.submitted_at >= TIMESTAMP '2025-10-03'
    AND c.submitted_at < TIMESTAMP '2026-01-01'
),
csat_agg AS (
  SELECT tenant_id, AVG(score) AS csat_avg_score, COUNT(*) AS csat_response_count
  FROM csat_window
  GROUP BY tenant_id
)
SELECT
  tn.name AS merchant_name,
  tn.industry,
  COALESCE(ta.ticket_count, 0) AS tickets_last_90_days,
  ROUND(COALESCE(ta.ticket_count,0) * 100.0 / NULLIF(top15.order_count,0), 2) AS tickets_per_100_orders,
  ROUND(ta.avg_first_response_hours, 2) AS avg_first_response_hours,
  ROUND(ta.avg_resolution_hours, 2) AS avg_resolution_hours,
  ROUND(ca.csat_avg_score, 2) AS csat_avg_score,
  COALESCE(ca.csat_response_count, 0) AS csat_response_count
FROM top15
JOIN tenants tn ON tn.id = top15.tenant_id
LEFT JOIN tickets_agg ta ON ta.tenant_id = top15.tenant_id
LEFT JOIN csat_agg ca ON ca.tenant_id = top15.tenant_id
ORDER BY tickets_last_90_days DESC, top15.tenant_id ASC
LIMIT 50

The warehouse answers

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

GraceSubmitted. The report ranks the top 15 tenants by Oct 3–Dec 31 2025 EUR order revenue, then joins ticket volume/speed and CSAT metrics for that same window, with zero/NULL shown where a merchant had no tickets or no CSAT responses in the period.

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 (272 KB) · Arjun Patel's transcript (881 KB) · Samuel Whitcombe's transcript (261 KB) · the night's plan (4 KB) · the night's reports (14 KB) · the week's memo (4 KB)

Support experience for our top 15 merchants, and whether it tracks with CSAT

A business event lands on the Head of Merchant Success's desk

Samuel Whitcombe writes to Arjun Patel

Arjun Patel plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q275

Everything, as recorded