First and Latest Salary Per Employee (FIRST_VALUE / LAST_VALUE)

Interview brief

Understand the request

For each employee in salaries_history, show every record alongside their first-ever salary and their latest salary. Beware the LAST_VALUE frame trap — use a full-partition frame. Return employee_id, effective_date, salary, first_salary, latest_salary — ordered by employee_id, effective_date.

Data you will use

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

Hints, when you need them

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

Verified SQL answer

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

SELECT employee_id, effective_date, salary, FIRST_VALUE(salary) OVER (PARTITION BY employee_id ORDER BY effective_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_salary, LAST_VALUE(salary) OVER (PARTITION BY employee_id ORDER BY effective_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_salary FROM salaries_history ORDER BY employee_id, effective_date;

Expected result

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

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