All sample questions / SQL

What does SELECT account, COUNT(*) FROM withdrawals WHERE amount >= 50 GROUP BY ... return? SQL Grouping and Aggregation

Block 3, Grouping and Aggregation, question 5 of the SQL sample

CREATE TABLE withdrawals (id INTEGER, account TEXT, amount INTEGER);
INSERT INTO withdrawals VALUES
  (1, 'A1', 200),
  (2, 'A1', 50),
  (3, 'A1', 20),
  (4, 'A2', 500),
  (5, 'A2', 10);

SELECT account, COUNT(*) FROM withdrawals WHERE amount >= 50 GROUP BY account HAVING COUNT(*) >= 2 ORDER BY account

What does this query return?

  1. A. A1 | 2 A2 | 1
  2. B. A2 | 1
  3. C. A1 | 3 A2 | 1
  4. D. A1 | 2
Show the answer and why each option is right or wrong

Answer, D. A1 | 2

A. A1 | 2 A2 | 1
This lists A2 as well, but A2 has only one withdrawal of 50 or more, which fails HAVING COUNT(*) >= 2, only A1 clears that bar.
B. A2 | 1
This keeps A2, whose single qualifying withdrawal does not meet the HAVING threshold, instead of A1, which has two.
C. A1 | 3 A2 | 1
This counts the 20 withdrawal for A1 too, but WHERE amount >= 50 removes it before grouping, leaving only two qualifying withdrawals for A1.
D. A1 | 2
Correct. WHERE keeps two A1 withdrawals and one A2 withdrawal, and HAVING then drops A2 for not reaching two.

WHERE removes the small withdrawals before grouping starts, and HAVING then removes any account whose remaining count still falls short of the threshold, the two conditions are checked at different stages.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.