Year-over-Year Salary Growth (LAG on History)
From salaries_history, for each employee ordered by effective_date, show the previous salary and the YoY growth % vs that previous record (rounded to 2; NULL for the first record). Return employee_id, effective_date, salary, prev_salary, yoy_growth_pct — ordered by employee_id, effective_date.
- Window functions
- NULL handling
- Sorting
Interview brief
Understand the request
From salaries_history, for each employee ordered by effective_date, show the previous salary and the YoY growth % vs that previous record (rounded to 2; NULL for the first record). Return employee_id, effective_date, salary, prev_salary, yoy_growth_pct — ordered by employee_id, effective_date.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
salaries_history
employee_idINTEGERsalaryINTEGEReffective_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
LAG(salary) over (PARTITION BY employee_id ORDER BY effective_date) gives the prior record.
Hint 2
Percent change = (current − prev) / prev × 100. Use NULLIF(prev, 0) to avoid divide-by-zero.
Hint 3
The first record per employee has no prior → prev_salary and growth are NULL.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT employee_id, effective_date, salary, LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS prev_salary, ROUND((salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date)) * 100.0 / NULLIF(LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date), 0), 2) AS yoy_growth_pct FROM salaries_history ORDER BY employee_id, effective_date;Why this works
Period-over-period growth is the most-requested finance metric. LAG turns it into a one-liner: no self-join on (year = year − 1). The NULLIF guard and the NULL-on-first-row behavior are the details interviewers probe.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | effective_date | salary | prev_salary | yoy_growth_pct |
|---|---|---|---|---|
| 4 | 2020-02-14 | 110000 | NULL | NULL |
| 4 | 2021-02-14 | 120000 | 110000 | 9.09 |
| 4 | 2022-02-14 | 130000 | 120000 | 8.33 |
| 5 | 2020-05-18 | 105000 | NULL | NULL |
| 5 | 2021-05-18 | 115000 | 105000 | 9.52 |
| 5 | 2022-05-18 | 125000 | 115000 | 8.7 |
| 6 | 2021-01-10 | 85000 | NULL | NULL |
| 6 | 2022-01-10 | 95000 | 85000 | 11.76 |
| 8 | 2020-08-15 | 95000 | NULL | NULL |
| 8 | 2021-08-15 | 105000 | 95000 | 10.53 |
Previewing 10 of 13 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: