Delta From Starting Salary (FIRST_VALUE)

Interview brief

Understand the request

For each salaries_history record, show how far the salary has moved from the employee's very first recorded salary. Return employee_id, effective_date, salary, starting_salary, delta_from_start — 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) AS starting_salary, salary - FIRST_VALUE(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS delta_from_start 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.