Third Largest Batch Job

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

L3

Asked in

Meta

Problem

A job name in batch_jobs covers many runs, so a job's size is the rows processed summed across all of its runs. Ahead of the weekly platform review, the lead wants every job down to the third-largest by that total, largest first.

Bronze medal in the batch job rankings.

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 top-three over per-name totals dressed up as a capacity review. Every run of a job is its own row in batch_jobs, twenty rows per name, so the thing being ranked does not exist until you build it with SUM(rows_done) per job_name. Anyone can sort descending. The trap is sorting the runs: the three biggest single runs belong to 'daily_report', 'sync_elastic' and 'backup_db', and two of those are nowhere near the top three totals. Rank the runs and you hand the review a shortlist that is two-thirds wrong.

Step 1: Build one total per job_name

GROUP BY job_name with SUM(rows_done) AS total_rows collapses the 200 runs into ten jobs. Thirty-two runs have a NULL rows_done, and SUM skips them, which is exactly the business rule: a run with no recorded count adds nothing. No COALESCE is needed, and no status filter either: the ask counts every run.

Step 2: Position the totals, not the runs

Put DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) in the same SELECT as the GROUP BY. The window is evaluated after grouping, so it orders the ten totals. Dense positions mean a tie at third keeps every tied job and the cut stays on totals, not on row counts.

Step 3: Cut at three, then order the answer

A window value cannot be filtered in the WHERE of the level that computes it, so wrap the grouped query and filter rnk <= 3 outside. Finish with ORDER BY total_rows DESC: the lead reads it largest first, and the same three rows ascending is a different answer.

SELECT job_name, total_rows
FROM (
    SELECT
        job_name,
        SUM(rows_done) AS total_rows,
        DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) AS rnk
    FROM batch_jobs
    GROUP BY job_name
) ranked_jobs
WHERE rnk <= 3
ORDER BY total_rows DESC;

Sorting runs

ORDER BY rows_done DESC over raw rows returns 'daily_report' (9700), 'sync_elastic' (9603), 'backup_db' (9506): the three biggest single runs.

Sorting job totals

'sync_elastic' 104760, 'metric_agg' 100880, 'cache_warm' 97000. 'daily_report' totals only 71780 because six of its twenty runs have a NULL rows_done.

Common follow-up questions

• What if the review wanted the top three per `priority` level? (Tests `PARTITION BY priority` and that the cut restarts in each partition.)
• How would you count only completed runs, given `status` holds both 'completed' and 'Completed'? (Tests normalizing with `LOWER(status)` before filtering.)
• Two jobs tie for third: what does your query return, and what would `ROW_NUMBER()` return? (Tests the tie semantics of dense positions versus row numbers.)