Flats Owing More Than Their Wing's Average — SQL Problem
A housing society keeps a dues ledger: maintenance charges raised on a flat and payments received from it.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Joins
- Dialect: MySQL
- Problem: #360
Problem statement
A housing society keeps a dues ledger: maintenance charges raised on a flat and payments received from it. A flat's outstanding is its total charges minus its total payments (negative when it paid in advance; 0 for a flat with no ledger entries).
Return the flats whose outstanding is strictly greater than the average outstanding of all flats in the same wing (flats without entries count as 0 in that average). Columns: flat_no, wing, outstanding and wing_average rounded to 2 decimals, ordered by wing, then outstanding descending, then flat_no.
Tables
Table: SocietyFlat
| Column | Type |
|---|---|
| flat_no | varchar |
| wing | char |
| owner_name | varchar |
Primary key: flat_no.
Table: DuesLedger
| Column | Type |
|---|---|
| entry_id | int |
| flat_no | varchar |
| entry_type | enum(charge, payment) |
| amount | int |
Primary key: entry_id.
amount is always positive; entry_type says whether it was billed or received. flat_no is always in SocietyFlat.
Examples
Example 1
SocietyFlat
| flat_no | wing | owner_name |
|---|---|---|
| A-101 | A | Priya Menon |
| A-102 | A | Rohan Gupta |
| A-103 | A | Saanvi Rao |
| B-101 | B | Farhan Khan |
| B-102 | B | Emma Das |
DuesLedger
| entry_id | flat_no | entry_type | amount |
|---|---|---|---|
| 1 | A-101 | charge | 9000 |
| 2 | A-101 | payment | 3000 |
| 3 | A-102 | charge | 9000 |
| 4 | A-102 | payment | 9000 |
| 5 | B-101 | charge | 6000 |
| 6 | B-102 | charge | 6000 |
| 7 | B-102 | payment | 6000 |
| 8 | B-101 | payment | 1000 |
| 9 | A-103 | charge | 4500 |
Output
| flat_no | wing | outstanding | wing_average |
|---|---|---|---|
| A-101 | A | 6000 | 3500 |
| A-103 | A | 4500 | 3500 |
| B-101 | B | 5000 | 2500 |
How to solve Flats Owing More Than Their Wing's Average
Two levels of aggregation, so do them one at a time. First, one balance per flat: LEFT JOIN the ledger to SocietyFlat and sum the entries with a sign — +amount for a charge, -amount for a payment. The LEFT JOIN plus COALESCE(…, 0) gives a flat with no entries an outstanding of 0, which matters because the statement counts those flats in the wing's average.
Second, compare each flat with its wing. A correlated subquery (SELECT AVG(outstanding) FROM balance WHERE wing = b.wing) recomputes the wing's average for each flat; a grouped CTE joined back, or AVG(outstanding) OVER (PARTITION BY wing), computes it once per wing. In the example wing A averages (6000 + 0 + 4500) / 3 = 3500, so A-101 and A-103 owe more than average.
Compare against the unrounded average and round only for display, or a flat equal to a rounded figure could slip through. The comparison is strict, so a wing where every flat owes the same returns nothing.
Reference solution (MySQL)
WITH balance AS (
SELECT f.flat_no, f.wing,
COALESCE(SUM(CASE WHEN d.entry_type = 'charge' THEN d.amount ELSE -d.amount END), 0) AS outstanding
FROM SocietyFlat f
LEFT JOIN DuesLedger d ON d.flat_no = f.flat_no
GROUP BY f.flat_no, f.wing
)
SELECT b.flat_no, b.wing, b.outstanding,
ROUND((SELECT AVG(x.outstanding) FROM balance x WHERE x.wing = b.wing), 2) AS wing_average
FROM balance b
WHERE b.outstanding > (SELECT AVG(x.outstanding) FROM balance x WHERE x.wing = b.wing)
ORDER BY b.wing, b.outstanding DESC, b.flat_noAnother way
SELECT flat_no, wing, outstanding, ROUND(wing_avg, 2) AS wing_average FROM (SELECT flat_no, wing, outstanding, AVG(outstanding) OVER (PARTITION BY wing) AS wing_avg FROM (SELECT f.flat_no, f.wing, IFNULL(SUM(IF(d.entry_type = 'charge', d.amount, -d.amount)), 0) AS outstanding FROM SocietyFlat f LEFT JOIN DuesLedger d ON d.flat_no = f.flat_no GROUP BY f.flat_no, f.wing) b) t WHERE outstanding > wing_avg ORDER BY wing, outstanding DESC, flat_noAnother way
WITH bal AS (SELECT f.flat_no, f.wing, COALESCE((SELECT SUM(amount) FROM DuesLedger d WHERE d.flat_no = f.flat_no AND d.entry_type = 'charge'), 0) - COALESCE((SELECT SUM(amount) FROM DuesLedger d WHERE d.flat_no = f.flat_no AND d.entry_type = 'payment'), 0) AS outstanding FROM SocietyFlat f), wavg AS (SELECT wing, AVG(outstanding) AS a FROM bal GROUP BY wing) SELECT b.flat_no, b.wing, b.outstanding, ROUND(w.a, 2) AS wing_average FROM bal b JOIN wavg w ON w.wing = b.wing WHERE b.outstanding > w.a ORDER BY b.wing, b.outstanding DESC, b.flat_no← Monthly Rent Collection: On Time, Late and Unpaid · Parse Listing Titles Into Locality and BHK →