CTEs & Window Functions SQL practice problemMediumVerified answer

Compare to Previous & Next Hire (LAG / LEAD)

Return each employee with the previous and next employee's salary using LAG and LEAD.

CTEs
Window functions
Joins
Sorting

Interview brief

Understand the request

Compensation comparison analyst The salary movement analysis shows each employee's pay alongside the previous and next employee's salary in the list.

For each employee, ordered by hire_date within their department, show the previous and next hired employee's salary, plus the salary change vs the previous hire. Return department_name, first_name, last_name, hire_date, salary, prev_emp_salary, next_emp_salary, salary_change — ordered by department_name, hire_date, employee_id.

Return

Return employee_name, salary, prev_salary, and next_salary.

Constraints

Use LAG(salary) and LEAD(salary) as window functions ordered by salary.

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

LAG(x) returns x from the PREVIOUS row; LEAD(x) from the NEXT row (NULL at the edges).

Hint 2

The first row in each partition has NULL prev (and last has NULL next) — that propagates to salary_change.

Hint 3

You can pass an offset and default: LAG(x, 2, 0) looks back two rows, defaulting to 0.

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, LAG(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.hire_date, e.employee_id) AS prev_emp_salary, LEAD(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.hire_date, e.employee_id) AS next_emp_salary, e.salary - LAG(e.salary) OVER (PARTITION BY e.department_id ORDER BY e.hire_date, e.employee_id) AS salary_change 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

LAG/LEAD are the 'look at neighboring rows' functions — the foundation of period-over-period analysis (Q19), delta detection, and gaps-and-islands (Q20). No self-join required.

Expected result

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

department_name	first_name	last_name	hire_date	salary	prev_emp_salary	next_emp_salary	salary_change
Engineering	Michael	Scott	2019-03-20	180000	NULL	130000	NULL
Engineering	David	Martinez	2020-02-14	130000	180000	125000	-50000
Engineering	Emily	Chen	2020-05-18	125000	130000	95000	-5000
Engineering	Robert	Williams	2021-01-10	95000	125000	90000	-30000
Engineering	Lisa	Anderson	2021-03-22	90000	95000	75000	-5000
Engineering	Amanda	White	2022-03-10	75000	90000	NULL	-15000
Executive	Jennifer	King	2018-01-15	250000	NULL	NULL	NULL
Finance	Jessica	Moore	2020-04-08	105000	NULL	70000	NULL
Finance	Daniel	Lee	2021-07-15	70000	105000	NULL	-35000
Human Resources	Patricia	Davis	2019-11-05	95000	NULL	65000	NULL

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: