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.
