DB Olympics

Queries · q131 · VP Platform Engineering · week 1, night 3

Support Ticket Volume Around the Nov 2025 Carts.remind Incident

“Did support ticket volume spike for the 10 affected tenants around the week of the carts.remind failures, compared to other tenants?”

Manager · asked the questionDiego MoralesVP Platform Engineering. Former reliability lead at a payments company. Owns uptime and the integrations merchants depend on.
Analyst · planned the reportLucas MoreauPragmatic: answers the question asked with the simplest reliable report.
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

  1. A business event lands on the VP Platform Engineering's desk

    Week 1; shared with Samuel Whitcombe, Head of Merchant Success

    Leadership wants a review of platform reliability in 2025: failed and slow background jobs, health check failures, rate limiting, webhook delivery problems, and which merchants felt them.

    One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

  2. Diego Morales writes to Lucas Moreau

    The manager, at the start of week 1

    Subject: 2025 platform reliability review — what I need by Friday

    Lucas,

    Leadership wants a year-in-review on platform reliability for 2025, and I'm leading it jointly with Samuel Whitcombe (Merchant Success). The deliverable is a memo that tells leadership how reliable the platform actually was last year, where it hurt merchants most, and what that implies for our 2026 investment priorities (reliability engineering headcount, which integrations/carriers to fix or replace, whether we need stricter rate limiting or SLAs). Samuel's side will map our findings to specific merchant relationships and churn risk, so where possible, surface things in a way that can be tied back to individual merchants rather than only platform-wide averages.

    Given you only get a handful of report slots a night, here's what matters most to me, roughly in priority order:

    1. How often did background work actually fail or run late in 2025, and is it getting better or worse? I want a view across the year (not just a single average) so we can see trend, not just a snapshot.
    2. When something broke, which merchants bore the brunt of it? I'd rather see a short list of the merchants hit hardest by job failures, slow jobs, or delivery problems than a platform-wide blended number — that's what Samuel needs to go have conversations.
    3. Were there periods where our own health checks told us something was wrong, and did that line up with the times merchants complained or things failed? I want to know if our monitoring is actually catching problems early or if we're flying blind.
    4. Are merchants getting throttled by our rate limits in ways that look like it's hurting their business, rather than protecting us? I want to know if this is a tuning problem or a capacity problem.
    5. How reliable are our webhook deliveries to merchants' own systems, and are there particular integrations or providers that are chronically troublesome? This feeds a build-vs-fix-vs-drop decision on a couple of integration partners.

    If you have to cut scope to fit your nightly slots, protect #1 and #2 — those are what the memo absolutely must answer. The rest can wait or be addressed at a higher level if time runs short.

    Let me know if anything above is ambiguous before you spend a slot on it.

    Diego

    A note from the Head of Merchant Success, Samuel Whitcombe

    Subject: Platform reliability review — what I need from my side

    Hi Lucas,

    As you pull together the 2025 reliability picture, here's what matters from the merchant-health side:

    1. Did our largest merchants actually feel this? For our top-tier accounts (the ones I sit QBRs with this month), can we tell whether job failures, health check issues, rate limiting or webhook problems clustered on their tenants more than the long tail — and whether it tracked with order volume or came out of nowhere?
    2. Did reliability issues coincide with commercial pain? I'd like to know if the merchants hit hardest by these issues also show signs of strain elsewhere — support tickets spiking, churn or downgrades, slower sales — so I can tell if this is a cause or just a correlation worth flagging.
    3. Any repeat offenders going into 2026? Were there a handful of merchants who got hit repeatedly through the year rather than a one-off bad week platform-wide? I want to walk into January reviews knowing who to proactively address before they raise it.

    Happy to flag specific merchant names if that helps scope the analysis.

    Best, Samuel

  3. Lucas Moreau plans the night

    The analyst · week 1, night 3, request 2 of 3

    Night 2 reframed the whole job-reliability story: queue delay and run duration are flat and tiny every single week (no "lateness" problem exists at all), and ALL 20 failures in the entire Oct-Dec window happened in one week (2025-11-24) in one handler (carts.remind), hitting exactly 10 tenants. So this isn't a chronic reliability problem - it's one contained one-week incident tied to the abandoned-cart-reminder job. Report 3 showed something genuinely useful: the health-check unhealthy rate spiked (2.44% vs a ~1% baseline) in that exact same week, suggesting monitoring did catch it. Given this, tonight I pivot to what Samuel needs about those 10 tenants specifically (are they top-tier/large accounts, did it track with order volume, did it coincide with ticket pain) since that's now answerable precisely rather than generically, and I use my third slot to open up webhook delivery reliability (Diego's #5), which I haven't touched yet and which is a more directly merchant-facing failure mode than internal jobs turned out to be.

    Support Ticket Volume Around the Nov 2025 Carts.remind Incident
    Question
    Did support ticket volume spike for the 10 affected tenants around the week of the carts.remind failures, compared to other tenants?
    One row per
    ISO week, Nov 1 - Dec 31 2025, split into affected vs other tenants
    Columns
    • week_start
    • tickets_affected_tenants
    • avg_tickets_per_affected_tenant
    • tickets_other_tenants
    • avg_tickets_per_other_tenant

  4. 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 affected AS (
  SELECT DISTINCT jr.tenant_id
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE j.handler = 'carts.remind'
    AND jr.status = 'failed'
    AND jr.queued_at >= TIMESTAMP '2025-11-01'
    AND jr.queued_at < TIMESTAMP '2025-12-01'
),
tenant_counts AS (
  SELECT COUNT(*) AS total_tenants
  FROM tenants
  WHERE status IN ('trial','active','past_due','suspended','churned')
),
ticket_weeks AS (
  SELECT cd.week_start,
         t.tenant_id,
         CASE WHEN t.tenant_id IN (SELECT tenant_id FROM affected) THEN 1 ELSE 0 END AS is_affected
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS DATE)
  WHERE t.created_at >= TIMESTAMP '2025-11-01' AND t.created_at < TIMESTAMP '2026-01-01'
)
SELECT
  week_start,
  COUNT(*) FILTER (WHERE is_affected = 1) AS tickets_affected_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 1) / 10.0, 2) AS avg_tickets_per_affected_tenant,
  COUNT(*) FILTER (WHERE is_affected = 0) AS tickets_other_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 0) / (SELECT (total_tenants - 10) FROM tenant_counts), 2) AS avg_tickets_per_other_tenant
