Earned Leave Lapsing Above the Carry-Forward Cap

Easy
228.

Earned Leave Lapsing Above the Carry-Forward Cap

EasyBasics

MySQL

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

ColumnTypeValues
emp_idint
leave_typeenumearned, casual, sick
leave_yearint
entitledint
availedint

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

Example

The rows must come back in the order shown.

LeaveBalance

emp_idleave_typeleave_yearentitledavailed
301earned20244812
301casual2024124
302earned20244010
303earned20245216
303earned2023455
304sick2024450
305earned2024448
306earned20241818

Output

emp_idclosing_balancelapsing_days
301366
303366
305366
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.

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

ColumnType
emp_idint
leave_typeenum(earned, casual, sick)
leave_yearint
entitledint
availedint

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_idleave_typeleave_yearentitledavailed
301earned20244812
301casual2024124
302earned20244010
303earned20245216
303earned2023455
304sick2024450
305earned2024448
306earned20241818

Output

emp_idclosing_balancelapsing_days
301366
303366
305366

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_id

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