DB Olympics

Queries · q128 · VP Platform Engineering · week 1, night 2

2025 Job Failures by Job Type (Handler)

“Are job failures concentrated in a small number of job types/handlers rather than spread evenly across 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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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 2, request 2 of 3

    Big surprise from night 1: Report 1 only returned 3 rows (Oct/Nov/Dec 2025) even though I asked for the full year — job_runs apparently has no rows before 2025-10-01 (clean month boundary, growing row counts, no gaps), which looks like a rolling retention window rather than a data error. Report 3 similarly only returned Nov-Dec 2025 for system_health_checks. So "year-long trend" isn't deliverable from these raw tables as asked — I'll say so plainly in the memo and work within the window that actually exists (Oct-Dec for jobs, Nov-Dec for health checks), at finer (weekly) grain to still show a trend. Also, Report 2's tenant ranking was a dead end for Diego's #2: every one of the ~10 "large" tenants had exactly 2 failed runs and the rest had 0 — that's too uniform to be a real merchant-specific signal, and likely means failures are tied to a specific job type that happens to run for every large tenant, not to anything particular about the merchant. So tonight I pivot #2 from "which tenant" to "which job type," which better serves Diego's investment-priority question, and I still owe Diego the "late" half of #1 (my first report only measured run duration, not delay/lateness) and a first test of #3 (does health-check trouble line up with job failures, in the one window where both exist).

    2025 Job Failures by Job Type (Handler)
    Question
    Are job failures concentrated in a small number of job types/handlers rather than spread evenly across tenants?
    One row per
    job handler (job type), aggregated across all tenants whose jobs use that handler
    Columns
    • handler
    • total_runs
    • failed_runs
    • failure_rate_pct
    • distinct_tenants_run
    • distinct_tenants_failed
    • avg_duration_seconds_failed

  4. Bea 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 job_runs_filtered AS (
  SELECT
    jr.id AS id,
    jr.tenant_id AS tenant_id,
    jr.job_id AS job_id,
    jr.status AS status,
    jr.started_at AS started_at,
    jr.finished_at AS finished_at,
    j.handler AS handler
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE jr.queued_at >= DATE '2025-10-01'
    AND jr.queued_at < DATE '2026-01-01'
)
SELECT
  t0.handler AS handler,
  COALESCE(t1.total_runs, 0) AS total_runs,
  COALESCE(t2.failed_runs, 0) AS failed_runs,
  ROUND(100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0), 2) AS failure_rate_pct,
  COALESCE(t3.distinct_tenants_run, 0) AS distinct_tenants_run,
  COALESCE(t4.distinct_tenants_failed, 0) AS distinct_tenants_failed,
  ROUND(t5.avg_duration_seconds_failed, 1) AS avg_duration_seconds_failed
FROM (SELECT DISTINCT handler FROM job_runs_filtered) t0
LEFT JOIN (
  SELECT handler, COUNT(*) AS total_runs
  FROM job_runs_filtered
  GROUP BY handler
) t1 ON t1.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(*) AS failed_runs
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t2 ON t2.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_run
  FROM job_runs_filtered
  GROUP BY handler
) t3 ON t3.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t4 ON t4.handler = t0.handler
LEFT JOIN (
  SELECT handler, AVG(EXTRACT(EPOCH FROM (finished_at - started_at))) AS avg_duration_seconds_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
    AND started_at IS NOT NULL
    AND finished_at IS NOT NULL
  GROUP BY handler
) t5 ON t5.handler = t0.handler
ORDER BY failed_runs DESC, t0.handler ASC

    “Query compiles and returns the requested columns. Submitting.”

    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 job_runs (joined to jobs for handler) over Q4 2025 — the only window the request specifies and that the data covers — computing total/failed runs, failure rate, distinct tenants run/failed, and average failed-run duration per handler, ordered by failed_runs descending.”

  5. The report comes back overnight

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

    handler             | total_runs | failed_runs | failure_rate_pct | distinct_tenants_run | distinct_tenants_failed | avg_duration_seconds_failed
