Scenario: Helix Systems' HR analytics team is investigating compensation record volume per department, knowing each active employee can have multiple salary records due to pay changes over time.
Task: Write a query to return each department name and the number of salary records on file for active employees in that department.
Assumptions:
- An active employee has
is_activeequal toTRUE. - An employee can have multiple salary records — each pay change adds a new record.
- The result covers only salary records associated with active employees.
Output:
- One row per department with at least one active employee on file.
- Columns in this order:
department_name,salary_record_count.
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,
COUNT(*) AS salary_record_count
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
GROUP BY
d.nameThe shape
Each active employee has multiple salary records, so joining employees to salaries produces one row per salary record, not per employee. COUNT(*) then totals those rows inside each department — which is exactly the salary-record count the HR analytics team is investigating.
Clause by clause
SELECT d.name AS department_name, COUNT(*) AS salary_record_countreturns the department name and the salary-record count inside that department.COUNT(*)here counts every row of the joined result, which after the join is one row per salary record.FROM employees ereads the employees as the driving table.is_activelives here, which is why the activity filter applies to this side.JOIN departments d ON e.department_id = d.idattaches each employee's department. This is a one-to-one lookup — every employee has one department — so this join does not fan out.JOIN salaries s ON s.employee_id = e.idattaches each employee's salary records. This is the one-to-many side: an employee with four pay changes contributes four rows.WHERE e.is_active = TRUEkeeps only active employees. The filter applies on the employee side before the salary records get aggregated, so an inactive employee's salary history doesn't enter the count.GROUP BY d.namecollapses the joined rows down to one per department, with the count running inside each group.
Why COUNT(*) and not COUNT(DISTINCT e.id)
COUNT(DISTINCT e.id) would return the number of distinct employees per department, which is a different metric — the prompt asks for the number of salary records, not the number of employees. Salary-record count is exactly what COUNT(*) on the post-join, post-filter row set produces, because every row in that set is one salary record. The fanout that an employee-count question would have to guard against is the very shape this question is asking for.
You practiced counting child rows under a per-parent restriction — every active employee's salary history contributes its full record count to the department total.
