Window Functions for Salary Trends | Dataford Interview Questions

Your question is Window Functions for Salary Trends. Start with the requirements and the three 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

Paylocity wants a quarterly compensation report for workforce analytics. Write a PostgreSQL query that ranks employees within their department for each quarter and measures each employee's change from the previous recorded quarter.

Requirements

Join employees to departments and quarterly salary history.
Derive the quarter from effective_date.
Use RANK() to rank salaries within each department and quarter, with higher salaries ranked first.
Use LAG() to retrieve each employee's prior-quarter salary and calculate the absolute salary change. The first recorded quarter for an employee should have a NULL change.
Return results ordered by department, quarter, and employee ID.

Assume one salary record per employee per quarter. Compare each employee with their previous recorded salary quarter.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department display name

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(120)	Employee full name
department_id	INT	References departments.department_id
job_title	VARCHAR(120)	Employee's current job title

salary_history

Column	Type	Description
salary_history_idPK	INT	Unique salary history record
employee_id	INT	References employees.employee_id
effective_date	DATE	Salary effective date
salary_amount	NUMERIC(12,2)	Annual salary amount