Queries · q125 · VP Platform Engineering · week 1, night 1

Merchants Hit Hardest by Job Failures, 2025

“Which merchants had the most background job failures in 2025, and does it track with order volume?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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 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
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
Lucas Moreau plans the night
The analyst · week 1, night 1, request 2 of 3
Night 1: focusing on Diego's top two priorities (job failure/lateness trend over the year, and which merchants got hit hardest), plus a first look at health-check reliability trend.
Merchants Hit Hardest by Job Failures, 2025
Question
Which merchants had the most background job failures in 2025, and does it track with order volume?
One row per
tenant
Columns
- tenant_name
- total_job_runs_2025
- failed_runs_2025
- failure_rate_pct
- avg_duration_seconds_failed
- total_orders_2025
- failed_runs_per_1000_orders

Felix 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)

SELECT t.name as tenant_name,
  jr.total_job_runs_2025,
  jr.failed_runs_2025,
  round(jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025, 1) as failure_rate_pct,
  round(jr.avg_duration_seconds_failed, 1) as avg_duration_seconds_failed,
  coalesce(o.total_orders_2025, 0) as total_orders_2025,
  case when coalesce(o.total_orders_2025, 0) = 0 then null
       else round(jr.failed_runs_2025 / o.total_orders_2025 * 1000.0, 1)
  end as failed_runs_per_1000_orders
FROM tenants t
JOIN (
  SELECT tenant_id,
    count(*) as total_job_runs_2025,
    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,
    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed
  FROM job_runs
  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) jr ON jr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id, count(*) as total_orders_2025
  FROM orders
  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) o ON o.tenant_id = t.id
ORDER BY jr.failed_runs_2025 DESC, t.name ASC
LIMIT 20

“Query compiles cleanly with the expected 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 returns the top 20 tenants by 2025 failed job runs, with total/failed job run counts, failure rate %, average failure duration (seconds), 2025 order volume (0 if none), and failed runs per 1,000 orders (blank for zero-order tenants) — ordered by failed runs descending, tenant name as tie-break.”

The report comes back overnight

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