--------------------+------------+-------------+------------------+----------------------+-------------------------+----------------------------
carts.remind        |      21867 |          20 |             0.09 |                   10 |                      10 |                         1.7
audit.archive       |        220 |           0 |              0.0 |                  111 |                       0 |                        NULL
loyalty.release     |       2580 |           0 |              0.0 |                   29 |                       0 |                        NULL
payouts.reconcile   |      10001 |           0 |              0.0 |                  113 |                       0 |                        NULL
search.reindex      |       1503 |           0 |              0.0 |                  113 |                       0 |                        NULL
segments.refresh    |       9987 |           0 |              0.0 |                  113 |                       0 |                        NULL
sitemap.build       |       1449 |           0 |              0.0 |                  112 |                       0 |                        NULL
subscriptions.renew |       1640 |           0 |              0.0 |                   18 |                       0 |                        NULL
(8 rows)

  6. What came of it

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

    Lucas Moreau's memo to Diego Morales at the end of the week:

    • 2. The one incident: the carts.remind job, week of Nov 24, hit exactly 10 tenants. All 20 failures in the quarter (0.09% of that handler's 21,867 runs) happened in a single week, in a single job type, affecting 10 of ~120 tenants — every other of the 8 job handlers had zero failures. Source: "2025 Job Failures by Job Type (Handler)." This is a contained, narrow incident, not a systemic job-reliability problem.

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q128

    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
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • placeholderseconds between
    • 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 job_runs_filtered AS (  SELECT    jr.id AS id,    jr.tenant_id AS tenant_id,    jr.job_id AS job_id,    jr.status AS status,    jr.started_at AS started_at,    jr.finished_at AS finished_at,    j.handler AS handler  FROM job_runs jr  JOIN jobs j ON j.id = jr.job_id  WHERE jr.queued_at >= DATE '2025-10-01'    AND jr.queued_at < DATE '2026-01-01')SELECT  t0.handler AS handler,  COALESCE(t1.total_runs, 0) AS total_runs,  COALESCE(t2.failed_runs, 0) AS failed_runs,  ROUND(100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0), 2) AS failure_rate_pct,  100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0) AS failure_rate_pct,  COALESCE(t3.distinct_tenants_run, 0) AS distinct_tenants_run,  COALESCE(t4.distinct_tenants_failed, 0) AS distinct_tenants_failed,  ROUND(t5.avg_duration_seconds_failed, 1) AS avg_duration_seconds_failed  t5.avg_duration_seconds_failed AS avg_duration_seconds_failedFROM (SELECT DISTINCT handler FROM job_runs_filtered) t0LEFT JOIN (  SELECT handler, COUNT(*) AS total_runs  FROM job_runs_filtered  GROUP BY handler) t1 ON t1.handler = t0.handlerLEFT JOIN (  SELECT handler, COUNT(*) AS failed_runs  FROM job_runs_filtered  WHERE status = 'failed'  GROUP BY handler) t2 ON t2.handler = t0.handlerLEFT JOIN (  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_run  FROM job_runs_filtered  GROUP BY handler) t3 ON t3.handler = t0.handlerLEFT JOIN (  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_failed  FROM job_runs_filtered  WHERE status = 'failed'  GROUP BY handler) t4 ON t4.handler = t0.handlerLEFT JOIN (  SELECT handler, AVG(EXTRACT(EPOCH FROM (finished_at - started_at))) AS avg_duration_seconds_failed  SELECT handler, AVG({{seconds_between(started_at, finished_at)}}) AS avg_duration_seconds_failed  FROM job_runs_filtered  WHERE status = 'failed'    AND started_at IS NOT NULL    AND finished_at IS NOT NULL  GROUP BY handler) t5 ON t5.handler = t0.handlerORDER BY failed_runs DESC, t0.handler ASCORDER BY failed_runs DESC NULLS FIRST, t0.handler ASC NULLS LAST

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

    WITH job_runs_filtered AS (
  SELECT
    jr.id AS id,
    jr.tenant_id AS tenant_id,
    jr.job_id AS job_id,
    jr.status AS status,
    jr.started_at AS started_at,
    jr.finished_at AS finished_at,
    j.handler AS handler
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE jr.queued_at >= DATE '2025-10-01'
    AND jr.queued_at < DATE '2026-01-01'
)
SELECT
  t0.handler AS handler,
  COALESCE(t1.total_runs, 0) AS total_runs,
  COALESCE(t2.failed_runs, 0) AS failed_runs,
  100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0) AS failure_rate_pct,
  COALESCE(t3.distinct_tenants_run, 0) AS distinct_tenants_run,
  COALESCE(t4.distinct_tenants_failed, 0) AS distinct_tenants_failed,
  t5.avg_duration_seconds_failed AS avg_duration_seconds_failed
