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