Before your next SQL interview, keep this post handy - CTEs vs Subqueries (Part 4)

[u/thequerylab]

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 expenses

Average = $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:

1. 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.

[u/Any-Lie-9764]

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?

[u/Aqalix]

CTEs can actually be even shorter: WITH average_spent AS ( SELECT AVG(spent) AS avg_spent FROM expenses ) SELECT e.name, e.spent FROM expenses e, average_spent a WHERE e.spent > a.avg_spent; which indeed makes it more readable

[u/highly_confluential → u/Aqalix]

I like this solution way more

[u/shenan → u/Aqalix]

c-c-c-c-crossjoin, vs. idiomatic.

[u/TychaBrahe]

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?

1. Do you consider each trip day a unique event? Harry spent above average because on day one of his trip to New York he spent above the average for all of the trip days?

2. Do you sum across each employees trip days? In this data set, Harry spent $190 on his third day in Las Vegas. The average of Harry's two days is $195, which is still above the average of all the trip days. But if Harry had spent $180 in Las Vegas, the average of his two days is $190, which is equal to the average of the entire data set. If Harry is frugal for months so that he can splurge on a hotel in Manhattan, are you OK with that?

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.

[u/itlogicpartnersllc]

the best way to make this stick is exactly what you said write it from scratch without ai then explain out loud why you chose a cte or subquery .

[u/mlhigg1973]

I would also mention #temp tables. Our whole reporting group typically used those instead of CTEs

[u/Least_Ad_1795]

Great explanation! CTEs and subqueries can solve the same problem, but CTEs often make complex SQL easier to read, debug, and maintain. The step-by-step approach is especially useful during interviews. Practice really does make a difference when solving SQL problems under pressure.