16. Salaries above the department average
Asked in screens shaped like: Google, Microsoft, Bloomberg
Return employees earning more than their own department’s average, and show that average beside them. The trap: compute the average with a window and filter it in WHERE, and the average is recomputed over only the surviving rows.
Requirements
- Columns: employee_id, name, dept, salary, dept_avg
- dept_avg is the average salary of the whole department, rounded to 2 decimals
- Keep only employees whose salary exceeds their department average
- Order by dept, then salary descending
Expected output
employee_id, name, dept, salary, dept_avg
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
employees
~8 rows
Org tree. manager_id points back at employee_id in the same table.
- employee_idINTEGER
- nameTEXT
- manager_idINTEGER— NULL for the top of the tree
- deptTEXT— exec, eng, ops
- salaryREAL
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
AVG(salary) OVER (PARTITION BY dept) gives each row its department average.
Hint 2
WHERE runs before window functions, so you cannot filter on the window in the same SELECT.
Hint 3
Compute the window in a CTE, then filter in the outer query.
WITH marked AS (
SELECT employee_id, name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees
)
SELECT employee_id, name, dept, salary, ROUND(dept_avg, 2) AS dept_avg
FROM marked
WHERE salary > dept_avg
ORDER BY dept, salary DESC;Why it works
Logical order is FROM, WHERE, GROUP BY, window, SELECT, ORDER BY. Because windows come after WHERE, the CTE is what keeps dept_avg computed over the full department instead of the filtered subset.
Run your query to see the result set.
Submit to run the checks.
Why it works
Logical order is FROM, WHERE, GROUP BY, window, SELECT, ORDER BY. Because windows come after WHERE, the CTE is what keeps dept_avg computed over the full department instead of the filtered subset.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems