CTEs & Window Functions SQL practice problemHardVerified answer

3-Row Moving Average of Salaries (Explicit ROWS Frame)

Within each department, ordered by hire_date, compute the moving average of salary over the current row and the two before it (a 3-row trailing window). Return department_name, first_name, last_name, hire_date, salary, moving_avg_3 (rounded to 2) — ordered by department_name, hire_date, employee_id.

  • Window functions
  • Joins
  • Aggregation
  • Sorting

Interview brief

Understand the request

Within each department, ordered by hire_date, compute the moving average of salary over the current row and the two before it (a 3-row trailing window). Return department_name, first_name, last_name, hire_date, salary, moving_avg_3 (rounded to 2) — ordered by department_name, hire_date, employee_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • hire_dateDATE
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = at most 3 rows (this one + two before).

Hint 2

Early rows in each partition average over fewer than 3 rows — that is expected.

Hint 3

Change the number to widen/narrow the window (e.g. 6 PRECEDING for a 7-row average).

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_name, e.first_name, e.last_name, e.hire_date, e.salary, ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.hire_date, e.employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.hire_date, e.employee_id;

Why this works

Moving averages smooth noisy series. The explicit ROWS frame is what makes it a true N-row trailing average; without it you get the default (often a running average). Frames are the single most under-used part of window functions.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_namefirst_namelast_namehire_datesalarymoving_avg_3
EngineeringMichaelScott2019-03-20180000180000
EngineeringDavidMartinez2020-02-14130000155000
EngineeringEmilyChen2020-05-18125000145000
EngineeringRobertWilliams2021-01-1095000116666.67
EngineeringLisaAnderson2021-03-2290000103333.33
EngineeringAmandaWhite2022-03-107500086666.67
ExecutiveJenniferKing2018-01-15250000250000
FinanceJessicaMoore2020-04-08105000105000
FinanceDanielLee2021-07-157000087500
Human ResourcesPatriciaDavis2019-11-059500095000

Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: