# The Quiet Middle > Not the biggest, not the smallest. The overlooked middle. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## Worked solution and explanation ### What this really is This looks like a filter-and-sort exercise, but the real skill is that you cannot filter a window function's output in the same WHERE clause where the window lives. DENSE_RANK() is evaluated after WHERE runs, so the position has to be computed in an inner query and filtered one level up. Anyone can write DENSE_RANK() OVER (ORDER BY rows_done DESC); the trap is trying to say WHERE DENSE_RANK() ... BETWEEN 8 AND 10 directly, which the engine rejects outright. And you specifically want DENSE_RANK, not ROW_NUMBER: two jobs tied at 194 rows must both hold position 8, or your band silently drops one of them and the row counts quietly drift. --- ### Build it in layers #### Step 1: Compute the position in a subquery Window functions are evaluated after WHERE, so you cannot reference the position in the same query level that defines it. Wrap the DENSE_RANK() SELECT in a subquery (aliased ranked here) and filter its output from the outer query. This layering is the whole point of the problem. #### Step 2: Pick DENSE_RANK for shared positions DENSE_RANK() gives tied rows the same number and never leaves a gap, so the two jobs at 194 rows both land on position 8 and the next job takes 9. ROW_NUMBER() would force the tied pair apart, and RANK() would skip to 10 after them; either one corrupts a positional band like 8 to 10. #### Step 3: Filter the band and order deterministically BETWEEN 8 AND 10 keeps exactly the three positions. Because two rows share position 8, add job_name as a secondary sort so their order is stable and reproducible; ORDER BY rnk alone leaves the tie order up to the engine. --- ### The solution **DENSE_RANK computed inside, band filtered outside** ```sql 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, job_name ``` > **Where the cost goes** > > The window function forces a full sort of all 350K rows on rows_done, and that O(n log n) sort dominates the plan. There is no shortcut: you have to position every row before you know which sit at 8 through 10, so an index on rows_done is what keeps the sort from spilling to disk. The outer BETWEEN is then a trivial post-filter over an already tiny positioned set. > **Interviewers watch for** > > The tell is whether you reach for a subquery or CTE the instant you need to filter on a window result, instead of flailing with WHERE on the position directly. Naming why DENSE_RANK beats ROW_NUMBER and RANK here, in concrete tie terms, is what separates someone who understands positioning from someone pattern-matching a template. > **Common pitfall** > > Filtering the position in the same WHERE: WHERE DENSE_RANK() OVER (...) BETWEEN 8 AND 10 fails because window functions are not allowed in WHERE at all. You must push the positioning into a subquery or CTE first, then filter the resulting column. --- ## Common follow-up questions - If some jobs have a NULL rows_done, where do they land in the descending ordering, and could they slip into your 8 to 10 band? _(Tests NULL sort behavior and whether NULLs can leak into the band.)_ - How would the result change if you swapped DENSE_RANK() for RANK() and there were ties at positions above 8? _(Tests RANK vs DENSE_RANK vs ROW_NUMBER tie behavior.)_ - The table holds 350K rows and grows daily. What index would keep the positioning sort cheap? _(Tests indexing knowledge for the ranking sort on a large, growing table.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_quiet_middle) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.