Scenario: Helix Systems' HR director needs a current headcount and salary summary for annual workforce planning, broken out by department.
Task: Write a query to return each department name, the number of active employees in it, and the average current salary across those employees.
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 active employees with a current salary on record.
Output:
- One row per department containing at least one qualifying employee.
- Columns in this order:
department_name,headcount,avg_salary. - Sorted by
department_nameascending.
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_employees AS (
SELECT
e.id AS employee_id,
d.name AS department_name,
s.amount AS salary
FROM
employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON s.employee_id = e.id
WHERE
e.is_active = TRUE
AND s.end_date IS NULL
),
dept_summary AS (
SELECT
department_name,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM
active_employees
GROUP BY
department_name
)
SELECT
department_name,
headcount,
avg_salary
FROM
dept_summary
ORDER BY
department_nameThe shape
Two CTEs, with the first doing all the work of identifying the right rows and the second doing all the work of summarising them. The first layer joins three tables and applies both filters, so by the time active_employees is built every row in it is a currently-paid active employee. The summary then groups that clean set by department.
Clause by clause
WITH active_employees AS (
SELECT e.id AS employee_id, d.name AS department_name, s.amount AS salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON s.employee_id = e.id
WHERE e.is_active = TRUE
AND s.end_date IS NULL
)Three tables come together: employees for the identity, departments for the readable name, salaries for the amount. The two WHERE predicates run against the joined set, so only active employees with a still-open salary record survive.
dept_summary AS (
SELECT department_name, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM active_employees
GROUP BY department_name
)GROUP BY department_name produces one row per department. COUNT(*) counts the qualifying employees in each, and AVG(salary) averages their current salaries. Engineering ends up with 16 employees averaging 164,812.50; Support with 5 averaging 100,000.
SELECT department_name, headcount, avg_salary FROM dept_summary ORDER BY department_namereturns the summary alphabetised, ready for the workforce planning sheet.
You practiced building the per-department summary as a chain of two CTEs — one that gathers the qualifying employees with their salaries, another that summarizes them per department.
