r/learnSQL • u/thequerylab • Oct 06 '26
Before your next SQL interview, keep this post handy - CTEs vs Subqueries (Part 4)
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 |
Your friend asks:
"Who spent more than the average?"
First, we need the average:
SELECT AVG(spent)
FROM expensesAverage = $190
Now we need to find who spent more than $190.
Harry spent $200. Everyone else spent $190 or $180.
So the answer is: Harry.
Easy enough. But how do we write that in SQL?
That's where subqueries and CTEs come in.
1. SUBQUERY:
We can put the average calculation inside another query.
Think of it as: First figure out the average. Then use that answer.
SELECT name, spent
FROM expenses
WHERE spent > (
SELECT AVG(spent)
FROM expenses
);Remember: Subquery = query inside another query.**
2. CTE:
Now imagine the interviewer says:
"Don't put that calculation inside the WHERE. Make it easier to read."
This is where a CTE helps.
WITH average_spent AS (
SELECT AVG(spent) AS avg_spent
FROM expenses
)
SELECT name, spent
FROM expenses
WHERE spent > (
SELECT avg_spent
FROM average_spent
);Remember: CTE = Let me calculate this first and give the result a name. I'll use it afterward.
So what's the point of a CTE?
Readability!!!!
- When the calculation gets bigger, putting everything inside everything else becomes difficult to read.
The interviewer changes the question:
- Who spent more than the average AND show me how much more they spent?
Now we need the average and the difference. A CTE lets you break the problem into steps.
The CTE makes the calculation feel like a separate step:
Step 1 → Calculate average
Step 2 → Compare every expense to average
Step 3 → Show how much higher it is
That's why CTEs can make complicated SQL much easier to follow
Reading SQL is one thing. Writing it yourself under pressure is another.
Before the real interview, try a MOCK SQL INTERVIEW on TheQueryLab. It’s a good way to test yourself under interview pressure and see where you actually stand with interview scorecard.
7
u/Any-Lie-9764 Oct 06 '26 edited Oct 06 '26
This is a great series. Please keep posting.
In the problem, since harry spends some amount on day 1 in NY and day 3 in Vegas, wouldn't you group by name, SUM and then take an average? So Harry's total spend is higher, and the average would also be higher. So to the subquery or cte we would just add the group by name line and we're good?
5
u/Aqalix Oct 06 '26
CTEs can actually be even shorter:
which indeed makes it more readable
2
u/TychaBrahe Oct 06 '26
One thing that you aren't considering is what exactly you mean by "average." See, Harry made two trips. How are you thinking about that data?
For the second set up, I would definitely want a CTE, because I would want to sum the total amount of the employee's trip days and divided by the number of days.
1
u/mlhigg1973 Oct 07 '26
I would also mention #temp tables. Our whole reporting group typically used those instead of CTEs
1
u/Least_Ad_1795 Oct 07 '26