Top 10 Batch Jobs

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Spotify

Problem

We run a batch-processing platform where each job runs on a schedule, so one job name recurs across many runs. Build a leaderboard of the jobs holding the five highest standings by total rows processed, showing each job's name and its standing, busiest first with ties broken alphabetically by name.

The ten biggest batch jobs.

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 asks

Under the leaderboard costume this is a per-name total with a tie-preserving cutoff. The scoring quantity is not a row, it is the sum of rows_done for a job name, because the same job runs on a schedule and shows up many times. Anyone can sort and cut off five rows. The two things that separate candidates: aggregate by job_name first so a recurring job counts once, and cut the list by standing rather than by row so a tie at the fifth spot is not silently chopped. Miss either and you either rank fragments of a job or drop a job that legitimately tied for fifth.

The moves

Step 1: Total each job's rows

Collapse the runs first: GROUP BY job_name and SUM(rows_done). A job name recurs across scheduled runs, so its size is the total over every run, and SUM quietly ignores the NULL rows_done rows rather than letting one poison the total.

Step 2: Give each total a standing

Assign DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) over those totals. DENSE_RANK, not ROW_NUMBER or RANK, so two jobs with the same total share one standing and the next total takes the very next number with no gap.

Step 3: Cut at the fifth standing

Wrap the ranking in a subquery and keep WHERE rnk <= 5. This is the load-bearing filter: it cuts standings 6 and below while keeping every job that lands at standing 5 or better, even if that is more than five rows. A plain limit of five cannot do both.

The solution

SELECT job_name, rnk
FROM (
  SELECT job_name,
         DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) AS rnk
  FROM batch_jobs
  GROUP BY job_name
) ranked
WHERE rnk <= 5
ORDER BY rnk ASC, job_name ASC

LIMIT 5 over raw rows

Sorts individual runs, so a recurring job is split across many rows and its total never forms. Cuts at exactly five rows, so a tie for fifth is truncated arbitrarily.

`DENSE_RANK` with WHERE rnk <= 5

Sums per job first, ranks the totals, then keeps every job at standing 5 or better. Recurring jobs count once and tied jobs at the cutoff all survive.

Common follow-up questions

• How would you produce the top three jobs within each status? (Tests moving from a global standing to a per-group one with PARTITION BY.)
• How does the answer change if you must return exactly five rows even under ties? (Tests the difference between `DENSE_RANK`, RANK, and `ROW_NUMBER` at the cutoff.)
• Why does a NULL `rows_done` not need an explicit filter here? (Tests understanding of how SUM treats NULL versus how COUNT would.)