"Do It Without Window Functions"

Concepts covered: Correlated Subquery, Self-Join, Outer Joins, Group Filtering

The follow-up lands 2 minutes after ROW_NUMBER is on the board: "This report runs on MySQL 5.7, no window functions. Same result." It also arrives as Hive 1.x, an old SQLite build or a BI tool that rejects OVER, and it tests 1 thing: whether you can say what the window function computed for you. The candidate who reaches for MAX(salary) with GROUP BY department_id returns 1 row per department.

Every per-group top N computes 1 number per row: how many rows in the same group outrank it. The row stays when that number is below N. PARTITION BY was "same group", ORDER BY was "outranks", and rn <= N compared the count against N. Say that first. It frames everything after as a translation, and a translation is graded on the 1 detail that changes meaning: ties.

The count, written as a correlated subquery

-- Top 3 highest-paid employees per department, no window functions
SELECT e1.department_id, e1.employee_id, e1.salary, e1.hire_date
FROM employees e1
WHERE (
  SELECT COUNT(*)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
    AND (
      e2.salary > e1.salary
      OR (e2.salary = e1.salary AND e2.hire_date < e1.hire_date)
      OR (e2.salary = e1.salary AND e2.hire_date = e1.hire_date
          AND e2.employee_id < e1.employee_id)
    )
) < 3;

Narrate it as a contract: "For each row, count the same-department employees who strictly outrank it; fewer than 3 means top 3. The OR chain is the ORDER BY: higher salary wins, then the earlier hire_date, then the lower employee_id." The rung order is business policy, exactly as the column order in the ORDER BY was.

SELECT employee_id, salary, hire_date
FROM employees e1
WHERE (
  SELECT COUNT(*) FROM employees e2
  WHERE e2.salary > e1.salary
    OR (e2.salary = e1.salary AND e2.hire_date < e1.hire_date)
) < 3

The animation runs that predicate over department 10 alone, so the chain is the whole query. Employee 101 earns 210000 and nobody outranks them. The next 3 employees, 102 through 104, tie at 185000, so the hire_date rung decides: 103 (hired 2018) is outranked by 101 only, 104 (2019) by 101 and 103, and 102 (2020) by all 3. A count of 3 removes 102, and watching those comparisons land is the narration for the room.

The last rung has to be a unique column

The fork the interviewer watches for: drop the tie rungs and count only e2.salary > e1.salary. The 3 employees at 185000 each get a count of 1 and all of them pass, so a top 3 returns 4 rows. That is RANK <= 3. The chain turns it into exactly N only when its last rung is unique within the group: end on hire_date and 2 people hired the same day still tie. Say "the unique column closes the tie" unprompted; it is the sentence of someone who has debugged a bonus pool with 4 names in it.

The same count as a self-join

-- Top 3 per department via self-join, RANK semantics
SELECT e1.department_id, e1.employee_id, e1.salary
FROM employees e1
LEFT JOIN employees e2
  ON e2.department_id = e1.department_id
 AND e2.salary > e1.salary
GROUP BY e1.department_id, e1.employee_id, e1.salary
HAVING COUNT(e2.employee_id) < 3;

Some planners handle a join and a GROUP BY better than a subquery tied to the outer row, so carry the second form too. The join pairs each e1 row with every e2 row that outranks it, GROUP BY folds the pairs back to 1 row per employee, and HAVING is the count comparison. Two details are scored: LEFT JOIN, because the top earner has nothing above them and an INNER JOIN drops them, and COUNT(e2.employee_id), because COUNT(*) counts the NULL-padded row and gives the top earner a count of 1.

SELECT e1.employee_id, e1.salary
FROM employees e1
LEFT JOIN employees e2
  ON e2.salary > e1.salary
GROUP BY e1.employee_id, e1.salary
HAVING COUNT(e2.employee_id) < 2

This run asks for a top 2 on the salary rung alone. Employee 101 joins to nothing; 102 and 103 at 185000 are each outranked by 101 only, count 1, so a top 2 returns 3 rows. The form changed and the leak did not, because both forms evaluate the same outranks predicate.

SELECT e1.employee_id, e2.employee_id AS beaten_by
FROM employees e1
LEFT JOIN employees e2
  ON e2.salary > e1.salary
  OR (e2.salary = e1.salary AND e2.hire_date < e1.hire_date)

Add the hire_date rung to the join condition and the tie turns one-sided: 103, hired in 2018, outranks 102, hired in 2020, and nothing outranks 103. One carries a beaten_by value and the other NULL, which is what lets HAVING keep exactly 1. The depth signal is converting both ways: reading a window query as this comparison, and reading the comparison back as its ORDER BY.

Correlated COUNT subquery

• Reads as the contract: count who outranks me, keep the row below N
• Postgres and MySQL 5.7 run it once per outer row: rows times group size
• SQL Server and Oracle may unnest it into a join plus aggregate; the plan says whether they did

Self-join plus GROUP BY

• The same count as LEFT JOIN plus HAVING COUNT(e2.employee_id) < N
• MySQL 5.7 has no hash join: a nested loop that wants an index on (department_id, salary)
• Hash-join engines join on department_id and apply the inequality as a residual filter
• Join output grows with group size and with ties, which the plan shows as join rows

About this lesson

This section is part of the Top N Per Group: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.