Average Current Salary per Department — Query Performance in SQL

The problem

Scenario: Helix Systems' HR analytics team needs the average current salary broken out by department.

Task: Write a query to return each department_id and the average current salary across employees in that department.

Assumptions:

A current salary record has end_date recorded as a missing value.
A department's avg_current_salary is the average of every current salary record for employees in that department.
The result covers only departments with at least one current salary record on file.

Output:

One row per department with at least one current salary record.
Columns in this order: department_id, avg_current_salary.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH
  current_salaries AS (
    SELECT
      employee_id,
      amount
    FROM
      salaries
    WHERE
      end_date IS NULL
  )
SELECT
  e.department_id,
  AVG(cs.amount) AS avg_current_salary
FROM
  employees e
  JOIN current_salaries cs ON cs.employee_id = e.id
GROUP BY
  e.department_id

The shape

Two independent operations have to compose: narrow salaries to the current set (end_date IS NULL), then average those amounts per department. The CTE isolates the narrowing step; the main query joins the narrowed set to employees to pick up department_id and runs the per-department average.

Clause by clause

The CTE current_salaries reads salaries with WHERE end_date IS NULL and projects employee_id, amount. Every row downstream is already a current salary record.
SELECT e.department_id, AVG(cs.amount) AS avg_current_salary produces the per-department average from the narrowed set.
FROM employees e JOIN current_salaries cs ON cs.employee_id = e.id pairs each current salary record with the employee's department. The inner join means a current salary record without a matching employee row would be dropped, but that case does not occur here.
GROUP BY e.department_id collapses the joined rows into one row per department. Departments with no current salary records on file do not appear, because they had no rows in the joined set.

Why this and not filter-after-join

The same numbers come out if the filter is pushed past the join: JOIN salaries s ON s.employee_id = e.id WHERE s.end_date IS NULL then GROUP BY. The narrowing happens in either case, but the CTE shape names it as a separate phase. The structural intent here is to keep the narrowing visible at the top of the query and the per-department calculation as a clean second step.

The trap

end_date IS NULL is the correct filter for "current." Writing end_date = NULL returns no rows at all, silently. The comparison = NULL is never true in SQL, not even when the value is null, because null is not a value that equals anything. IS NULL is the only spelling that detects the missing-value case.

You practiced narrowing salary records to the current set first, then averaging per department — separating the row-narrowing step from the per-department calculation.

Return each `department_id` and the average current salary across employees in that department

The shape

Clause by clause

Why this and not filter-after-join

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.