Leave Ledger Entries That Took the Balance Negative
Earned leave is kept as a ledger. carry_forward and accrual entries add days; availed and encashed entries subtract them.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #247
Problem statement
Earned leave is kept as a ledger. carry_forward and accrual entries add days; availed and encashed entries subtract them. The balance starts again from 0 every calendar year (the days carried in arrive as a carry_forward entry in January). Entries apply in order of entry_date, and entries on the same day in order of entry_id.
Payroll needs every entry after which that year's running balance is below zero (leave taken in advance). Return emp_id, entry_id, entry_date and balance_after. Order by emp_id, then entry_date, then entry_id.
Tables
Table: LeaveLedger
| Column | Type |
|---|---|
| entry_id | int |
| emp_id | int |
| entry_date | date |
| entry_type | enum(carry_forward, accrual, availed, encashed) |
| days | int |
Primary key: entry_id.
days is always positive; the type says whether it adds to or subtracts from the balance.
Examples
Example 1
LeaveLedger
| entry_id | emp_id | entry_date | entry_type | days |
|---|---|---|---|---|
| 1 | 31 | 2024-01-01 | carry_forward | 4 |
| 2 | 31 | 2024-01-31 | accrual | 2 |
| 3 | 31 | 2024-02-10 | availed | 7 |
| 4 | 31 | 2024-02-29 | accrual | 2 |
| 5 | 31 | 2024-12-20 | encashed | 2 |
| 6 | 31 | 2025-01-03 | availed | 1 |
| 7 | 32 | 2024-03-04 | availed | 2 |
| 8 | 32 | 2024-03-04 | accrual | 2 |
| 9 | 32 | 2024-03-29 | accrual | 2 |
| 10 | 32 | 2024-04-02 | availed | 2 |
Output
| emp_id | entry_id | entry_date | balance_after |
|---|---|---|---|
| 31 | 3 | 2024-02-10 | -1 |
| 31 | 5 | 2024-12-20 | -1 |
| 31 | 6 | 2025-01-03 | -1 |
| 32 | 7 | 2024-03-04 | -2 |
How to solve Leave Ledger Entries That Took the Balance Negative
First give each entry a sign: CASE WHEN entry_type IN ('carry_forward', 'accrual') THEN days ELSE -days END. The balance after an entry is then the running sum of those signed values for the same employee and the same year, up to and including that entry.
The window does exactly that: SUM(signed) OVER (PARTITION BY emp_id, YEAR(entry_date) ORDER BY entry_date, entry_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Partitioning by the year is the reset — January starts a new partition, so last December's balance is never carried silently. The order includes entry_id because same-day entries apply one at a time: on the example's 4 March an advance is availed before the month's accrual lands, and that intermediate negative balance is exactly what payroll wants to see. With the default RANGE frame both same-day rows would be summed together and the dip would vanish, hence the explicit ROWS frame.
Window results cannot be filtered in their own WHERE, so wrap the query and keep balance_after < 0. The alternative is the pre-window way: join each entry to every earlier-or-equal entry of the same employee and year, sum, and filter with HAVING — quadratic per employee-year, against one sort for the window.
Reference solution (MySQL)
SELECT emp_id, entry_id, entry_date, balance_after
FROM (
SELECT emp_id, entry_id, entry_date,
SUM(CASE WHEN entry_type IN ('carry_forward', 'accrual') THEN days ELSE -days END)
OVER (PARTITION BY emp_id, YEAR(entry_date) ORDER BY entry_date, entry_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_after
FROM LeaveLedger
) t
WHERE balance_after < 0
ORDER BY emp_id, entry_date, entry_idAnother way
SELECT l.emp_id, l.entry_id, l.entry_date,
SUM(IF(p.entry_type IN ('availed', 'encashed'), -p.days, p.days)) AS balance_after
FROM LeaveLedger l
JOIN LeaveLedger p ON p.emp_id = l.emp_id AND YEAR(p.entry_date) = YEAR(l.entry_date)
AND (p.entry_date < l.entry_date OR (p.entry_date = l.entry_date AND p.entry_id <= l.entry_id))
GROUP BY l.emp_id, l.entry_id, l.entry_date
HAVING SUM(IF(p.entry_type IN ('availed', 'encashed'), -p.days, p.days)) < 0
ORDER BY l.emp_id, l.entry_date, l.entry_id← 90- and 180-Day Retention of 2024 Hires by Quarter · Machines Overdue for Calibration on Audit Day →