Question

Let Account be a relation as shown.

Table: Account

AccNo	Balance
A1	5000
A2	5000
A3	10000
A4	15000
A5	18000

Consider the given SQL query.

SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)

The number of rows returned by the SQL query is _______. (Answer in integer)

Hint:

For each account, compare how many accounts have a strictly higher balance against how many have a strictly lower balance; only rows at or below the median pass.

Answer 1

Correct Answer: 3

A quicker way to see this is to first sort the balances and think in terms of rank. Sorted in increasing order, the balances are 5000, 5000, 10000, 15000, 18000, held by A1/A2, A1/A2, A3, A4, A5 respectively (A1 and A2 tie at 5000).

For any account A, countGreater is simply how many of the 5 balances are strictly above A's balance, and countLess is how many are strictly below it. The condition countGreater $\geq$ countLess is really asking whether an account sits at or below the "middle" of the distribution: values on the high end will always have more accounts below them than above, so they fail the test, while values on the low or middle end pass.

1. A1 and A2 (5000, the smallest value, tied): Nothing is smaller, so countLess $= 0$. Everything except the other tied account is bigger, so countGreater $= 3$. Since $3 \geq 0$, both pass.
2. A3 (10000, the middle value): Two balances (5000, 5000) are smaller and two (15000, 18000) are bigger, so countLess $= 2$ and countGreater $= 2$. Since $2 \geq 2$, it passes.
3. A4 (15000): Three balances are smaller (5000, 5000, 10000) and only one is bigger (18000), so countLess $= 3$ and countGreater $= 1$. Since $1 \geq 3$ is false, it fails.
4. A5 (18000, the largest value): All four other balances are smaller, so countLess $= 4$ and countGreater $= 0$. Since $0 \geq 4$ is false, it fails.

So the accounts that pass the filter are exactly those at or below the median balance: A1, A2, and A3. That gives 3 rows in the result.

$$\boxed{3}$$