Salary Hike Percentage at the April 2025 Appraisal

Medium
231.

Salary Hike Percentage at the April 2025 Appraisal

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

ColumnTypeValues
emp_idint
effective_fromdate
annual_ctcint
reasonenumjoining, appraisal, promotion, correction

Primary key (together): 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.

Example

The rows must come back in the order shown.

SalaryRevision

emp_ideffective_fromannual_ctcreason
6012023-06-12800000joining
6012024-04-01880000appraisal
6012025-04-01968000appraisal
6022024-01-081200000joining
6022025-04-011200000appraisal
6032025-04-01650000joining
6042022-09-011500000joining
6042025-04-011725000appraisal
6042025-07-012000000promotion
6052024-11-04900000joining

Output

emp_idprevious_ctcnew_ctchike_pct
6041500000172500015
60188000096800010
602120000012000000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
effective_fromdate
annual_ctcint
reasonenum(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_ideffective_fromannual_ctcreason
6012023-06-12800000joining
6012024-04-01880000appraisal
6012025-04-01968000appraisal
6022024-01-081200000joining
6022025-04-011200000appraisal
6032025-04-01650000joining
6042022-09-011500000joining
6042025-04-011725000appraisal
6042025-07-012000000promotion
6052024-11-04900000joining

Output

emp_idprevious_ctcnew_ctchike_pct
6041500000172500015
60188000096800010
602120000012000000

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_id

Another 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 →