FROM ticket_weeks
GROUP BY week_start
ORDER BY week_start

    “Query compiles with 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 groups ticket counts by ISO week (Nov 1 – Dec 31, 2025) split into the 10 tenants affected by the carts.remind job failures versus all other tenants, with per-tenant averages as requested.”

  5. The report comes back overnight

    As Lucas Moreau saw it, from the small dataset the agents worked on

    week_start | tickets_affected_tenants | avg_tickets_per_affected_tenant | tickets_other_tenants | avg_tickets_per_other_tenant
-----------+--------------------------+---------------------------------+-----------------------+-----------------------------
2025-10-27 |                       10 |                             1.0 |                     6 |                         0.05
2025-11-03 |                       22 |                             2.2 |                    26 |                         0.24
2025-11-10 |                       31 |                             3.1 |                    26 |                         0.24
2025-11-17 |                       54 |                             5.4 |                    43 |                         0.39
2025-11-24 |                       58 |                             5.8 |                    40 |                         0.36
2025-12-01 |                      113 |                            11.3 |                    79 |                         0.72
2025-12-08 |                       87 |                             8.7 |                    81 |                         0.74
2025-12-15 |                       31 |                             3.1 |                    27 |                         0.25
2025-12-22 |                       35 |                             3.5 |                    14 |                         0.13
2025-12-29 |                       11 |                             1.1 |                     7 |                         0.06
(10 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q131

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; 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 (3×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
    The benchmark text, against the submitted SQL
    WITH affected AS (  SELECT DISTINCT jr.tenant_id  SELECT DISTINCT jr.tenant_id AS tenant_id  FROM job_runs jr  JOIN jobs j ON j.id = jr.job_id  WHERE j.handler = 'carts.remind'    AND jr.status = 'failed'    AND jr.queued_at >= TIMESTAMP '2025-11-01'    AND jr.queued_at < TIMESTAMP '2025-12-01'),tenant_counts AS (  SELECT COUNT(*) AS total_tenants  FROM tenants  WHERE status IN ('trial','active','past_due','suspended','churned')),ticket_weeks AS (  SELECT cd.week_start,         t.tenant_id,  SELECT cd.week_start AS week_start,         t.tenant_id AS tenant_id,         CASE WHEN t.tenant_id IN (SELECT tenant_id FROM affected) THEN 1 ELSE 0 END AS is_affected  FROM tickets t  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS DATE)  WHERE t.created_at >= TIMESTAMP '2025-11-01' AND t.created_at < TIMESTAMP '2026-01-01')SELECT  week_start,  COUNT(*) FILTER (WHERE is_affected = 1) AS tickets_affected_tenants,  ROUND(COUNT(*) FILTER (WHERE is_affected = 1) / 10.0, 2) AS avg_tickets_per_affected_tenant,  COUNT(*) FILTER (WHERE is_affected = 1) / 10.0 AS avg_tickets_per_affected_tenant,  COUNT(*) FILTER (WHERE is_affected = 0) AS tickets_other_tenants,  ROUND(COUNT(*) FILTER (WHERE is_affected = 0) / (SELECT (total_tenants - 10) FROM tenant_counts), 2) AS avg_tickets_per_other_tenant  CAST(COUNT(*) FILTER (WHERE is_affected = 0) AS NUMERIC(18,6)) / (SELECT (total_tenants - 10) FROM tenant_counts) AS avg_tickets_per_other_tenantFROM ticket_weeksGROUP BY week_startORDER BY week_startORDER BY week_start NULLS LAST

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

    WITH affected AS (
  SELECT DISTINCT jr.tenant_id AS tenant_id
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE j.handler = 'carts.remind'
    AND jr.status = 'failed'
    AND jr.queued_at >= TIMESTAMP '2025-11-01'
    AND jr.queued_at < TIMESTAMP '2025-12-01'
),
tenant_counts AS (
  SELECT COUNT(*) AS total_tenants
  FROM tenants
  WHERE status IN ('trial','active','past_due','suspended','churned')
),
ticket_weeks AS (
  SELECT cd.week_start AS week_start,
         t.tenant_id AS tenant_id,
         CASE WHEN t.tenant_id IN (SELECT tenant_id FROM affected) THEN 1 ELSE 0 END AS is_affected
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS DATE)
  WHERE t.created_at >= TIMESTAMP '2025-11-01' AND t.created_at < TIMESTAMP '2026-01-01'
)
SELECT
  week_start,
  COUNT(*) FILTER (WHERE is_affected = 1) AS tickets_affected_tenants,
  COUNT(*) FILTER (WHERE is_affected = 1) / 10.0 AS avg_tickets_per_affected_tenant,
  COUNT(*) FILTER (WHERE is_affected = 0) AS tickets_other_tenants,
  CAST(COUNT(*) FILTER (WHERE is_affected = 0) AS NUMERIC(18,6)) / (SELECT (total_tenants - 10) FROM tenant_counts) AS avg_tickets_per_other_tenant
