Question

Persons
id	name	address	age	eyeColor	gender

BankAccounts
id	balance

AccountOf
id	person_id → Persons	account_id → BankAccounts

Write a query that returns the names of all people that have less than half of the wealth of the richest person. We define the wealth of a person as the total money on all of his/her accounts.

Answer 1

SELECT p.name FROM BankAccount b INNER JOIN AccountOf a ON b.id = a.account_id INNER JOIN Persons p ON p.id = a.person_id
GROUP BY a.person_id
HAVING SUM(b.balance) < (SELECT SUM(b.balance) FROM BankAccounts b INNER JOIN AccountOf a ON b.id = a.account_id GROUP BY a.person_id ORDER BY SUM(b.balance))/2;

Explanation

The query above will fetch the names of all the persons having the total balance in all the accounts less than half the total balance of the petson having highest balance.

We have used the concept to subquery for the query above. A subquery is actually a query inside another parent query.

The subquery used here will give us the highest total wealth of a person.

We have now used this value to compare with the total wealths of the other persons.

Inner join is used to join the three tables.

Group by is used to group all the accounts of a person

Sum aggregate function is used to find the sum of balance of all accounts for a user.

Having is used to compare total wealth of each user with the half of highest wealth.