First Day a Member's Fine Balance Reached ₹500 — SQL Problem
Every fine, payment and waiver on a library account is an entry in fine_ledger.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #93
Problem statement
Every fine, payment and waiver on a library account is an entry in fine_ledger. A fine raises the balance; a payment or a waiver lowers it (a payment can leave the account in credit, below zero). Entries are applied in date order, and entries on the same date in entry_id order.
A member is reported to the branch head on the first entry after which their running balance is ₹500 or more. Return member_id, blocked_on (that entry's date) and balance_then (the balance right after it) for every member whose balance ever reached ₹500, ordered by member_id.
Tables
Table: fine_ledger
| Column | Type |
|---|---|
| entry_id | int |
| member_id | int |
| entry_date | date |
| entry_type | enum(fine, payment, waiver) |
| amount | int |
Primary key: entry_id.
One row per ledger entry; amount is always positive, in rupees.
Examples
Example 1
fine_ledger
| entry_id | member_id | entry_date | entry_type | amount |
|---|---|---|---|---|
| 1 | 1 | 2024-01-05 | fine | 300 |
| 2 | 1 | 2024-01-20 | fine | 150 |
| 3 | 1 | 2024-02-02 | payment | 100 |
| 4 | 1 | 2024-02-10 | fine | 250 |
| 5 | 2 | 2024-01-08 | fine | 500 |
| 6 | 3 | 2024-01-10 | fine | 400 |
| 7 | 3 | 2024-01-10 | waiver | 200 |
| 8 | 3 | 2024-03-01 | fine | 200 |
| 9 | 4 | 2024-02-14 | fine | 450 |
| 10 | 4 | 2024-02-14 | fine | 120 |
| 11 | 4 | 2024-02-20 | fine | 300 |
Output
| member_id | blocked_on | balance_then |
|---|---|---|
| 1 | 2024-02-10 | 600 |
| 2 | 2024-01-08 | 500 |
| 4 | 2024-02-14 | 570 |
How to solve First Day a Member's Fine Balance Reached ₹500
A balance is a running total of signed amounts. CASE WHEN entry_type = 'fine' THEN amount ELSE -amount END turns every entry into its effect on the account, and SUM(...) OVER (PARTITION BY member_id ORDER BY entry_date, entry_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is the balance right after each entry. The explicit ROWS frame matters: with the default RANGE frame two entries on the same date would both see each other's amounts. Member 4 in the example fined twice on 14 February shows why — after the first entry the balance is 450, after the second 570.
Then the question is "first row meeting a condition": filter the rows with a balance of 500 or more and number them per member in the same order; row 1 is the crossing. A payment can delay the crossing (member 1 reached ₹450, paid ₹100 and crossed only with the fine of 10 February, at ₹600), only the first crossing is reported, and a member who never reaches 500 has no qualifying rows. Exactly 500 counts, as member 2 shows.
Without windows, a correlated subquery sums every entry up to and including the current one, and NOT EXISTS keeps the crossing with no earlier crossing — quadratic per member, but the same answer.
Reference solution (MySQL)
WITH running AS (
SELECT member_id, entry_id, entry_date,
SUM(CASE WHEN entry_type = 'fine' THEN amount ELSE -amount END)
OVER (PARTITION BY member_id ORDER BY entry_date, entry_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM fine_ledger
),
crossings AS (
SELECT member_id, entry_date, balance,
ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY entry_date, entry_id) AS nth
FROM running
WHERE balance >= 500
)
SELECT member_id, entry_date AS blocked_on, balance AS balance_then
FROM crossings
WHERE nth = 1
ORDER BY member_idAnother way
WITH bal AS (
SELECT l.member_id, l.entry_id, l.entry_date,
(SELECT SUM(IF(x.entry_type = 'fine', x.amount, -x.amount)) FROM fine_ledger x
WHERE x.member_id = l.member_id AND (x.entry_date < l.entry_date OR (x.entry_date = l.entry_date AND x.entry_id <= l.entry_id))) AS balance
FROM fine_ledger l
)
SELECT b.member_id, b.entry_date AS blocked_on, b.balance AS balance_then
FROM bal b
WHERE b.balance >= 500
AND NOT EXISTS (SELECT 1 FROM bal c WHERE c.member_id = b.member_id AND c.balance >= 500
AND (c.entry_date < b.entry_date OR (c.entry_date = b.entry_date AND c.entry_id < b.entry_id)))
ORDER BY b.member_id← Reservation Holders to Notify After Today's Returns · Median Days to Close Service Requests by Department →