The Quiet Middle

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're auditing the batch pipeline and want the jobs in the overlooked middle by rows processed: not the busiest, not the idlest. Line them up from most rows to least, then return positions 8 through 10, each job's name with its position, lowest position first.

Not the biggest, not the smallest. The overlooked middle.

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 question about what a position means, dressed up as a pipeline audit. Every rows_done value in batch_jobs is held by two runs, so positions 8 through 10 are six rows, not three: two data_cleanup runs at 8,924 rows, two user_export runs at 8,827 and two sync_elastic runs at 8,633. Anyone can sort by rows processed. What separates candidates is reading position as a standing shared by every run with the same count and numbered without gaps. Slice physical rows with LIMIT 3 OFFSET 7 instead and you get one metric_agg run and two cache_warm runs, none of which belong in the band.

One number per row, or gaps after ties

ROW_NUMBER() splits the 9,409-row pair across positions 7 and 8 and puts the 9,215-row pair at 9 and 10, so the band is metric_agg, cache_warm, cache_warm. RANK() lets each pair share but skips a number after it (1, 1, 3, 3, ...), so the only position inside 8 to 10 is 9, held by the cache_warm pair.

Shared, gap-free positions

DENSE_RANK() gives both runs of a count the same position and the next lower count the very next number, so 8, 9 and 10 are the 8,924, 8,827 and 8,633 counts: six runs, exactly the preview.

Build it in layers

Step 1: Number by the count, not by the row

Compute DENSE_RANK() OVER (ORDER BY rows_done DESC) as rnk. Ordering by the count descending is the 'most rows to least' in the statement, and the dense numbering is what the preview shows: two rows at 8, two at 9, then 10.

Step 2: Filter the position one level up

A window is evaluated after WHERE, so WHERE DENSE_RANK() OVER (...) BETWEEN 8 AND 10 is rejected outright. Put the numbering in a derived table (ranked) and filter its rnk column from the outer query.

Step 3: Keep the band, lowest position first

rnk BETWEEN 8 AND 10 is inclusive at both ends, which is exactly the three positions asked for. ORDER BY rnk puts position 8 first. The two runs sharing a position are identical rows, so nothing more is needed to order them.

SELECT job_name, rnk
FROM (
    SELECT job_name, DENSE_RANK() OVER (ORDER BY rows_done DESC) AS rnk
    FROM batch_jobs
) ranked
WHERE rnk BETWEEN 8 AND 10
ORDER BY rnk

Common follow-up questions

• If two different jobs processed the same number of rows, what would the band look like, and does anything in your output need to change? (Tests whether the candidate sees that one position can hold different jobs.)
• How would the result change if you swapped `DENSE_RANK()` for `RANK()`? (Tests gap behaviour after ties: only the `cache_warm` pair at position 9 would survive.)
• The table grows to hundreds of millions of runs. How do you keep finding positions 8 to 10 cheap? (Tests indexing on `rows_done` and whether positioning the distinct counts first can replace a full sort.)