Salary Hike Percentage at the April 2025 Appraisal
Every salary change is a new row in SalaryRevision, effective from a date.
- Difficulty: Medium
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #231
Problem statement
Every salary change is a new row in SalaryRevision, effective from a date. The annual appraisal revisions all took effect on 2025-04-01, and the compensation team wants the hike each person got.
For every employee with a revision effective on 2025-04-01 and an earlier revision, return emp_id, previous_ctc (the CTC of the revision immediately before it), new_ctc and hike_pct = (new − previous) × 100 / previous, rounded to 2 decimals. Employees whose first ever revision is the April one (new joiners) are not listed; later revisions (say a July promotion) do not matter. Order the rows by hike_pct descending, then emp_id ascending.
Tables
Table: SalaryRevision
| Column | Type |
|---|---|
| emp_id | int |
| effective_from | date |
| annual_ctc | int |
| reason | enum(joining, appraisal, promotion, correction) |
Primary key: emp_id, effective_from.
One row per salary change. annual_ctc is the cost to company in rupees per year from effective_from until the next revision.
Examples
Example 1
SalaryRevision
| emp_id | effective_from | annual_ctc | reason |
|---|---|---|---|
| 601 | 2023-06-12 | 800000 | joining |
| 601 | 2024-04-01 | 880000 | appraisal |
| 601 | 2025-04-01 | 968000 | appraisal |
| 602 | 2024-01-08 | 1200000 | joining |
| 602 | 2025-04-01 | 1200000 | appraisal |
| 603 | 2025-04-01 | 650000 | joining |
| 604 | 2022-09-01 | 1500000 | joining |
| 604 | 2025-04-01 | 1725000 | appraisal |
| 604 | 2025-07-01 | 2000000 | promotion |
| 605 | 2024-11-04 | 900000 | joining |
Output
| emp_id | previous_ctc | new_ctc | hike_pct |
|---|---|---|---|
| 604 | 1500000 | 1725000 | 15 |
| 601 | 880000 | 968000 | 10 |
| 602 | 1200000 | 1200000 | 0 |
How to solve Salary Hike Percentage at the April 2025 Appraisal
The hike compares two rows of one employee: the April 2025 revision and the revision immediately before it. LAG(annual_ctc) OVER (PARTITION BY emp_id ORDER BY effective_from) puts the previous CTC on every row, in one sort of the table.
Order of operations matters. Window functions run after WHERE, so if you filtered to effective_from = '2025-04-01' in the same query, each employee would have one row and LAG would always be NULL. Compute LAG in a derived table first, then keep the April rows outside. A July promotion sits after April in the order, so it never becomes April's previous row.
New joiners whose first revision is in April get a NULL previous_ctc and are dropped by previous_ctc IS NOT NULL. The percentage is (new − previous) × 100 / previous, rounded to 2 decimals so MySQL's decimal division and other engines agree.
Without windows, find the previous revision's date with a correlated MAX(effective_from) … < '2025-04-01' and join back to read its CTC; the inner join drops employees with no earlier row by itself. That is one indexed lookup per April row.
Reference solution (MySQL)
SELECT emp_id, previous_ctc, annual_ctc AS new_ctc,
ROUND((annual_ctc - previous_ctc) * 100 / previous_ctc, 2) AS hike_pct
FROM (
SELECT emp_id, effective_from, annual_ctc,
LAG(annual_ctc) OVER (PARTITION BY emp_id ORDER BY effective_from) AS previous_ctc
FROM SalaryRevision
) r
WHERE effective_from = '2025-04-01' AND previous_ctc IS NOT NULL
ORDER BY hike_pct DESC, emp_idAnother way
SELECT a.emp_id, p.annual_ctc AS previous_ctc, a.annual_ctc AS new_ctc,
ROUND((a.annual_ctc - p.annual_ctc) * 100 / p.annual_ctc, 2) AS hike_pct
FROM SalaryRevision a
JOIN SalaryRevision p ON p.emp_id = a.emp_id
AND p.effective_from = (SELECT MAX(effective_from) FROM SalaryRevision x WHERE x.emp_id = a.emp_id AND x.effective_from < '2025-04-01')
WHERE a.effective_from = '2025-04-01'
ORDER BY hike_pct DESC, a.emp_id← Work Anniversaries Coming Up in October 2025 · Employees Averaging Less Than Eight Hours a Day →