First Day a Member's Fine Balance Reached ₹500

Hard
93.

First Day a Member's Fine Balance Reached ₹500

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

ColumnTypeValues
entry_idint
member_idint
entry_datedate
entry_typeenumfine, payment, waiver
amountint

Primary key: entry_id.

One row per ledger entry; amount is always positive, in rupees.

Example

The rows must come back in the order shown.

fine_ledger

entry_idmember_identry_dateentry_typeamount
112024-01-05fine300
212024-01-20fine150
312024-02-02payment100
412024-02-10fine250
522024-01-08fine500
632024-01-10fine400
732024-01-10waiver200
832024-03-01fine200
942024-02-14fine450
1042024-02-14fine120
1 more row

Output

member_idblocked_onbalance_then
12024-02-10600
22024-01-08500
42024-02-14570
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.

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

ColumnType
entry_idint
member_idint
entry_datedate
entry_typeenum(fine, payment, waiver)
amountint

Primary key: entry_id.

One row per ledger entry; amount is always positive, in rupees.

Examples

Example 1

fine_ledger

entry_idmember_identry_dateentry_typeamount
112024-01-05fine300
212024-01-20fine150
312024-02-02payment100
412024-02-10fine250
522024-01-08fine500
632024-01-10fine400
732024-01-10waiver200
832024-03-01fine200
942024-02-14fine450
1042024-02-14fine120
1142024-02-20fine300

Output

member_idblocked_onbalance_then
12024-02-10600
22024-01-08500
42024-02-14570

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_id

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