FROM (SELECT DISTINCT handler FROM job_runs_filtered) t0
LEFT JOIN (
  SELECT handler, COUNT(*) AS total_runs
  FROM job_runs_filtered
  GROUP BY handler
) t1 ON t1.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(*) AS failed_runs
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t2 ON t2.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_run
  FROM job_runs_filtered
  GROUP BY handler
) t3 ON t3.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t4 ON t4.handler = t0.handler
LEFT JOIN (
  SELECT handler, AVG(CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6))) AS avg_duration_seconds_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
    AND started_at IS NOT NULL
    AND finished_at IS NOT NULL
  GROUP BY handler
) t5 ON t5.handler = t0.handler
ORDER BY failed_runs DESC NULLS FIRST, t0.handler ASC NULLS LAST
    1 ms10 ms100 ms1 sHyper7.9 msDuckDB19.3 msGizmoSQL27.9 msclickhouse_brew203 msFirebolt53.9 msDataFusionwrong resultClickHouse114 ms
    • DataFusionrow 1: col 7: "2.51435" vs "2.2839000000"

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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Lucas Moreau (analyst for the VP Platform Engineering):

Title: 2025 Job Failures by Job Type (Handler)
Question: Are job failures concentrated in a small number of job types/handlers rather than spread evenly across tenants?
Grain: one row per job handler (job type), aggregated across all tenants whose jobs use that handler
Columns:
- handler
- total_runs
- failed_runs
- failure_rate_pct
- distinct_tenants_run
- distinct_tenants_failed
- avg_duration_seconds_failed
Details: Tables job_runs joined to jobs on job_id to get jobs.handler (the shared job-type identifier, since jobs.name/jobs table rows are per-tenant but handler denotes the same underlying job type across tenants). Filter queued_at >= 2025-10-01 and < 2026-01-01 (the available window). Group by handler. distinct_tenants_run = count distinct tenant_id among all runs for that handler; distinct_tenants_failed = count distinct tenant_id among failed runs for that handler. avg_duration_seconds_failed computed over failed runs only where started_at and finished_at are both set. Order by failed_runs descending, tie-break by handler ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH job_runs_filtered AS (
  SELECT
    jr.id AS id,
    jr.tenant_id AS tenant_id,
    jr.job_id AS job_id,
    jr.status AS status,
    jr.started_at AS started_at,
    jr.finished_at AS finished_at,
    j.handler AS handler
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE jr.queued_at >= DATE '2025-10-01'
    AND jr.queued_at < DATE '2026-01-01'
)
SELECT
  t0.handler AS handler,
  COALESCE(t1.total_runs, 0) AS total_runs,
  COALESCE(t2.failed_runs, 0) AS failed_runs,
  ROUND(100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0), 2) AS failure_rate_pct,
  COALESCE(t3.distinct_tenants_run, 0) AS distinct_tenants_run,
  COALESCE(t4.distinct_tenants_failed, 0) AS distinct_tenants_failed,
  ROUND(t5.avg_duration_seconds_failed, 1) AS avg_duration_seconds_failed
