Scenario: Helix Systems' finance team is auditing compensation costs and wants to surface departments where the average current salary exceeds $130,000.
Task: Write a query to return each department name, the number of active employees in it, the average current salary across those employees, and the total current payroll, restricted to departments where the average current salary exceeds $130,000.
Assumptions:
- An active employee has
is_activeequal toTRUE. - The current salary is the salary record whose
end_dateis a missing value. - The result covers only departments whose average current salary exceeds
$130,000.
Output:
- One row per qualifying department.
- Columns in this order:
department_name,headcount,avg_salary,total_payroll. - Sorted by
total_payrolldescending.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
active_salaries AS (
SELECT
e.department_id,
s.amount AS salary
FROM
employees e
JOIN salaries s ON s.employee_id = e.id
WHERE
e.is_active = TRUE
AND s.end_date IS NULL
),
dept_metrics AS (
SELECT
d.name AS department_name,
COUNT(*) AS headcount,
AVG(sal.salary) AS avg_salary,
SUM(sal.salary) AS total_payroll
FROM
active_salaries sal
JOIN departments d ON d.id = sal.department_id
GROUP BY
d.name
),
high_cost_depts AS (
SELECT
department_name,
headcount,
avg_salary,
total_payroll
FROM
dept_metrics
WHERE
avg_salary > 130000
)
SELECT
department_name,
headcount,
avg_salary,
total_payroll
FROM
high_cost_depts
ORDER BY
total_payroll DESCThe shape
Three CTEs in the order qualifying, summarise, restrict. The first builds the set of active employees with current salaries, the second rolls them up to per-department headcount, average, and total, and the third drops the departments that fail the $130,000 average check. The threshold runs over already-computed averages, so the comparison value exists at the layer where the comparison happens.
Clause by clause
WITH active_salaries AS (
SELECT e.department_id, s.amount AS salary
FROM employees e
JOIN salaries s ON s.employee_id = e.id
WHERE e.is_active = TRUE
AND s.end_date IS NULL
)employees is joined to salaries to pair each employee with their pay, and the two predicates restrict the join to active employees with an open salary record. Only department_id and salary are carried forward because the next layer joins the department name back in.
dept_metrics AS (
SELECT d.name AS department_name, COUNT(*) AS headcount, AVG(sal.salary) AS avg_salary, SUM(sal.salary) AS total_payroll
FROM active_salaries sal
JOIN departments d ON d.id = sal.department_id
GROUP BY d.name
)The department table is joined in here, and GROUP BY d.name produces one row per department. Three aggregates are computed in one pass: headcount, average salary, and total payroll. Engineering's 16 employees average 164,812.50 and total 2,637,000.
high_cost_depts AS (
SELECT department_name, headcount, avg_salary, total_payroll
FROM dept_metrics
WHERE avg_salary > 130000
)WHERE avg_salary > 130000 runs against the already-aggregated value. Five departments clear the bar; HR, Marketing, and Support fall below.
SELECT department_name, headcount, avg_salary, total_payroll FROM high_cost_depts ORDER BY total_payroll DESCorders the survivors by total cost so the biggest payroll lines surface first.
The trap
The threshold is on the average salary, not on the total payroll. A small department of highly-paid people clears the avg_salary > 130000 bar even when its total payroll is modest; a large department of moderately-paid people may carry a huge total but still get filtered out because the average sits below the line. Reading the prompt as "high-cost departments" and reaching for a total-payroll threshold would change which departments survive.
You practiced sequencing three CTEs — qualifying employees, per-department totals, threshold cut — so the cut runs over already-summarized values rather than inside the summary.
