Savings Accounts Above the Bank-Wide Average Balance
Marketing wants to offer a premium debit card to savings customers whose balance is strictly above the average balance of all savings accounts in the bank.
- Difficulty: Easy
- Topics: Subqueries
- Dialect: MySQL
- Problem: #55
Problem statement
Marketing wants to offer a premium debit card to savings customers whose balance is strictly above the average balance of all savings accounts in the bank.
Return account_id, holder_name and balance for those accounts, ordered by balance descending, then by account_id ascending. An account whose balance equals the average is not included.
Tables
Table: SavingsAccount
| Column | Type |
|---|---|
| account_id | int |
| holder_name | varchar |
| balance | int |
Primary key: account_id.
One row per savings account; balance is in rupees.
Examples
Example 1
SavingsAccount
| account_id | holder_name | balance |
|---|---|---|
| 1 | Aarav Patel | 120000 |
| 2 | Ishaan Rao | 40000 |
| 3 | Tanvi Nair | 60000 |
| 4 | Simran Gupta | 150000 |
| 5 | Liam Das | 30000 |
| 6 | Ira Menon | 120000 |
Output
| account_id | holder_name | balance |
|---|---|---|
| 4 | Simran Gupta | 150000 |
| 1 | Aarav Patel | 120000 |
| 6 | Ira Menon | 120000 |
How to solve Savings Accounts Above the Bank-Wide Average Balance
The comparison value is a property of the whole table, not of the row, so it comes from an aggregate: AVG(balance). An aggregate cannot appear in a WHERE clause on its own, but a scalar subquery can — (SELECT AVG(balance) FROM SavingsAccount) produces a single number that the database computes once and compares every row against.
When every account has the same balance, the average equals each balance and the strict comparison returns nothing, which is exactly what "strictly above" means. Ties among the qualifying balances are ordered by account_id.
The same idea can be written as a CROSS JOIN to a one-row derived table holding the average, or with the window function AVG(balance) OVER (), which attaches the overall average to every row so a derived table can filter on it. All three read the table twice or once plus a sort; for a small table the difference does not matter.
Reference solution (MySQL)
SELECT account_id, holder_name, balance
FROM SavingsAccount
WHERE balance > (SELECT AVG(balance) FROM SavingsAccount)
ORDER BY balance DESC, account_idAnother way
SELECT s.account_id, s.holder_name, s.balance FROM SavingsAccount s CROSS JOIN (SELECT AVG(balance) AS avg_balance FROM SavingsAccount) a WHERE s.balance > a.avg_balance ORDER BY s.balance DESC, s.account_idAnother way
SELECT account_id, holder_name, balance FROM (SELECT account_id, holder_name, balance, AVG(balance) OVER () AS avg_balance FROM SavingsAccount) t WHERE balance > avg_balance ORDER BY balance DESC, account_id← Customers Holding Both Savings and Current Accounts · UPI Success Rate by App and Month →