Queries · q124 · VP Platform Engineering · week 1, night 1
“How did background job failure rates and run durations trend across 2025?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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.
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:
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
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:
Happy to flag specific merchant names if that helps scope the analysis.
Best, Samuel
The analyst · week 1, night 1, request 1 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.
The engineer · 1 dry run on the empty schema, then the submission
select date_trunc('month', queued_at)::date as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, round(100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0), 2) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, round(100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0), 2) as cancelled_rate_pct, round(avg(extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null), 1) as avg_duration_seconds, round(percentile_cont(0.95) within group (order by extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null)) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc
“Query compiles cleanly with the expected columns.”
“Submitted. The report aggregates job_runs by calendar month of queued_at for 2025, platform-wide across all tenants, with failure/cancellation rates and avg/p95 duration (computed only over runs that have both started_at and finished_at).”
As Lucas Moreau saw it, from the small dataset the agents worked on
month | total_job_runs | failed_runs | failure_rate_pct | cancelled_runs | cancelled_rate_pct | avg_duration_seconds | p95_duration_seconds -----------+----------------+-------------+------------------+----------------+--------------------+----------------------+--------------------- 2025-10-01 | 15688 | 0 | 0.0 | 0 | 0.0 | 4.8 | 13.0 2025-11-01 | 16487 | 20 | 0.12 | 0 | 0.0 | 5.4 | 18.0 2025-12-01 | 17072 | 0 | 0.0 | 0 | 0.0 | 5.0 | 16.0 (3 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
select date_trunc('month', queued_at)::date as month, CAST(date_trunc('month', queued_at) AS date) as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, round(100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0), 2) as failure_rate_pct, 100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, round(100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0), 2) as cancelled_rate_pct, round(avg(extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null), 1) as avg_duration_seconds, round(percentile_cont(0.95) within group (order by extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null)) as p95_duration_seconds 100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0) as cancelled_rate_pct, avg({{seconds_between(started_at, finished_at)}}) filter (where started_at is not null and finished_at is not null) as avg_duration_seconds, {{percentile_cont_filter(0.95, {{seconds_between(started_at, finished_at)}}, started_at is not null and finished_at is not null)}} as p95_duration_secondsfrom job_runswhere queued_at >= date '2025-01-01' and queued_at < date '2026-01-01'group by 1order by 1 ascorder by 1 asc NULLS LAST
select CAST(date_trunc('month', queued_at) AS date) as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, 100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, 100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0) as cancelled_rate_pct, avg(CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6))) filter (where started_at is not null and finished_at is not null) as avg_duration_seconds, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6))) FILTER (WHERE started_at is not null and finished_at is not null) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc NULLS LAST
select CAST(date_trunc('month', queued_at) AS date) as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, 100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, 100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0) as cancelled_rate_pct, avg(CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6))) filter (where started_at is not null and finished_at is not null) as avg_duration_seconds, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CASE WHEN started_at is not null and finished_at is not null THEN CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6)) END) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc NULLS LAST
select CAST(date_trunc('month', queued_at) AS date) as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, 100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, 100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0) as cancelled_rate_pct, avg(CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6))) filter (where started_at is not null and finished_at is not null) as avg_duration_seconds, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (finished_at - started_at)) AS NUMERIC(18,6)) AS DOUBLE PRECISION)) FILTER (WHERE started_at is not null and finished_at is not null) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc NULLS LAST
select CAST(date_trunc('month', queued_at) AS date) as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, 100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, 100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0) as cancelled_rate_pct, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', started_at, finished_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))) filter (where started_at is not null and finished_at is not null) as avg_duration_seconds, quantileExactInclusiveIf(0.95)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', started_at, finished_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))), started_at is not null and finished_at is not null) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Lucas Moreau (analyst for the VP Platform Engineering): Title: 2025 Background Job Reliability by Month Question: How did background job failure rates and run durations trend across 2025? Grain: one row per calendar month of 2025 Columns: - month - total_job_runs - failed_runs - failure_rate_pct - cancelled_runs - cancelled_rate_pct - avg_duration_seconds - p95_duration_seconds Details: Table job_runs. Filter queued_at in 2025. Group by month of queued_at. failure_rate_pct = failed_runs/total_job_runs*100 rounded 2dp. Duration = finished_at-started_at in seconds where both set, avg rounded 1dp, p95 via percentile_cont(0.95) rounded to whole seconds. Platform-wide, all tenants combined. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect date_trunc('month', queued_at)::date as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, round(100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0), 2) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, round(100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0), 2) as cancelled_rate_pct, round(avg(extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null), 1) as avg_duration_seconds, round(percentile_cont(0.95) within group (order by extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null)) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, total_job_runs, failed_runs, failure_rate_pct, cancelled_runs, cancelled_rate_pct, avg_duration_seconds, p95_duration_seconds
submitselect date_trunc('month', queued_at)::date as month, count(*) as total_job_runs, count(*) filter (where status = 'failed') as failed_runs, round(100.0 * count(*) filter (where status = 'failed') / nullif(count(*), 0), 2) as failure_rate_pct, count(*) filter (where status = 'cancelled') as cancelled_runs, round(100.0 * count(*) filter (where status = 'cancelled') / nullif(count(*), 0), 2) as cancelled_rate_pct, round(avg(extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null), 1) as avg_duration_seconds, round(percentile_cont(0.95) within group (order by extract(epoch from (finished_at - started_at))) filter (where started_at is not null and finished_at is not null)) as p95_duration_seconds from job_runs where queued_at >= date '2025-01-01' and queued_at < date '2026-01-01' group by 1 order by 1 asc
Submitted to tonight's batch. Your work on this request is done.