Advanced

By the end of this lesson you'll write the two tools that separate SQL analysts from everyone else: CTEs that turn tangled subqueries into readable, reusable steps, and window functions that rank, total, and compare rows without throwing the detail away. Every result is hand-computed from a 6-row table so you can check yourself.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every query in this lesson runs against this little products table — the same one from the SELECT lesson. It is only 6 rows on purpose: small enough that you can hand-check every window-function result yourself.

A CTE (Common Table Expression) is a temporary, named result set you define with WITH name AS (...) and then query as if it were a real table. It exists only for the single statement it sits in, and it changes nothing in the database. The point is readability : instead of burying logic inside a nested subquery you have to decode from the middle outwards, you give that step a name and read the query top to bottom.

A CTE is like a labelled prep bowl in a kitchen. Rather than chopping onions inside the pan while everything else cooks, you prep "diced onions" in a bowl first, label it, then use it. The recipe reads in order, and you can use the bowl more than once.

Here is the basic shape — define a CTE, then select from it:

The same logic can be written as a nested subquery — but compare how it reads. The CTE version pulls the inner query out and names it, so you follow it step by step instead of unwrapping it from the inside.

Fill in the two blanks to build a CTE that filters to well-stocked products, then selects from it. The expected result is in the comments so you can check yourself.

A window function performs a calculation across a set of related rows — the "window" — but, crucially, it keeps every row . This is the one idea to hold on to: GROUP BY collapses rows into one summary per group, while a window function adds the summary as a new column and leaves the detail rows intact.

Picture a class roster showing each student's grade and the class average printed next to every name. GROUP BY would fold everyone into a single "class average" row. A window function keeps every student visible while writing the average beside each one.

First, the familiar GROUP BY — notice the detail rows disappear:

Now the same kind of average as a window function. The syntax is function() OVER (...) . An empty OVER () means "the window is the whole table", so every row gets the overall average — and all 6 product rows survive.

(The average of all six prices is 171.73 ÷ 6 ≈ 28.62 — shown rounded here; your playground may print more decimals.)

ROW_NUMBER() hands each row a unique position number. The OVER (ORDER BY ...) clause decides the order it counts in — it does not reorder your final output unless you also add a top-level ORDER BY . To rank products from most to least expensive, order by price DESC .

Fill in the two blanks so the dearest product is rank 1 and the cheapest is rank 6. The full expected ranking is in the comments.

The difference between the three ranking functions only appears when rows tie on the value you order by. With two products both priced 24.99: ROW_NUMBER still forces a unique 1, 2, 3, 4; RANK gives the tied rows the same number and then skips (1, 2, 2, 4); DENSE_RANK shares the number but does not skip (1, 2, 2, 3).

Our six products all have distinct prices, so there are no ties — and the three functions return the identical 1…6 you saw above. That is exactly why ROW_NUMBER and RANK look the same here.

PARTITION BY splits the rows into groups and runs the window function independently inside each one. Think of it as " GROUP BY for windows" — same grouping idea, but it keeps every row instead of collapsing the group. Add PARTITION BY category and the ranking counter resets to 1 at the start of each category.

Put an ORDER BY inside the OVER (...) of an aggregate like SUM and it becomes a running total — each row's value is its own amount plus everything that came before it in that order. This is how cumulative charts and "balance to date" columns are built.

Accumulating stock in price-DESC order: 45, then 45+80=125, then +120=245, +200=445, +300=745, +500= 1245 (the grand total on the final row).

LAG(col) returns the value of col from the previous row; LEAD(col) returns it from the next row, in whatever order the OVER (ORDER BY ...) defines. They are how you write "compared to the row before" — month-over-month growth, the gap to the next-cheapest item, and so on. The first row has no previous row (so LAG is NULL ) and the last row has no next row (so LEAD is NULL ).

Q: When should I use a CTE instead of a subquery?

Reach for a CTE when a query gets hard to read, or when you need the same intermediate result more than once. They produce the same answer — a CTE just reads top-to-bottom and can be referenced by name multiple times.

Q: What's the real difference between a window function and GROUP BY ?

GROUP BY collapses each group into one row. A window function adds the summary as a new column and keeps every row , so you get the detail and the aggregate side by side.

Q: Why can't I put a window function in WHERE ?

WHERE runs before window functions are calculated, so the value doesn't exist yet. Compute it in a CTE/subquery, then filter on that column in the outer query.

Q: ROW_NUMBER , RANK , or DENSE_RANK — which do I pick?

Use ROW_NUMBER when you need exactly one row per slot (e.g. "the single newest order per customer"). Use RANK or DENSE_RANK when tied values should genuinely share a position; pick DENSE_RANK if you don't want gaps after a tie.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary