Savings Accounts Above the Bank-Wide Average Balance

Easy
55.

Savings Accounts Above the Bank-Wide Average Balance

EasySubqueries

MySQL

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

ColumnType
account_idint
holder_namevarchar
balanceint

Primary key: account_id.

One row per savings account; balance is in rupees.

Example

The rows must come back in the order shown.

SavingsAccount

account_idholder_namebalance
1Aarav Patel120000
2Ishaan Rao40000
3Tanvi Nair60000
4Simran Gupta150000
5Liam Das30000
6Ira Menon120000

Output

account_idholder_namebalance
4Simran Gupta150000
1Aarav Patel120000
6Ira Menon120000
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.

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

ColumnType
account_idint
holder_namevarchar
balanceint

Primary key: account_id.

One row per savings account; balance is in rupees.

Examples

Example 1

SavingsAccount

account_idholder_namebalance
1Aarav Patel120000
2Ishaan Rao40000
3Tanvi Nair60000
4Simran Gupta150000
5Liam Das30000
6Ira Menon120000

Output

account_idholder_namebalance
4Simran Gupta150000
1Aarav Patel120000
6Ira Menon120000

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_id

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

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