Leave Ledger Entries That Took the Balance Negative

Hard
247.

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. 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

ColumnTypeValues
entry_idint
emp_idint
entry_datedate
entry_typeenumcarry_forward, accrual, availed, encashed
daysint

Primary key: entry_id.

days is always positive; the type says whether it adds to or subtracts from the balance.

Example

The rows must come back in the order shown.

LeaveLedger

entry_idemp_identry_dateentry_typedays
1312024-01-01carry_forward4
2312024-01-31accrual2
3312024-02-10availed7
4312024-02-29accrual2
5312024-12-20encashed2
6312025-01-03availed1
7322024-03-04availed2
8322024-03-04accrual2
9322024-03-29accrual2
10322024-04-02availed2

Output

emp_identry_identry_datebalance_after
3132024-02-10-1
3152024-12-20-1
3162025-01-03-1
3272024-03-04-2
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.

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

ColumnType
entry_idint
emp_idint
entry_datedate
entry_typeenum(carry_forward, accrual, availed, encashed)
daysint

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_idemp_identry_dateentry_typedays
1312024-01-01carry_forward4
2312024-01-31accrual2
3312024-02-10availed7
4312024-02-29accrual2
5312024-12-20encashed2
6312025-01-03availed1
7322024-03-04availed2
8322024-03-04accrual2
9322024-03-29accrual2
10322024-04-02availed2

Output

emp_identry_identry_datebalance_after
3132024-02-10-1
3152024-12-20-1
3162025-01-03-1
3272024-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_id

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