Flats Owing More Than Their Wing's Average

Medium
360.

Flats Owing More Than Their Wing's Average

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

ColumnType
flat_novarchar
wingchar
owner_namevarchar

Primary key: flat_no.

Table: DuesLedger

ColumnTypeValues
entry_idint
flat_novarchar
entry_typeenumcharge, payment
amountint

Primary key: entry_id.

amount is always positive; entry_type says whether it was billed or received. flat_no is always in SocietyFlat.

Example

The rows must come back in the order shown.

SocietyFlat

flat_nowingowner_name
A-101APriya Menon
A-102ARohan Gupta
A-103ASaanvi Rao
B-101BFarhan Khan
B-102BEmma Das

DuesLedger

entry_idflat_noentry_typeamount
1A-101charge9000
2A-101payment3000
3A-102charge9000
4A-102payment9000
5B-101charge6000
6B-102charge6000
7B-102payment6000
8B-101payment1000
9A-103charge4500

Output

flat_nowingoutstandingwing_average
A-101A60003500
A-103A45003500
B-101B50002500
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.

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

ColumnType
flat_novarchar
wingchar
owner_namevarchar

Primary key: flat_no.

Table: DuesLedger

ColumnType
entry_idint
flat_novarchar
entry_typeenum(charge, payment)
amountint

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_nowingowner_name
A-101APriya Menon
A-102ARohan Gupta
A-103ASaanvi Rao
B-101BFarhan Khan
B-102BEmma Das

DuesLedger

entry_idflat_noentry_typeamount
1A-101charge9000
2A-101payment3000
3A-102charge9000
4A-102payment9000
5B-101charge6000
6B-102charge6000
7B-102payment6000
8B-101payment1000
9A-103charge4500

Output

flat_nowingoutstandingwing_average
A-101A60003500
A-103A45003500
B-101B50002500

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_no

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

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