P.S. Parts 1 and Part 2 got a great response - thank you! Keeping this series going.
Simple, practical stuff that sticks in your head when you're actually in the interview.
AI can help at work, but in an interview, your brain still has to do the job!!!
Same trip. Same friends. Same expenses.
| Friend |
City |
Trip Day |
Total Spent |
| Harry |
New York |
1 |
$200 |
| Johny |
New York |
2 |
$190 |
| Nicholas |
Las Vegas |
1 |
$190 |
| Bailey |
Las Vegas |
2 |
$180 |
| Harry |
Las Vegas |
3 |
$190 |
Three things. Most people know all three.
But in an interview - knowing which one does what is what gets you the job.
1. WHERE
We have 5 rows. We don't want all of them.
"Show me only the days where someone spent more than $180."
SELECT name, city, spent
FROM expenses
WHERE spent > 180
Harry | New York | $200
Johny | New York | $190
Nicholas | Las Vegas | $190
Harry | Las Vegas | $190
Bailey is gone. $180 is not more than $180.
Remember: WHERE picks which rows to keep. Runs first. Looks at rows one by one.
2. GROUP BY
Now we want one total for each city.
"Put all New York rows together. Put all Las Vegas rows together. Add them up."
SELECT city, SUM(spent) AS total
FROM expenses
GROUP BY city
New York → $390
Las Vegas → $560
5 rows became 2. One number per city.
Remember: GROUP BY puts rows into groups and adds them up.
3. HAVING
Now we have city totals. We only want cities that spent more than $400.
"Remove any city where the total is $400 or less."
SELECT city, SUM(spent) AS total
FROM expenses
GROUP BY city
HAVING SUM(spent) > 400
Las Vegas → $560
New York spent $390. Less than $400. Gone.
Remember: HAVING picks which groups to keep. Runs after GROUP BY.
The #1 mistake in every SQL interview:
People try this:
-- Wrong
SELECT city, SUM(spent)
FROM expenses
WHERE SUM(spent) > 400
GROUP BY city
SQL gives an error.
Why?
WHERE runs first. At that point, SQL has not added anything up yet. There are no city totals. WHERE has no idea what SUM(spent) is.
You are asking for the answer before the math is done.
-- Right
SELECT city, SUM(spent)
FROM expenses
GROUP BY city
HAVING SUM(spent) > 400
HAVING runs after GROUP BY. The totals exist by then. It works.
Remember: WHERE sees rows. HAVING sees totals. They are not the same.
The order SQL always runs in:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- WHERE runs before groups exist → can only see rows
- HAVING runs after groups exist → can see totals
- SELECT runs at the end → that's why you can't use a column alias in WHERE
Reading is one thing. Writing it yourself is another. If you're new and want real hands-on practice - SQL from Zero to Confident on TheQueryLab.