SQL Date-Based Employee Lookup | Dataford Interview Questions

Your question is SQL Date-Based Employee Lookup. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Abzooba maintains employee salary history with an effective date for each salary change. Write a PostgreSQL query that reports each employee's latest salary effective on the current date and the salary immediately before it.

Requirements

Consider only salary records where effective_from <= CURRENT_DATE.
Return every employee, including employees without an effective salary record.
Use a window function to identify the latest salary and its predecessor for each employee.
Return the employee ID, name, current salary, previous salary, and current salary effective date, ordered by employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department	VARCHAR(80)	Abzooba department

salary_history

Column	Type	Description
salary_history_idPK	INTEGER	Unique salary history identifier
employee_id	INTEGER	References employees.employee_id
salary	NUMERIC(12,2)	Salary amount
effective_from	DATE	Date the salary became effective
effective_to	DATE	Date the salary ended, if known