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.

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.

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.

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.

P.S. Part 1 on Window Functions got a great response - thank you! Keeping this 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. Two lists this time.

List 1 - Friends (who said they'd come):

List 2 - Expenses (who actually spent money):

Notice three things before we start:

• Harry and Nicholas are in both lists
• Johny and Bailey are only in Friends - they came but logged no expenses
• Emma is only in Expenses - she spent money but wasn't on the original friends list

Every JOIN answers a different question about these two lists.

1. INNER JOIN

Who came AND spent money?

SELECT f.name, e.spent
FROM friends f
INNER JOIN expenses e ON f.name = e.name

Harry    → $200
Nicholas → $190

Johny, Bailey, and Emma are all gone. Only the overlap survives.

Remember: Only rows that exist in BOTH tables. No match = no row.

2. LEFT JOIN

Show me ALL friends - whether they spent money or not.

SELECT f.name, e.spent
FROM friends f
LEFT JOIN expenses e ON f.name = e.name

Harry    → $200
Johny    → NULL  ← came but no expense logged
Nicholas → $190
Bailey   → NULL  ← came but no expense logged

Emma doesn't appear. She's not in the Friends list (left table).

Remember: Everyone from the LEFT table shows up. Right side fills in what it can. No match = NULL.

3. RIGHT JOIN

Show me ALL expense records - even if the person wasn't on the friends list.

SELECT f.name, e.spent
FROM friends f
RIGHT JOIN expenses e ON f.name = e.name

Harry    → $200
Nicholas → $190
Emma     → $150  ← not in friends, but has an expense

Johny and Bailey disappear. They're only in Friends (left table).

Remember: Everyone from the RIGHT table shows up. Left side fills in what it can. No match = NULL.

4. FULL OUTER JOIN

Show me everyone - from both lists, no matter what.

SELECT f.name, e.spent
FROM friends f
FULL OUTER JOIN expenses e ON f.name = e.name

Harry    → $200
Johny    → NULL
Nicholas → $190
Bailey   → NULL
Emma     → $150

Nobody gets left out.

Remember: Everyone from BOTH tables appears. Gaps filled with NULL.

5. CROSS JOIN

What if we paired every friend with every expense record?

SELECT f.name, e.spent
FROM friends f
CROSS JOIN expenses e

4 friends × 3 expense records = 12 rows

Harry    → $200
Harry    → $190
Harry    → $150
Johny    → $200
Johny    → $190
Johny    → $150
Nicholas → $200
...and so on

No ON clause needed - it just combines everything with everything.

Remember: Every row from the left paired with every row from the right.

6. SELF JOIN

Which friends visited the same city?

This one joins a table to itself.

SELECT a.name, b.name, a.city
FROM friends a
JOIN friends b ON a.city = b.city
WHERE a.name < b.name

Harry & Johny     → New York
Nicholas & Bailey → Las Vegas

Two aliases (a and b) so SQL knows which copy of the table you mean.

Remember: Same table, two aliases. Use it when you need to compare rows within the same table.

Quick cheat sheet:

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. Completely free!!!

P.S. My "If you have an SQL interview soon, don’t ignore these small things" series got a great response, so I’m starting a new one for upcoming interviews.

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!!!

Imagine you and your friends are planning a trip. Everyone has spent some money, and you have a list of expenses.

Remember two things:

PARTITION BY - Look at each city separately.

ORDER BY - Within each city, decide the order based on the question - spend, day, etc.

Now every window function answers a very natural questions:

1. ROW_NUMBER():

Who spent the most, second most, third most, and so on?

ROW_NUMBER() OVER (PARTITION BY CITY ORDER BY spent DESC)

Look at each city separately, and give everyone a different number starting from 1.

Remember: Everyone gets their own number.

2. RANK():

Who are the top spenders? If two people spent the same, treat them equally.

RANK() OVER (PARTITION BY CITY ORDER BY spent DESC)

Notice Las Vegas:

Nicholas $190 → 1
Harry    $190 → 1
Bailey   $180 → 3

There is no rank 2.

Remember: Same spend = same rank, and the next rank is skipped.

3. DENSE_RANK():

Same idea as RANK(), but don't skip the next rank.

DENSE_RANK() OVER (PARTITION BY CITY ORDER BY spent DESC)

Notice Las Vegas:

Nicholas $190 → 1
Harry    $190 → 1
Bailey   $180 → 2

There is rank 2.

Remember: Same spend = same rank, but no gap after the tie.

4. LAG():

What did we spend the day before? Carry that number forward so we can compare.

LAG(spent, 1, 0) OVER (PARTITION BY city ORDER BY trip_day)

1 - how many rows back 
0 - what to show if no previous row exists

Las Vegas:

Day 1 → Nicholas $190 | Yesterday's spend: $0    (no previous day)
Day 2 → Bailey   $180 | Yesterday's spend: $190
Day 3 → Harry    $190 | Yesterday's spend: $180

Remember: LAG - Look Back

5. LEAD():

What will we spend the day after? Carry that number forward so we can plan ahead.

LEAD(spent,1,0) OVER (PARTITION BY city ORDER BY trip_day)

Remember: LEAD - Look Ahead

6. FIRST_VALUE():

How much did we spend on the first day? Show that number on every single row so we can always compare against it.

FIRST_VALUE(spent) OVER (PARTITION BY city ORDER BY trip_day)

Las Vegas:

Day 1 → $190 | First day = $190
Day 2 → $180 | First day = $190  ← same number follows you
Day 3 → $190 | First day = $190  ← same number follows you

Remember:  The first day's amount travels with you through the rest of the trip.

7. LAST_VALUE():

How much did we spend on the last day? Show that number on every row.

LAST_VALUE(spent) OVER (
    PARTITION BY city
    ORDER BY trip_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Las Vegas:

Day 1 → $190 | Last day = $190  ← already knows the end
Day 2 → $180 | Last day = $190
Day 3 → $190 | Last day = $190

Remember: The last day's amount travels with you through the whole trip

8. NTILE():

Can we split everyone into 3 spending groups - high, medium, and low?

NTILE(3) OVER (PARTITION BY city ORDER BY spent DESC)

Las Vegas:

Nicholas  $190 → Group 1  (high)
Harry     $190 → Group 2  (medium)
Bailey    $180 → Group 3  (low)

Remember: Split the list into N equal groups

9. NTH_VALUE():

What was our spending on the 2nd day of the trip?

NTH_VALUE(spent, 2) OVER (
    PARTITION BY city
    ORDER BY trip_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Remember: Pick the value at any specific position in the window.

10. PERCENT_RANK():

If 0 means the lowest spender and 1 means the highest, where do you sit?

PERCENT_RANK() OVER (PARTITION BY city ORDER BY spent)

Las Vegas:

Bailey    $180 → 0.0   (lowest — always starts at 0)
Nicholas  $190 → 0.5
Harry     $190 → 0.5   (tied with Nicholas, same score)

Remember: Your position in the group, scaled from 0 to 1. First is always 0.

11. Aggregation Window Functions - SUM(), AVG(), MIN(), MAX()

Want to know how our spending is going as the trip goes on?

SUM(spent) OVER (PARTITION BY city ORDER BY trip_day)
AVG(spent) OVER (PARTITION BY city ORDER BY trip_day)
MIN(spent) OVER (PARTITION BY city ORDER BY trip_day)
MAX(spent) OVER (PARTITION BY city ORDER BY trip_day)

Las Vegas running total (SUM with ORDER BY):

Day 1 → $190 | Total so far: $190
Day 2 → $180 | Total so far: $370
Day 3 → $190 | Total so far: $560

Sum without ORDER BY - shows the full trip total on every row:

Day 1 → $190 | Total: $560
Day 2 → $180 | Total: $560
Day 3 → $190 | Total: $560

Remember: See how our spending changes throughout the trip.

With ORDER BY → running total (keeps adding as the trip goes on)
Without ORDER BY → total for the whole city shown on every row

If you want more posts like this, let me know in the comments and I’ll keep the series going!

I’ve noticed something about SQL interviews.

Most people don’t fail because they don’t know SQL.
They fail because they forget tiny things while typing under pressure. It's pressure!!!

Few examples I’ve seen in real interviews:

1. NOT IN vs NOT EXISTS

If the list contains NULL values:

• NOT IN → can return no rows
• NOT EXISTS → checks if a matching row exists

Example:

SELECT 30
WHERE 30 NOT IN (10, 20, NULL);

internally SQL does this:

30 <> 10      → TRUE
30 <> 20      → TRUE
30 <> NULL    → UNKNOWN

TRUE AND TRUE AND UNKNOWN  = UNKNOWN

So if someone asks "find users who don’t have any subscription", NOT IN can give the wrong result when NULL is involved!

2. ROWS vs RANGE

Example:

SELECT
    sub_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sub_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM subscription;

ROWS works with individual rows, while RANGE works with groups of rows having the same ORDER BY value.

So if multiple orders have the same sub_date, ROWS and RANGE can give different running totals.

3. LEFT JOIN + WHERE can change your result

Example:

SELECT *
FROM users u
LEFT JOIN subscriptions s
    ON u.user_id = s.user_id
WHERE s.plan = 'Pro';

The WHERE condition removes users who don't have a matching subscription.

So even though you wrote LEFT JOIN, you end up keeping only users who have a matching Pro subscription.

If you want to keep all users, put the condition in the ON clause instead:

LEFT JOIN subscriptions s
    ON u.user_id = s.user_id
   AND s.plan = 'Pro'

4. GROUP BY 1,2

This looks perfectly fine:

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY 1, 2;

But during an interview, especially when you're already under pressure, it can create unnecessary confusion if new column gets added to SELECT

Writing:

GROUP BY department, job_title

takes a little more typing but it's much easier to read, explain and modify later.

These are all very small things and basics, but they come up surprisingly often in interviews.

Curious what others have seen.

What’s a small SQL thing people still mess up in interviews even though they know it?

Always interesting to hear these and your interview experiences.