How to Master Intermediate SQL in Under 10 Minutes

Introduction

Most people plateau at the same point in SQL. They can write a SELECT, join two tables, and filter with WHERE. Then they hit a wall the moment a query needs to answer something slightly harder - a running total, a rank within a group, a query built from smaller pieces. That wall isn't about talent. It's about five concepts nobody explains well the first time. Learn these, and you jump from beginner to intermediate almost immediately. We'll use one running example: an orders table with order_id, customer_id, order_date, and amount.

1. Stop Thinking in Rows. Start Thinking in Sets

Beginners write SQL like a loop: "for each row, do this." That mental model breaks down fast. SQL doesn't process one row at a time - it operates on entire sets at once. Once you think in sets, clauses like GROUP BY and window functions stop feeling like magic and start feeling like the obvious tool for the job.

Here's the shift in practice:

-- Beginner instinct: "loop through orders and total each customer"
-- SQL reality: describe the shape of the result you want
SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;

You're not telling SQL how to loop. You're describing what the final set should look like.

2. Get Comfortable With All Four JOIN Types

Most people know INNER JOIN and stop there. That's enough for simple lookups, but it silently drops data the moment two tables don't perfectly match.

The one that trips people up is LEFT JOIN. It's the right choice whenever you need "everything from A, plus whatever matches from B" - customers with zero orders, products never sold, users who never logged in.

-- Every customer, even ones with no orders yet
SELECT c.customer_id, c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

Rule of thumb: if the question includes the word "even if" or "including those without," reach for LEFT JOIN.

3. GROUP BY and HAVING Aren't Interchangeable With WHERE

WHERE filters rows before grouping. HAVING filters groups after they've been formed. Mixing these up is one of the most common intermediate-level mistakes.

-- Customers who've spent more than $1,000 total
SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;

You can't write WHERE SUM(amount) > 1000 - at the point WHERE runs, that sum doesn't exist yet. It only exists after grouping, which is exactly why HAVING is a separate clause.

4. Window Functions Are the Real Turning Point

If there's one thing that separates beginner SQL from intermediate SQL, it's this. A window function lets you calculate something across a set of rows - a rank, a running total, a comparison to the previous row - without collapsing those rows into one, the way GROUP BY does.

-- Each order, plus that customer's running total so far
SELECT order_id, customer_id, amount, SUM(amount) OVER ( PARTITION BY customer_id ORDER BY order_date ) AS running_total FROM orders;

PARTITION BY splits the data into groups, the same way GROUP BY does. The difference is that every original row survives. You get the detail and the aggregate in the same result. Once this clicks, ranking, "top 3 per category," and month-over-month comparisons stop being hard problems.

5. CTEs: Break Complex Queries Into Readable Steps

A Common Table Expression (WITH clause) lets you name a subquery and reuse it, instead of nesting subqueries five levels deep.

WITH customer_totals AS ( SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id ) SELECT customer_id, total_spent FROM customer_totals WHERE total_spent > 1000;

Nothing here is more powerful than a subquery - it's the same logic. What changes is readability. Six months from now, you'll understand a CTE named customer_totals far faster than a nested query with no name at all.

Subquery or CTE? A Quick Way to Decide

• One-off, simple filter → subquery is fine.
• Reused more than once, or the logic has multiple steps → CTE.
• You'd need a comment to explain what the subquery does → that's a sign it should be a named CTE instead.

Try It: A 5-Minute Challenge

Using the orders table above, write a query that returns each customer's three most recent orders. That single problem forces you to combine a window function (ROW_NUMBER()), a PARTITION BY, and a CTE to filter the result - the exact three concepts covered here.

WITH ranked_orders AS ( SELECT order_id, customer_id, order_date, amount, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC ) AS rn FROM orders ) SELECT order_id, customer_id, order_date, amount FROM ranked_orders WHERE rn <= 3;

If you can read that query and explain what each part does, you're no longer a beginner.

Where to Go From Here

These five ideas cover most of what separates "I can query a database" from "I can actually reason about data." The next step isn't more syntax - it's practice on real, messy datasets where the right join or window function isn't obvious. Pick one query you wrote recently that felt harder than it should have. Rewrite it using a window function or a CTE. That single exercise will teach you more than another list of syntax rules.

If you want structured practice instead of hunting for your own messy datasets, here's how to set it up:

• Find a dataset with at least two related tables and a realistic volume of rows - a few thousand, not ten. Small toy tables hide the problems that window functions and CTEs are meant to solve.
• Write down three real questions you'd actually want answered from that data (e.g., "who are the top 10% of customers by spend," "how does month-over-month revenue compare"). Vague practice produces vague queries.
• Force yourself to answer each question two ways: once with a subquery, once with a CTE or window function. Comparing the two is what makes the difference between them click.
• Time yourself. A query that takes 20 minutes the first time should take 5 the fourth time. If it doesn't, you're pattern-matching syntax, not understanding it.

Look for a platform built around production-style SQL problems - window functions, CTEs, and query optimization included - rather than isolated syntax drills that never combine concepts the way real queries do.