Department With the Highest Average Pay
Problem
A table employee(id, name, dept, salary) holds one row per person. Write a query that returns the department with the highest average salary, together with that average rounded to two decimals, as columns dept and avg_salary. When several departments tie for the highest average, return all of them, sorted by department.
Examples
Input: employee = [(1, "Ana", "eng", 120), (2, "Cleo", "eng", 130), (3, "Dev", "ops", 95), (4, "Finn", "ops", 100), (5, "Gus", "hr", 70)]
Output: [("eng", 125.0)]
Why: the averages are eng 125, ops 97.5 and hr 70Input: employee = [(1, "Ana", "eng", 100), (2, "Ben", "eng", 110), (3, "Dev", "ops", 105)]
Output: [("eng", 105.0), ("ops", 105.0)]
Why: both departments average 105, so both come backInput: employee = [(1, "Ana", "sales", 100), (2, "Ben", "sales", 100), (3, "Cy", "sales", 101)]
Output: [("sales", 100.33)]
Why: edge case, 301 / 3 is rounded to two decimals only for displayHints
0 / 3
Split the question in two: first the average of every department, then the largest of those averages.
ORDER BY the average with LIMIT 1 is the tempting answer, but it silently drops a department that ties for first place.
Put the per-department averages in a CTE, then select the rows of that CTE whose average equals (SELECT MAX(avg_salary) FROM the CTE). Compare the raw averages and round only in the final SELECT.
Solution
GROUP BY dept with AVG(salary) gives one row per department, and naming that result in a WITH clause lets the query read it twice: once to find the highest average with MAX, once to keep every department that reaches it. Comparing against the maximum rather than taking the first row after a sort is what keeps ties, since LIMIT 1 returns one arbitrary winner out of several. Rounding happens only in the last SELECT, because rounding before comparing could make two different averages look equal. Grouping is O(n) with hashing or O(n log n) with sorting, and the CTE has one row per department, so reading it twice costs almost nothing.
import sqlite3
QUERY = """
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept
)
SELECT dept, ROUND(avg_salary, 2) AS avg_salary
FROM dept_avg
WHERE avg_salary = (SELECT MAX(avg_salary) FROM dept_avg)
ORDER BY dept
"""
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, "Cleo", "eng", 130), (3, "Dev", "ops", 95), (4, "Finn", "ops", 100), (5, "Gus", "hr", 70)])) # -> [('eng', 125.0)]
print(run([(1, "Ana", "eng", 100), (2, "Ben", "eng", 110), (3, "Dev", "ops", 105)])) # -> [('eng', 105.0), ('ops', 105.0)]
print(run([(1, "Ana", "sales", 100), (2, "Ben", "sales", 100), (3, "Cy", "sales", 101)])) # -> [('sales', 100.33)]Stuck on the idea rather than the code? Aggregations & GROUP BY covers it.