Helix Systems' HR director is reviewing headcount to identify well-staffed departments.
Write a query to return the department ID and employee count for every department with more than five employees on record.
Assumptions:
- The
employeestable contains every active and former employee. - The threshold (
> 5) applies to the per-department headcount.
Output:
- One row per qualifying department, with columns
department_idandemp_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
department_id,
emp_count
FROM
(
SELECT
department_id,
COUNT(*) AS emp_count
FROM
employees
GROUP BY
department_id
) AS dept_counts
WHERE
emp_count > 5The shape
The inner query produces a headcount per department; the outer query keeps only the departments whose headcount exceeds five. Two passes — group first, threshold second — held together by the derived table.
Clause by clause
- The inner block counts employees per department:
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_idOne row per department, with emp_count as the running headcount.
- FROM (...) AS dept_counts materialises that result as a derived table. The alias names what each row represents and is mandatory for the query to parse.
- WHERE emp_count > 5 narrows the per-department rows to the five departments staffed above the threshold. Department 1 is the largest at 17, department 3 next at 11.
- SELECT department_id, emp_count returns the two columns the HR director needs to see at a glance. Any column the inner SELECT didn't expose — salary, hire_date, name — is unreachable from this layer.
You practiced the aggregate-then-filter pattern in a third domain. Internalising the shape pays off because it generalises: the outer query can do anything WHERE allows on the aggregated columns — comparisons, ranges, IN lists.