tenant_name                   | total_job_runs_2025 | failed_runs_2025 | failure_rate_pct | avg_duration_seconds_failed | total_orders_2025 | failed_runs_per_1000_orders
------------------------------+---------------------+------------------+------------------+-----------------------------+-------------------+----------------------------
Ampfeld Elektronik            |                2397 |                2 |              0.1 |                         1.1 |               449 |                         4.5
Brennquell Kaffee             |                2490 |                2 |              0.1 |                         2.3 |              4195 |                         0.5
Cinder Peak Sports Co.        |                2485 |                2 |              0.1 |                         1.5 |               338 |                         5.9
Cragsley Outfitters           |                2397 |                2 |              0.1 |                         2.8 |              3635 |                         0.6
Fennel & Flint                |                2397 |                2 |              0.1 |                         1.2 |               389 |                         5.1
Gratlinie Outdoor             |                2400 |                2 |              0.1 |                         1.2 |               232 |                         8.6
Kibblewick                    |                2482 |                2 |              0.1 |                         2.3 |               672 |                         3.0
Marrowby & Vane               |                2489 |                2 |              0.1 |                         1.9 |              1758 |                         1.1
Petalcourt Beauty             |                2395 |                2 |              0.1 |                         1.0 |               706 |                         2.8
Studio Vrij                   |                2489 |                2 |              0.1 |                         1.6 |               631 |                         3.2
Alpine Loft                   |                 209 |                0 |              0.0 |                        NULL |                 2 |                         0.0
Ampere & Co.                  |                 301 |                0 |              0.0 |                        NULL |                62 |                         0.0
Atelier Nouveau               |                 300 |                0 |              0.0 |                        NULL |                64 |                         0.0
Ballycorran Industrial Supply |                 209 |                0 |              0.0 |                        NULL |               344 |                         0.0
Barkley & Co.                 |                 210 |                0 |              0.0 |                        NULL |                38 |                         0.0
Basecamp Nord                 |                 299 |                0 |              0.0 |                        NULL |                43 |                         0.0
Bijoux Lune                   |                 213 |                0 |              0.0 |                        NULL |                21 |                         0.0
Brewline Coffee               |                 308 |                0 |              0.0 |                        NULL |                42 |                         0.0
Brick & Bobbin                |                 209 |                0 |              0.0 |                        NULL |                27 |                         0.0
Buchstabe                     |                 305 |                0 |              0.0 |                        NULL |                35 |                         0.0
(20 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q1282025 Job Failures by Job Type (Handler)
- q130Tenant Size vs Carts.remind Job Failure Exposure, 2025

It becomes benchmark query q125

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

spellingfetch first
determinismtie-breaker: tenant names are not unique
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; multiplied before the division, so an engine that keeps few decimals in the quotient does not scale its rounding up
made explicita selected t.col gets AS col, its standard name made explicit (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 (3×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT t.name as tenant_name,  jr.total_job_runs_2025,  jr.failed_runs_2025,  round(jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025, 1) as failure_rate_pct,  round(jr.avg_duration_seconds_failed, 1) as avg_duration_seconds_failed,  jr.total_job_runs_2025 AS total_job_runs_2025,  jr.failed_runs_2025 AS failed_runs_2025,  jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025 as failure_rate_pct,  jr.avg_duration_seconds_failed as avg_duration_seconds_failed,  coalesce(o.total_orders_2025, 0) as total_orders_2025,  case when coalesce(o.total_orders_2025, 0) = 0 then null       else round(jr.failed_runs_2025 / o.total_orders_2025 * 1000.0, 1)       else cast(jr.failed_runs_2025 as numeric(18,6)) * 1000.0 / o.total_orders_2025  end as failed_runs_per_1000_ordersFROM tenants tJOIN (  SELECT tenant_id,    count(*) as total_job_runs_2025,    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed  FROM job_runs  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'  GROUP BY tenant_id) jr ON jr.tenant_id = t.idLEFT JOIN (  SELECT tenant_id, count(*) as total_orders_2025  FROM orders  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'  GROUP BY tenant_id) o ON o.tenant_id = t.idORDER BY jr.failed_runs_2025 DESC, t.name ASCLIMIT 20ORDER BY jr.failed_runs_2025 DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(20)}}

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

SELECT t.name as tenant_name,
  jr.total_job_runs_2025 AS total_job_runs_2025,
  jr.failed_runs_2025 AS failed_runs_2025,
  jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025 as failure_rate_pct,
  jr.avg_duration_seconds_failed as avg_duration_seconds_failed,
  coalesce(o.total_orders_2025, 0) as total_orders_2025,
  case when coalesce(o.total_orders_2025, 0) = 0 then null
       else cast(jr.failed_runs_2025 as numeric(18,6)) * 1000.0 / o.total_orders_2025
  end as failed_runs_per_1000_orders
FROM tenants t
JOIN (
  SELECT tenant_id,
    count(*) as total_job_runs_2025,
    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,
    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed
  FROM job_runs
  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) jr ON jr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id, count(*) as total_orders_2025
  FROM orders
  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) o ON o.tenant_id = t.id
ORDER BY jr.failed_runs_2025 DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST
FETCH FIRST 20 ROWS ONLY

SELECT t.name as tenant_name,
  jr.total_job_runs_2025 AS total_job_runs_2025,
  jr.failed_runs_2025 AS failed_runs_2025,
  jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025 as failure_rate_pct,
  jr.avg_duration_seconds_failed as avg_duration_seconds_failed,
  coalesce(o.total_orders_2025, 0) as total_orders_2025,
  case when coalesce(o.total_orders_2025, 0) = 0 then null
       else cast(jr.failed_runs_2025 as numeric(18,6)) * 1000.0 / o.total_orders_2025
  end as failed_runs_per_1000_orders
FROM tenants t
JOIN (
  SELECT tenant_id,
    count(*) as total_job_runs_2025,
    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,
    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed
  FROM job_runs
  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) jr ON jr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id, count(*) as total_orders_2025
  FROM orders
  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) o ON o.tenant_id = t.id
ORDER BY jr.failed_runs_2025 DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST
LIMIT 20

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

Felix's whole session, 7 turns