FROM ticket_weeks
GROUP BY week_start
ORDER BY week_start NULLS LAST
    1 ms10 ms100 ms1 sHyper6.9 msDuckDB6.7 msGizmoSQL16.3 msclickhouse_brew108 msFirebolt45.5 msDataFusionerrorClickHouse15.0 ms
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression InSubquery(InSubquery { expr: Column(Column { relation: Some(Bare { table: "t" }), name: "tenant_id" }), subquery: <subquery>, negated: false })

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

  8. 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 Lucas Moreau (analyst for the VP Platform Engineering):

Title: Support Ticket Volume Around the Nov 2025 Carts.remind Incident
Question: Did support ticket volume spike for the 10 affected tenants around the week of the carts.remind failures, compared to other tenants?
Grain: one row per ISO week, Nov 1 - Dec 31 2025, split into affected vs other tenants
Columns:
- week_start
- tickets_affected_tenants
- avg_tickets_per_affected_tenant
- tickets_other_tenants
- avg_tickets_per_other_tenant
Details: Table tickets, filtered to created_at >= 2025-11-01 and < 2026-01-01, grouped by ISO week (calendar_days.week_start). 'Affected' tenants are the 10 identified via job_runs joined to jobs where handler='carts.remind', status='failed', queued_at in November 2025 (same definition as the prior request); 'other' is every other tenant with status in (trial, active, past_due, suspended, churned) -- i.e. all remaining tenants. avg_tickets_per_affected_tenant = tickets_affected_tenants / 10; avg_tickets_per_other_tenant = tickets_other_tenants / (total tenant count - 10). Order by week_start ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      WITH affected AS (
  SELECT DISTINCT jr.tenant_id
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE j.handler = 'carts.remind'
    AND jr.status = 'failed'
    AND jr.queued_at >= TIMESTAMP '2025-11-01'
    AND jr.queued_at < TIMESTAMP '2025-12-01'
),
tenant_counts AS (
  SELECT COUNT(*) AS total_tenants
  FROM tenants
  WHERE status IN ('trial','active','past_due','suspended','churned')
),
ticket_weeks AS (
  SELECT cd.week_start,
         t.tenant_id,
         CASE WHEN t.tenant_id IN (SELECT tenant_id FROM affected) THEN 1 ELSE 0 END AS is_affected
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS DATE)
  WHERE t.created_at >= TIMESTAMP '2025-11-01' AND t.created_at < TIMESTAMP '2026-01-01'
)
SELECT
  week_start,
  COUNT(*) FILTER (WHERE is_affected = 1) AS tickets_affected_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 1) / 10.0, 2) AS avg_tickets_per_affected_tenant,
  COUNT(*) FILTER (WHERE is_affected = 0) AS tickets_other_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 0) / (SELECT (total_tenants - 10) FROM tenant_counts), 2) AS avg_tickets_per_other_tenant
