Earned Leave Lapsing Above the Carry-Forward Cap
Company policy lets employees carry at most 30 days of earned leave into the next year; anything above that lapses on 31 December.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #228
Problem statement
Company policy lets employees carry at most 30 days of earned leave into the next year; anything above that lapses on 31 December. Casual and sick leave never carry forward and are not part of this report.
For the 2024 earned-leave balances, return every employee whose closing balance (entitled - availed) is more than 30, with the columns emp_id, closing_balance and lapsing_days (the closing balance minus 30). A balance of exactly 30 loses nothing and is not listed. Order the rows by lapsing_days descending, then emp_id ascending.
Tables
Table: LeaveBalance
| Column | Type |
|---|---|
| emp_id | int |
| leave_type | enum(earned, casual, sick) |
| leave_year | int |
| entitled | int |
| availed | int |
Primary key: emp_id, leave_type, leave_year.
One row per employee, leave type and calendar year. entitled already includes the days carried in from the year before.
Examples
Example 1
LeaveBalance
| emp_id | leave_type | leave_year | entitled | availed |
|---|---|---|---|---|
| 301 | earned | 2024 | 48 | 12 |
| 301 | casual | 2024 | 12 | 4 |
| 302 | earned | 2024 | 40 | 10 |
| 303 | earned | 2024 | 52 | 16 |
| 303 | earned | 2023 | 45 | 5 |
| 304 | sick | 2024 | 45 | 0 |
| 305 | earned | 2024 | 44 | 8 |
| 306 | earned | 2024 | 18 | 18 |
Output
| emp_id | closing_balance | lapsing_days |
|---|---|---|
| 301 | 36 | 6 |
| 303 | 36 | 6 |
| 305 | 36 | 6 |
How to solve Earned Leave Lapsing Above the Carry-Forward Cap
A filter with arithmetic. Keep the rows for leave_type = 'earned' in leave_year = 2024, compute the closing balance as entitled - availed, and keep it only when it is strictly above the cap of 30 — a balance of exactly 30 carries over in full.
The lapsing days are the closing balance minus 30. One detail trips people up: a WHERE clause is evaluated before the SELECT list, so it cannot refer to the alias closing_balance. Either repeat the expression in WHERE (the solution) or compute it in a derived table and filter outside, where the alias is a real column (the alternative).
Ordering by lapsing_days descending is the same as ordering by the closing balance descending, since one is the other minus a constant; emp_id breaks ties. The primary key (emp_id, leave_type, leave_year) guarantees one row per employee for that type and year, so no aggregation is needed. A single scan.
Reference solution (MySQL)
SELECT emp_id, entitled - availed AS closing_balance, entitled - availed - 30 AS lapsing_days
FROM LeaveBalance
WHERE leave_type = 'earned' AND leave_year = 2024 AND entitled - availed > 30
ORDER BY lapsing_days DESC, emp_idAnother way
SELECT emp_id, closing_balance, closing_balance - 30 AS lapsing_days
FROM (SELECT emp_id, entitled - availed AS closing_balance FROM LeaveBalance WHERE leave_type = 'earned' AND leave_year = 2024) b
WHERE closing_balance > 30
ORDER BY closing_balance DESC, emp_id← Late Punch-Ins per Employee in March 2025 · Referral Bonus Owed to Each Employee →