The request, as Felix received it

Report request from Lucas Moreau (analyst for the VP Platform Engineering):

Title: Merchants Hit Hardest by Job Failures, 2025
Question: Which merchants had the most background job failures in 2025, and does it track with order volume?
Grain: one row per tenant
Columns:
- tenant_name
- total_job_runs_2025
- failed_runs_2025
- failure_rate_pct
- avg_duration_seconds_failed
- total_orders_2025
- failed_runs_per_1000_orders
Details: job_runs joined to tenants on tenant_id. Filter queued_at in 2025. total_orders_2025 = count of orders with placed_at in 2025 per tenant, left joined so zero-order tenants show 0. failed_runs_per_1000_orders = failed_runs_2025/total_orders_2025*1000 rounded 1dp, blank if zero orders. Order by failed_runs_2025 descending, tie-break tenant_name ascending. Limit to top 20 tenants.

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

Felix calls dry_run

SELECT t.name as tenant_name,
  jr.total_job_runs_2025,
  jr.failed_runs_2025,
  round(jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025, 1) as failure_rate_pct,
  round(jr.avg_duration_seconds_failed, 1) as avg_duration_seconds_failed,
  coalesce(o.total_orders_2025, 0) as total_orders_2025,
  case when coalesce(o.total_orders_2025, 0) = 0 then null
       else round(jr.failed_runs_2025 / o.total_orders_2025 * 1000.0, 1)
  end as failed_runs_per_1000_orders
FROM tenants t
JOIN (
  SELECT tenant_id,
    count(*) as total_job_runs_2025,
    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,
    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed
  FROM job_runs
  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) jr ON jr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id, count(*) as total_orders_2025
  FROM orders
  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) o ON o.tenant_id = t.id
ORDER BY jr.failed_runs_2025 DESC, t.name ASC
LIMIT 20

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, total_job_runs_2025, failed_runs_2025, failure_rate_pct, avg_duration_seconds_failed, total_orders_2025, failed_runs_per_1000_orders

FelixQuery compiles cleanly with the expected columns. Submitting.

Felix calls submit

SELECT t.name as tenant_name,
  jr.total_job_runs_2025,
  jr.failed_runs_2025,
  round(jr.failed_runs_2025 * 100.0 / jr.total_job_runs_2025, 1) as failure_rate_pct,
  round(jr.avg_duration_seconds_failed, 1) as avg_duration_seconds_failed,
  coalesce(o.total_orders_2025, 0) as total_orders_2025,
  case when coalesce(o.total_orders_2025, 0) = 0 then null
       else round(jr.failed_runs_2025 / o.total_orders_2025 * 1000.0, 1)
  end as failed_runs_per_1000_orders
FROM tenants t
JOIN (
  SELECT tenant_id,
    count(*) as total_job_runs_2025,
    count(*) FILTER (WHERE status = 'failed') as failed_runs_2025,
    avg(duration_ms / 1000.0) FILTER (WHERE status = 'failed') as avg_duration_seconds_failed
  FROM job_runs
  WHERE queued_at >= timestamp '2025-01-01' AND queued_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) jr ON jr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id, count(*) as total_orders_2025
  FROM orders
  WHERE placed_at >= timestamp '2025-01-01' AND placed_at < timestamp '2026-01-01'
  GROUP BY tenant_id
) o ON o.tenant_id = t.id
ORDER BY jr.failed_runs_2025 DESC, t.name ASC
LIMIT 20

The warehouse answers

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

FelixSubmitted. The report returns the top 20 tenants by 2025 failed job runs, with total/failed job run counts, failure rate %, average failure duration (seconds), 2025 order volume (0 if none), and failed runs per 1,000 orders (blank for zero-order tenants) — ordered by failed runs descending, tenant name as tie-break.

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

Felix's transcript (260 KB) · Lucas Moreau's transcript (813 KB) · Diego Morales's transcript (264 KB) · the night's plan (2 KB) · the night's reports (6 KB) · the week's memo (4 KB)

Merchants Hit Hardest by Job Failures, 2025

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

Diego Morales writes to Lucas Moreau

Lucas Moreau plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q125

Everything, as recorded