Top 100 Batch Jobs Total Output

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

L4

Asked in

Spotify

Problem

We're auditing throughput on a batch-processing platform and need the combined output of its ten busiest jobs, measured by rows completed. When jobs finish the same number of rows they share a position, so a tie at the tenth spot pulls every one of those jobs in. Return that single total.

The hundred biggest jobs. Combined output.

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

Strip the framing and this is a top-N-with-ties collapsed to one number: dense-rank the jobs by rows completed, keep everything down to the tenth distinct level, then sum. The tell is the tie rule. Three functions look like they answer it and only one does. ROW_NUMBER caps the set at ten rows no matter how many jobs tie. RANK honors ties but skips numbers after each one, so rnk <= 10 covers far fewer than ten distinct sizes. DENSE_RANK gives contiguous shared positions, so the surviving set grows with however many jobs tie. Every rows_done value here belongs to exactly two jobs, so a LIMIT 10 or a RANK cut stops at five sizes and reports roughly half the real total.

Break down the requirements

Step 1: Position, do not just sort

Sort alone cannot express the tie rule. You need a window function so two jobs with equal rows_done share a position and the next distinct value moves to the next integer.

Step 2: Filter on rnk

DENSE_RANK() OVER (ORDER BY rows_done DESC) then WHERE rnk <= 10 selects the ten highest distinct rows_done levels. The surviving row count is not ten; it scales with how many jobs share those values (here two per value, so twenty rows).

Step 3: Sum what survives

Sum rows_done over every row that passes the filter. One scalar out. No GROUP BY is needed since the outer query is a single aggregate, and the 32 jobs with a NULL rows_done never matter: DESC order places them after every real value, and SUM skips them anyway.

The solution

SELECT SUM(rows_done) AS total_rows
FROM (
  SELECT rows_done,
         DENSE_RANK() OVER (ORDER BY rows_done DESC) AS rnk
  FROM batch_jobs
)
WHERE rnk <= 10

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Restrict the ranking to jobs whose `status` is 'completed', in any casing. (Tests whether you place the filter inside the subquery so it applies before positioning, and normalize the mixed-case `status` values.)
• Return the ten highest `rows_done` values per `priority` and sum each bucket. (Probes `PARTITION BY priority` inside the window and group-level aggregation.)
• How would the answer differ if we used `RANK()` instead of `DENSE_RANK()`? (Checks understanding of position-skipping behavior and how it shrinks the surviving row set.)