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.
Output details
- Return columns in this order:
department_name, quarter_start, employee_id, employee_name, salary_amount, salary_rank, prior_salary, salary_change.
- Represent each derived quarter as its first calendar day, such as
2025-01-01.
- Sort all output columns ascending by
department_name, quarter_start, and employee_id.
- Preserve duplicate salary records for an employee and quarter; do not deduplicate them.
- Order prior-salary records by
quarter_start only, without an additional tie-breaker for duplicate quarters.
- Do not round or otherwise alter salary values or calculated changes.