FROM (SELECT DISTINCT handler FROM job_runs_filtered) t0
LEFT JOIN (
  SELECT handler, COUNT(*) AS total_runs
  FROM job_runs_filtered
  GROUP BY handler
) t1 ON t1.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(*) AS failed_runs
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t2 ON t2.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_run
  FROM job_runs_filtered
  GROUP BY handler
) t3 ON t3.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t4 ON t4.handler = t0.handler
LEFT JOIN (
  SELECT handler, AVG(EXTRACT(EPOCH FROM (finished_at - started_at))) AS avg_duration_seconds_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
    AND started_at IS NOT NULL
    AND finished_at IS NOT NULL
  GROUP BY handler
) t5 ON t5.handler = t0.handler
ORDER BY failed_runs DESC, t0.handler ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: handler, total_runs, failed_runs, failure_rate_pct, distinct_tenants_run, distinct_tenants_failed, avg_duration_seconds_failed
    • BeaQuery compiles and returns the requested columns. Submitting.
    • Bea calls submit
      WITH job_runs_filtered AS (
  SELECT
    jr.id AS id,
    jr.tenant_id AS tenant_id,
    jr.job_id AS job_id,
    jr.status AS status,
    jr.started_at AS started_at,
    jr.finished_at AS finished_at,
    j.handler AS handler
  FROM job_runs jr
  JOIN jobs j ON j.id = jr.job_id
  WHERE jr.queued_at >= DATE '2025-10-01'
    AND jr.queued_at < DATE '2026-01-01'
)
SELECT
  t0.handler AS handler,
  COALESCE(t1.total_runs, 0) AS total_runs,
  COALESCE(t2.failed_runs, 0) AS failed_runs,
  ROUND(100.0 * COALESCE(t2.failed_runs, 0) / NULLIF(COALESCE(t1.total_runs, 0), 0), 2) AS failure_rate_pct,
  COALESCE(t3.distinct_tenants_run, 0) AS distinct_tenants_run,
  COALESCE(t4.distinct_tenants_failed, 0) AS distinct_tenants_failed,
  ROUND(t5.avg_duration_seconds_failed, 1) AS avg_duration_seconds_failed
FROM (SELECT DISTINCT handler FROM job_runs_filtered) t0
LEFT JOIN (
  SELECT handler, COUNT(*) AS total_runs
  FROM job_runs_filtered
  GROUP BY handler
) t1 ON t1.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(*) AS failed_runs
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t2 ON t2.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_run
  FROM job_runs_filtered
  GROUP BY handler
) t3 ON t3.handler = t0.handler
LEFT JOIN (
  SELECT handler, COUNT(DISTINCT tenant_id) AS distinct_tenants_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
  GROUP BY handler
) t4 ON t4.handler = t0.handler
LEFT JOIN (
  SELECT handler, AVG(EXTRACT(EPOCH FROM (finished_at - started_at))) AS avg_duration_seconds_failed
  FROM job_runs_filtered
  WHERE status = 'failed'
    AND started_at IS NOT NULL
    AND finished_at IS NOT NULL
  GROUP BY handler
) t5 ON t5.handler = t0.handler
ORDER BY failed_runs DESC, t0.handler ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report groups job_runs (joined to jobs for `handler`) over Q4 2025 — the only window the request specifies and that the data covers — computing total/failed runs, failure rate, distinct tenants run/failed, and average failed-run duration per handler, ordered by failed_runs descending.

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

    Bea's transcript (265 KB) · Lucas Moreau's transcript (813 KB) · Diego Morales's transcript (264 KB) · the night's plan (5 KB) · the night's reports (6 KB) · the week's memo (4 KB)