Batch Job Performance Tiers

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

Travelport

Problem

The data platform team is grading batch job throughput for their quarterly review, where a job's grade comes from the total rows it processed across all of its runs. A job clearing 700 million rows is 'Outstanding', 350 million up to 700 million is 'Satisfactory', 200 million up to 350 million is 'Unsatisfactory', and anything below 200 million is 'Poor'. Show each job's name, its total rows processed, and its tier.

Every batch job gets a grade.

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 problem really is

Beneath the quarterly-review framing this is a bucketing problem: collapse many job runs down to a per-name total, then map that single aggregate onto ordered tier labels. The trap is treating the CASE as decoration you can skip: the platform's per-name totals actually straddle the 200M/350M/700M cutoffs, so the label IS the answer, not the raw SUM. Two ways candidates blow it: they order the CASE branches low-to-high so the 200-million branch swallows every job, or they group by job_id and report per-run rows instead of per-name throughput. Either way the tiers come out wrong and a top performer gets stamped 'Poor'.

Break down the requirements

Step 1: Pick the grain

batch_jobs carries both job_id and job_name. The prompt grades 'each job's name', so collapse to job_name with GROUP BY job_name. Ask out loud whether the many job_id rows per name are reruns to sum (they are here) or whether you want a max.

Step 2: Aggregate rows

SUM(rows_done) AS total_rows. Sum, not avg, because the tier is about total throughput over the quarter, not per-run average. SUM also skips the null rows_done rows for free, so a crashed run that never logged a count just contributes nothing.

Step 3: Bucket with CASE WHEN

Order branches high-to-low so the first true one wins: >= 700000000 'Outstanding', then >= 350000000, then >= 200000000, else 'Poor'. Wrapping SUM(rows_done) in CASE is fine; the planner computes the aggregate once per group.

Step 4: Sort the output

ORDER BY total_rows DESC so the platform PM reads top throughput first, with job_name as a stable tiebreaker. Don't sort by the tier label; alphabetical 'Outstanding' before 'Poor' is meaningless to a reader.

The solution

SELECT
  job_name,
  SUM(rows_done) AS total_rows,
  CASE
    WHEN SUM(rows_done) >= 700000000 THEN 'Outstanding'
    WHEN SUM(rows_done) >= 350000000 THEN 'Satisfactory'
    WHEN SUM(rows_done) >= 200000000 THEN 'Unsatisfactory'
    ELSE 'Poor'
  END AS performance_tier
FROM batch_jobs
GROUP BY job_name
;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you rewrite this so the tier thresholds come from a config table? (Tests whether you reach for a small thresholds CTE or table plus `BETWEEN`, and whether you handle the quarter-over-quarter SCD.)
• Show the count and percentage of jobs in each tier. (Probes a second aggregation layer: wrap this query in a CTE and `GROUP BY performance_tier` with `COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()`.)
• How does the answer change if `rows_done` can be NULL for jobs that crashed mid-run? (Tests whether you remember `SUM` ignores NULL but `COALESCE(rows_done, 0)` is safer if you ever divide, plus whether crashed runs should be excluded entirely.)
• The PM wants a 5th tier 'Elite' at 1 billion rows and above. How do you add it without rewriting? (Reinforces the config-table answer and shows you anticipate threshold churn.)