Top Two Earners per Department
Problem
A table employee(id, name, dept, salary) holds one row per person. For each department, return everyone whose salary is one of the two highest distinct salaries in that department, so people who tie are all kept. Return columns dept, name and salary, sorted by department, then salary from high to low, then name.
Examples
Input: employee = [(1, "Ana", "eng", 120), (2, "Ben", "eng", 90), (3, "Cleo", "eng", 130), (4, "Dev", "ops", 95), (5, "Eve", "ops", 80), (6, "Finn", "ops", 100)]
Output: [("eng", "Cleo", 130), ("eng", "Ana", 120), ("ops", "Finn", 100), ("ops", "Dev", 95)]Input: employee = [(1, "Ana", "eng", 120), (2, "Ben", "eng", 120), (3, "Cleo", "eng", 110), (4, "Dev", "eng", 100)]
Output: [("eng", "Ana", 120), ("eng", "Ben", 120), ("eng", "Cleo", 110)]
Why: Ana and Ben share the top salary, so the two highest distinct salaries are 120 and 110Input: employee = [(1, "Ana", "eng", 120), (2, "Gus", "hr", 70)]
Output: [("eng", "Ana", 120), ("hr", "Gus", 70)]
Why: edge case, a department with one person returns that personHints
0 / 3
A ranking that restarts in every department is a window function with PARTITION BY dept ORDER BY salary DESC.
Three ranking functions differ on ties. ROW_NUMBER never repeats a number, RANK repeats and then skips, DENSE_RANK repeats without skipping. Which one makes the second distinct salary rank 2?
A window function cannot appear in WHERE, because WHERE runs before SELECT computes it. Rank with DENSE_RANK in a subquery, then filter rnk <= 2 in the outer query.
Solution
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) numbers the distinct salaries of each department 1, 2, 3 and gives tied people the same number without leaving a gap, so rank 2 always means the second distinct salary. ROW_NUMBER would keep exactly two people and drop one of a tied pair, and RANK would give the third person in the tie example rank 3. The filter cannot go in the same query: the logical order is FROM, WHERE, GROUP BY, HAVING, then SELECT, and window functions are computed in SELECT, after WHERE has already run. So the ranking lives in a subquery and the outer query filters on it. The window sorts each department, O(n log n) overall.
import sqlite3
QUERY = """
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employee
) AS ranked
WHERE rnk <= 2
ORDER BY dept, salary DESC, name
"""
def run(employees):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER)")
db.executemany("INSERT INTO employee VALUES (?, ?, ?, ?)", employees)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana", "eng", 120), (2, "Ben", "eng", 90), (3, "Cleo", "eng", 130), (4, "Dev", "ops", 95), (5, "Eve", "ops", 80), (6, "Finn", "ops", 100)])) # -> [('eng', 'Cleo', 130), ('eng', 'Ana', 120), ('ops', 'Finn', 100), ('ops', 'Dev', 95)]
print(run([(1, "Ana", "eng", 120), (2, "Ben", "eng", 120), (3, "Cleo", "eng", 110), (4, "Dev", "eng", 100)])) # -> [('eng', 'Ana', 120), ('eng', 'Ben', 120), ('eng', 'Cleo', 110)]
print(run([(1, "Ana", "eng", 120), (2, "Gus", "hr", 70)])) # -> [('eng', 'Ana', 120), ('hr', 'Gus', 70)]Stuck on the idea rather than the code? Query Execution Order covers it.