Towers Where Every Unit Cleared March Dues

Medium
365.

Towers Where Every Unit Cleared March Dues

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

ColumnType
unit_novarchar
towervarchar
monthly_chargeint

Primary key: unit_no.

monthly_charge is the unit's maintenance per month, in rupees.

Table: MaintenanceReceipt

ColumnType
receipt_idint
unit_novarchar
periodchar
amountint

Primary key: receipt_id.

period is the month paid for as 'YYYY-MM'; unit_no is always in ApartmentUnit.

Example

The rows must come back in the order shown.

ApartmentUnit

unit_notowermonthly_charge
T1-101Tower 14200
T1-102Tower 14200
T2-101Tower 25100
T2-102Tower 25100
T3-101Tower 33600
T4-101Tower 43900

MaintenanceReceipt

receipt_idunit_noperiodamount
1T1-1012025-034200
2T1-1022025-032000
3T1-1022025-032200
4T2-1012025-035100
5T2-1022025-035000
6T3-1012025-023600
7T4-1012025-034000

Output

towerunits
Tower 12
Tower 41
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.

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

ColumnType
unit_novarchar
towervarchar
monthly_chargeint

Primary key: unit_no.

monthly_charge is the unit's maintenance per month, in rupees.

Table: MaintenanceReceipt

ColumnType
receipt_idint
unit_novarchar
periodchar
amountint

Primary key: receipt_id.

period is the month paid for as 'YYYY-MM'; unit_no is always in ApartmentUnit.

Examples

Example 1

ApartmentUnit

unit_notowermonthly_charge
T1-101Tower 14200
T1-102Tower 14200
T2-101Tower 25100
T2-102Tower 25100
T3-101Tower 33600
T4-101Tower 43900

MaintenanceReceipt

receipt_idunit_noperiodamount
1T1-1012025-034200
2T1-1022025-032000
3T1-1022025-032200
4T2-1012025-035100
5T2-1022025-035000
6T3-1012025-023600
7T4-1012025-034000

Output

towerunits
Tower 12
Tower 41

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.tower

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

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