Scenario: Helix Systems' executive committee is identifying departments whose current average salary exceeds $100,000.
Task: Write a query to return each qualifying department's name and its current average salary.
Assumptions:
- A current salary record has
end_daterecorded as a missing value. - A department's
avg_salaryis the average of every current salary record across its employees. - The result covers only departments whose
avg_salaryis strictly greater than100000.
Output:
- One row per qualifying department.
- Columns in this order:
department_name,avg_salary.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
d.name AS department_name,
dept_salaries.avg_salary
FROM
departments d
JOIN (
SELECT
e.department_id,
AVG(s.amount) AS avg_salary
FROM
employees e
JOIN salaries s ON s.employee_id = e.id
WHERE
s.end_date IS NULL
GROUP BY
e.department_id
) AS dept_salaries ON dept_salaries.department_id = d.id
WHERE
dept_salaries.avg_salary > 100000The shape
The threshold check happens at the department level, but the data lives at the salary-record level. A derived table aggregates current salary records up to the department level first, computing each department's average, and the outer query then attaches the department name and applies the > 100000 cut.
Clause by clause
- The derived table:
SELECT e.department_id, AVG(s.amount) AS avg_salary
FROM employees e
JOIN salaries s ON s.employee_id = e.id
WHERE s.end_date IS NULL
GROUP BY e.department_idThe join pairs each employee with their salary records, WHERE s.end_date IS NULL narrows to current records, and the GROUP BY produces one row per department with the current average already computed.
- SELECT d.name AS department_name, dept_salaries.avg_salary returns the department's display name alongside the precomputed average.
- FROM departments d JOIN (...) AS dept_salaries ON dept_salaries.department_id = d.id attaches the department name to the aggregated row. Departments with no current salary records on file do not appear in the derived table, so the inner join silently drops them.
- WHERE dept_salaries.avg_salary > 100000 applies the threshold after the per-department average has already been computed.
Why filter after aggregation and not before
The threshold is on the aggregate, not on individual salary records. Pushing > 100000 into the inner block would filter individual salaries, then average only the high-paid ones — a different, wrong number. The aggregation has to happen first so the per-department average reflects every current salary record in the department, and the threshold only enters once that average exists.
The trap
The current-salary filter is s.end_date IS NULL, not s.end_date = NULL. The equality form returns no rows silently — = NULL is never true in SQL, not even when the value is null, because null is not a value that equals anything. Writing the filter as = NULL would produce an empty derived table, which would then produce an empty final result, with no error message anywhere along the way. IS NULL is the only spelling that detects the missing-value case.
You practiced precomputing per-department averages in a derived table before threshold-checking against $100,000 — keeping the per-department calculation separate from the threshold cut.
