Zero-Retry Job Ratio by Priority

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

City and County of San Francisco

Problem

What share of each job status in batch_jobs ran with zero retries, out of every job carrying that status? The status column is written in inconsistent letter case, so 'FAILED' and 'failed' count as one outcome.

No retries needed. First try success rate.

Tables

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

What this really is

This is a subset-over-whole ratio dressed up as a reliability report. Everyone can count zero-retry jobs, and everyone can count all jobs. What separates candidates is whether both counts come out of the same unfiltered scan of batch_jobs. Reach for WHERE retries = 0 and the denominator is gone: every status reports a clean share of exactly 1. Survive that and integer division is waiting next, turning 26 out of 30 into 0 so every status looks equally broken.

There is a third, quieter trap in the data: status holds 'completed' and 'Completed', 'failed' and 'FAILED'. Group on the raw column and one outcome splits into two rows with two different shares, and neither is the real one.

Step 1: Normalize the outcome before grouping

Group on LOWER(status), not status. Seven raw spellings collapse to five outcomes; 'failed' goes from two half-sized groups to one group of 58 jobs, which is the denominator the share actually needs.

Step 2: Count the subset inside the group

SUM(CASE WHEN retries = 0 THEN 1 ELSE 0 END) counts the clean runs while COUNT(*) counts everything, side by side in one pass. No filter touches the rows, so both numbers describe the same population.

Step 3: Divide as decimals

Both counts are integers, so multiply the clean-run count by 1.0 before dividing by COUNT(*) to force decimal division. The CASE sum appears twice, once as the reported count and once in the ratio, but it is the same aggregate over the same group, so it costs nothing extra.

SELECT
    LOWER(status) AS job_status,
    SUM(CASE WHEN retries = 0 THEN 1 ELSE 0 END) AS zero_retry_jobs,
    COUNT(*) AS total_jobs,
    SUM(CASE WHEN retries = 0 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS zero_retry_share
FROM batch_jobs
GROUP BY LOWER(status)

Filter first

WHERE retries = 0 then GROUP BY leaves only clean jobs, so COUNT(*) equals the zero-retry count and every share is 1. Getting the total back means a second query and a join.

Count conditionally

SUM(CASE WHEN retries = 0 ...) next to COUNT(*) keeps every row in the group, so 'canceled' honestly reports 24 clean out of 28.

Common follow-up questions

• How would you also report the average retry count per status? (Add `AVG(retries)` to the same select list; no second pass needed.)
• Which statuses have fewer than 87% clean runs? (Filter with `HAVING` on the ratio expression.)
• What if `retries` could be `NULL` for jobs that never started? (`NULL = 0` is not true, so those rows count toward `total_jobs` only; decide whether `COALESCE(retries, 0)` or excluding them matches the business meaning.)