Library Members Blocked for Unpaid Fines — SQL Joins Problem
The library stops issuing books to a member whose unpaid fines add up to more than ₹200. Fines that have been paid (paid = 1) no longer count.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #81
Problem statement
The library stops issuing books to a member whose unpaid fines add up to more than ₹200. Fines that have been paid (paid = 1) no longer count.
Return every blocked member with member_id, name and unpaid_total (the sum of their unpaid fines). A member at exactly ₹200 is not blocked. Order by unpaid_total descending, then member_id ascending.
Tables
Table: members
| Column | Type |
|---|---|
| member_id | int |
| name | varchar |
| branch | varchar |
Primary key: member_id.
One row per library member.
Table: fines
| Column | Type |
|---|---|
| fine_id | int |
| member_id | int |
| reason | enum(overdue, damaged, lost) |
| amount | int |
| paid | bool |
Primary key: fine_id.
One row per fine, in rupees. paid is 1 once settled at the counter, otherwise 0.
Examples
Example 1
members
| member_id | name | branch |
|---|---|---|
| 1 | Aisha Khan | Central |
| 2 | Vivaan Rao | Jayanagar |
| 3 | Simran Kaur | Central |
| 4 | John Mathew | Indiranagar |
| 5 | Anjali Menon | Malleshwaram |
fines
| fine_id | member_id | reason | amount | paid |
|---|---|---|---|---|
| 1 | 1 | overdue | 120 | 0 |
| 2 | 1 | damaged | 150 | 0 |
| 3 | 2 | lost | 450 | 1 |
| 4 | 2 | overdue | 60 | 0 |
| 5 | 3 | overdue | 200 | 0 |
| 6 | 4 | lost | 270 | 0 |
| 7 | 5 | overdue | 90 | 1 |
Output
| member_id | name | unpaid_total |
|---|---|---|
| 1 | Aisha Khan | 270 |
| 4 | John Mathew | 270 |
How to solve Library Members Blocked for Unpaid Fines
The question combines a join, a filter on rows and a filter on groups. Rows: only unpaid fines (paid = 0) count, so they are filtered in WHERE before grouping. Groups: one per member, with SUM(f.amount) as the outstanding balance. The block rule is a condition on that sum, which can only be tested after aggregation — that is what HAVING SUM(f.amount) > 200 is for.
Members with no fines, or only paid ones, have no unpaid rows and form no group, so they never appear. The strict > keeps the member sitting at exactly ₹200 (Simran in the example) off the list.
The alternatives compute the same balance differently: a correlated scalar subquery per member (NULL for a member with nothing unpaid, and NULL > 200 is not true), or conditional summing with CASE, which lets every fine into the group but adds zero for paid ones. All three are linear with an index on fines(member_id).
Reference solution (MySQL)
SELECT m.member_id, m.name, SUM(f.amount) AS unpaid_total
FROM members m
JOIN fines f ON f.member_id = m.member_id
WHERE f.paid = 0
GROUP BY m.member_id, m.name
HAVING SUM(f.amount) > 200
ORDER BY unpaid_total DESC, m.member_idAnother way
SELECT member_id, name, unpaid_total FROM (
SELECT m.member_id, m.name, (SELECT SUM(amount) FROM fines f WHERE f.member_id = m.member_id AND f.paid = 0) AS unpaid_total
FROM members m
) t WHERE unpaid_total > 200
ORDER BY unpaid_total DESC, member_idAnother way
SELECT m.member_id, m.name, SUM(CASE WHEN f.paid = 0 THEN f.amount ELSE 0 END) AS unpaid_total
FROM members m JOIN fines f ON f.member_id = m.member_id
GROUP BY m.member_id, m.name
HAVING SUM(CASE WHEN f.paid = 0 THEN f.amount ELSE 0 END) > 200
ORDER BY unpaid_total DESC, m.member_id← Cash Donations and 80G Eligibility · Grievances Resolved Within the Seven-Day SLA →