Library Members Blocked for Unpaid Fines

Medium
81.

Library Members Blocked for Unpaid Fines

MediumJoinsAggregation

MySQL

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

ColumnType
member_idint
namevarchar
branchvarchar

Primary key: member_id.

One row per library member.

Table: fines

ColumnTypeValues
fine_idint
member_idint
reasonenumoverdue, damaged, lost
amountint
paidbool

Primary key: fine_id.

One row per fine, in rupees. paid is 1 once settled at the counter, otherwise 0.

Example

The rows must come back in the order shown.

members

member_idnamebranch
1Aisha KhanCentral
2Vivaan RaoJayanagar
3Simran KaurCentral
4John MathewIndiranagar
5Anjali MenonMalleshwaram

fines

fine_idmember_idreasonamountpaid
11overdue1200
21damaged1500
32lost4501
42overdue600
53overdue2000
64lost2700
75overdue901

Output

member_idnameunpaid_total
1Aisha Khan270
4John Mathew270
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.

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

ColumnType
member_idint
namevarchar
branchvarchar

Primary key: member_id.

One row per library member.

Table: fines

ColumnType
fine_idint
member_idint
reasonenum(overdue, damaged, lost)
amountint
paidbool

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_idnamebranch
1Aisha KhanCentral
2Vivaan RaoJayanagar
3Simran KaurCentral
4John MathewIndiranagar
5Anjali MenonMalleshwaram

fines

fine_idmember_idreasonamountpaid
11overdue1200
21damaged1500
32lost4501
42overdue600
53overdue2000
64lost2700
75overdue901

Output

member_idnameunpaid_total
1Aisha Khan270
4John Mathew270

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_id

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

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