FROM ticket_weeks
GROUP BY week_start
ORDER BY week_start
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: week_start, tickets_affected_tenants, avg_tickets_per_affected_tenant, tickets_other_tenants, avg_tickets_per_other_tenant
    • OmarQuery compiles with the expected columns. Submitting now.
    • Omar calls submit
      WITH affected AS (
  SELECT DISTINCT jr.tenant_id
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE j.handler = 'carts.remind'
    AND jr.status = 'failed'
    AND jr.queued_at >= TIMESTAMP '2025-11-01'
    AND jr.queued_at < TIMESTAMP '2025-12-01'
),
tenant_counts AS (
  SELECT COUNT(*) AS total_tenants
  FROM tenants
  WHERE status IN ('trial','active','past_due','suspended','churned')
),
ticket_weeks AS (
  SELECT cd.week_start,
         t.tenant_id,
         CASE WHEN t.tenant_id IN (SELECT tenant_id FROM affected) THEN 1 ELSE 0 END AS is_affected
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS DATE)
  WHERE t.created_at >= TIMESTAMP '2025-11-01' AND t.created_at < TIMESTAMP '2026-01-01'
)
SELECT
  week_start,
  COUNT(*) FILTER (WHERE is_affected = 1) AS tickets_affected_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 1) / 10.0, 2) AS avg_tickets_per_affected_tenant,
  COUNT(*) FILTER (WHERE is_affected = 0) AS tickets_other_tenants,
  ROUND(COUNT(*) FILTER (WHERE is_affected = 0) / (SELECT (total_tenants - 10) FROM tenant_counts), 2) AS avg_tickets_per_other_tenant
FROM ticket_weeks
GROUP BY week_start
ORDER BY week_start
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. The report groups ticket counts by ISO week (Nov 1 – Dec 31, 2025) split into the 10 tenants affected by the `carts.remind` job failures versus all other tenants, with per-tenant averages as requested.

    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 (263 KB) · Lucas Moreau's transcript (813 KB) · Diego Morales's transcript (264 KB) · the night's plan (4 KB) · the night's reports (9 KB) · the week's memo (4 KB)