Towers Where Every Unit Cleared March Dues — SQL Problem
A gated community wants to thank the towers where every unit paid its full maintenance for '2025-03'.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #365
Problem statement
A gated community wants to thank the towers where every unit paid its full maintenance for '2025-03'. A unit may pay in several receipts; it has cleared the month when its receipts for '2025-03' add up to at least its monthly_charge. A unit with no receipt for the month has not cleared it.
Return tower and units (the number of units in it) for every tower where all units cleared the month, ordered by tower.
Tables
Table: ApartmentUnit
| Column | Type |
|---|---|
| unit_no | varchar |
| tower | varchar |
| monthly_charge | int |
Primary key: unit_no.
monthly_charge is the unit's maintenance per month, in rupees.
Table: MaintenanceReceipt
| Column | Type |
|---|---|
| receipt_id | int |
| unit_no | varchar |
| period | char |
| amount | int |
Primary key: receipt_id.
period is the month paid for as 'YYYY-MM'; unit_no is always in ApartmentUnit.
Examples
Example 1
ApartmentUnit
| unit_no | tower | monthly_charge |
|---|---|---|
| T1-101 | Tower 1 | 4200 |
| T1-102 | Tower 1 | 4200 |
| T2-101 | Tower 2 | 5100 |
| T2-102 | Tower 2 | 5100 |
| T3-101 | Tower 3 | 3600 |
| T4-101 | Tower 4 | 3900 |
MaintenanceReceipt
| receipt_id | unit_no | period | amount |
|---|---|---|---|
| 1 | T1-101 | 2025-03 | 4200 |
| 2 | T1-102 | 2025-03 | 2000 |
| 3 | T1-102 | 2025-03 | 2200 |
| 4 | T2-101 | 2025-03 | 5100 |
| 5 | T2-102 | 2025-03 | 5000 |
| 6 | T3-101 | 2025-02 | 3600 |
| 7 | T4-101 | 2025-03 | 4000 |
Output
| tower | units |
|---|---|
| Tower 1 | 2 |
| Tower 4 | 1 |
How to solve Towers Where Every Unit Cleared March Dues
This is a "for all" question — a tower qualifies only if every unit cleared — and SQL answers "for all" by checking that no counterexample exists. The counterexample is a unit whose March receipts sum to less than its charge. Summing per unit is required because units pay in instalments: T1-102 paid 2,000 + 2,200 = 4,200 and cleared, while T2-102 paid 5,000 of 5,100 and kept Tower 2 off the list.
A unit without any March receipt must count as paying 0 — that is the COALESCE(SUM(…), 0) in the correlated subquery, or COALESCE(p.paid, 0) after a LEFT JOIN to pre-aggregated payments. Tower 3 paid only for February, so it fails; Tower 4 overpaid, which still clears.
Three equivalent shapes: count failing units in HAVING and require zero; NOT EXISTS a failing unit in the same tower; or take the MIN of a cleared flag per tower and require 1. Pre-aggregating receipts once is the cheapest on a large ledger.
Reference solution (MySQL)
SELECT u.tower, COUNT(*) AS units
FROM ApartmentUnit u
GROUP BY u.tower
HAVING SUM(CASE WHEN u.monthly_charge <= (
SELECT COALESCE(SUM(r.amount), 0) FROM MaintenanceReceipt r
WHERE r.unit_no = u.unit_no AND r.period = '2025-03'
) THEN 0 ELSE 1 END) = 0
ORDER BY u.towerAnother way
SELECT tower, COUNT(*) AS units FROM ApartmentUnit t WHERE NOT EXISTS (SELECT 1 FROM ApartmentUnit u WHERE u.tower = t.tower AND u.monthly_charge > (SELECT IFNULL(SUM(amount), 0) FROM MaintenanceReceipt r WHERE r.unit_no = u.unit_no AND r.period = '2025-03')) GROUP BY tower ORDER BY towerAnother way
SELECT u.tower, COUNT(*) AS units FROM ApartmentUnit u LEFT JOIN (SELECT unit_no, SUM(amount) AS paid FROM MaintenanceReceipt WHERE period = '2025-03' GROUP BY unit_no) p ON p.unit_no = u.unit_no GROUP BY u.tower HAVING MIN(CASE WHEN COALESCE(p.paid, 0) >= u.monthly_charge THEN 1 ELSE 0 END) = 1 ORDER BY u.tower← Each Broker's Best Month by Deal Value · Tenants Late on Rent Three Months Running →