SQL CTEs: How to Build a Query in Steps You Can Check

1. What a CTE is, in one query

Before the explanation: you write WITH rated AS (...) at the top of a query. Is rated a real table that now exists in your database, and would a colleague be able to query it tomorrow?

CTE stands for common table expression. Ignore the name. It is a way to say "first work this out and call it X", and then use X in the query below as though it were a table.

WITH rated AS (
    SELECT *
    FROM games
    WHERE TotalReviews >= 500
)
SELECT COUNT(*) FROM rated;

That returns 10,507. The WITH block defines a step and names it. The SELECT underneath uses that name. Nothing was created and nothing was saved, which is the answer to the prequestion: rated exists only while this one query runs, and a colleague tomorrow would find nothing.

That temporariness is the point. You get the readability of a saved table with none of the cleanup, permissions or staleness that a real table brings.

Two rules of syntax cover almost everything. Each named step is wrapped in brackets after AS. Multiple steps are separated by commas, and the word WITH appears only once, at the very top.

2. Stacking steps, and checking each one

Before the explanation: your query has three stacked steps and the final answer is wrong. How many queries do you have to run to find out which step broke it?

One per step, and that is the whole argument for writing it this way. A later step can refer to any earlier one, so the steps form a chain.

WITH rated AS (
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    SELECT * FROM rated WHERE PctPositive >= 90
)
SELECT COUNT(*) FROM well_liked;

Note that well_liked reads from rated, not from games. Each step narrows the one before it. The count comes back 3,454.

Now the habit that makes this worth doing. To test any step, change nothing except the final line.

-- Same CTEs above, then one of:
SELECT COUNT(*) FROM rated;       -- 10,507
SELECT COUNT(*) FROM well_liked;  -- 3,454
SELECT * FROM well_liked LIMIT 5; -- eyeball actual rows

Four counts down the chain tell you where a number went wrong, and they take seconds. A step that drops from 10,507 to 12 is a broken filter, and you find it at the step rather than at the end.

Say out loud why testing the third step of a deeply nested subquery is harder than this, before you read on. The answer is that a nested step has no name, so there is nothing to put after FROM. You have to cut the inner query out, paste it somewhere else, and hope you grabbed matching brackets. That friction is why people skip the check.

3. The real four-step example

Before the explanation: "an overlooked game" is not a column in any dataset. How many separate decisions do you think it takes to turn that phrase into rows?

Four, and naming them is most of the analysis. Here is the actual definition.

WITH rated AS (
    -- Step 1: enough reviews that the rating means something
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    -- Step 2: players actually rate it highly
    SELECT * FROM rated WHERE PctPositive >= 90
),
overlooked AS (
    -- Step 3: not many people own it
    SELECT * FROM well_liked WHERE EstOwnersMid <= 50000
),
genre_avg AS (
    -- Step 4: what "highly rated" means in its own genre
    SELECT PrimaryGenre, AVG(PctPositive) AS genre_rating
    FROM rated
    GROUP BY PrimaryGenre
    HAVING COUNT(*) >= 100
)
SELECT o.Name,
       o.PrimaryGenre,
       o.PctPositive,
       ROUND(g.genre_rating, 1) AS genre_avg
FROM overlooked AS o
JOIN genre_avg AS g ON o.PrimaryGenre = g.PrimaryGenre
ORDER BY o.PctPositive DESC;

The counts down the chain are 82,956, then 10,507, then 3,454, then 663. Each drop is a decision someone can argue with, which is the right kind of argument to be able to have.

Step four is doing something different from the first three, and it is worth noticing. It does not narrow the rows. It calculates a genre average from the rated step, and then the final query joins it back on. A CTE used this way is a lookup table you build on the spot. That pattern, calculate a summary then join it back to the detail, is one of the most useful things CTEs do.

The results say something the raw ranking could not. Kabuto Park sits at 99.9% positive in Casual, a genre averaging 84.6%. The Werecleaner is at 99.4% in Action, where the average is 80.9%. Both were invisible in a plain top-rated list, because a plain list is full of games with forty reviews.

Picture running that chain against a table you actually work with. What would your four steps be, and which of them would you expect to be the one someone argues about?

4. A CTE against a subquery, same answer

Before the explanation: both versions below return identical rows. If the database does the same work either way, does the choice matter at all?

Here is the nested version of just the first two steps.

SELECT COUNT(*)
FROM (
    SELECT * FROM (
        SELECT * FROM games WHERE TotalReviews >= 500
    ) WHERE PctPositive >= 90
);

Same 3,454. It is correct, and the criticism is not that it is bad style. It is that you read it inside out, from the middle brackets to the edges, which is the opposite of the order the work happens in. Nothing has a name, so nothing can be tested without surgery.

The choice matters for the person reading it in three months, which is usually you. That is the entire argument, and it is enough.

That last row is the honest boundary. A CTE vanishes when the query ends. If three people need the same step next week, it belongs in a view or a table, not copied into three files.

5. The full before and after

Same question: which highly rated games have very few owners, relative to their own genre?

Before

SELECT Name, PrimaryGenre, PctPositive
FROM games
WHERE TotalReviews >= 500
  AND PctPositive >= 90
  AND EstOwnersMid <= 50000
ORDER BY PctPositive DESC;

It runs and returns 663 rows. Three problems, none of which produce an error. The three thresholds sit in one clause with no stated reason, so nobody can tell which was the judgement call. There is no way to see how many rows each condition removed. And it cannot answer the actual question, because comparing a game to its own genre needs an average that this query never calculates.

After

-- ============================================================
-- STEP 5: Which well-rated games are overlooked in their genre?
-- WHY: A plain top-rated list is dominated by games with forty
--      reviews. Each step below removes one specific problem,
--      and each has a row count you can check on its own.
-- COUNTS: 82,956 all games, 10,507 rated, 3,454 well liked,
--      663 overlooked. Change a threshold and rerun the counts.
-- ============================================================
WITH rated AS (
    --Games with enough reviews for the rating to be stable
    SELECT * FROM games WHERE TotalReviews >= 500
),
well_liked AS (
    --Of those, the ones players rate 90% positive or better
    SELECT * FROM rated WHERE PctPositive >= 90
),
overlooked AS (
    --Of those, the ones hardly anyone owns
    SELECT * FROM well_liked WHERE EstOwnersMid <= 50000
),
genre_avg AS (
    --Separately: the average rating within each genre,
    --  built from rated so it is not skewed by tiny samples
    SELECT PrimaryGenre, AVG(PctPositive) AS genre_rating
    FROM rated
    GROUP BY PrimaryGenre
    HAVING COUNT(*) >= 100
)
--Finally: the overlooked games, each shown next to its genre average
SELECT o.Name,
       o.PrimaryGenre,
       o.PctPositive,
       ROUND(g.genre_rating, 1) AS genre_avg
FROM overlooked AS o
JOIN genre_avg AS g ON o.PrimaryGenre = g.PrimaryGenre
ORDER BY o.PctPositive DESC;

Every threshold now sits next to the reason for it. Every step can be counted. The genre comparison the question actually asked for is present. The comment format is the one from how to comment SQL so it teaches.

6. Edge cases and the honest limits

Before the explanation: you split a slow query into six tidy CTEs and it gets slower. What could naming a step possibly have to do with speed?

Six things worth knowing.

Some databases treat a CTE as a fence. This is the answer to the prequestion. PostgreSQL before version 12 materialised every CTE, meaning it calculated each one fully instead of folding it into the wider plan, so a filter in the final query could not push down into the step above. Modern PostgreSQL inlines them by default, and you can force either behaviour with AS MATERIALIZED or AS NOT MATERIALIZED. If a rewrite got slower, that is the first thing to look at.

A CTE referenced twice may be computed twice. Where CTEs are inlined, using the same step in two places can run it twice. Usually irrelevant. On a step that scans forty million rows, not irrelevant. Handling large datasets covers working at that size.

Only one WITH, and commas between steps. Writing WITH again before the second step is the most common syntax error here, and the message rarely points at the real line.

A trailing comma before the final SELECT breaks it. The last CTE has no comma after its closing bracket. This bites every time you reorder steps.

Naming a step after a real table shadows it. If you write WITH games AS (...), every mention of games below now means your step, not the table. Occasionally deliberate. More often an afternoon lost.

Recursive CTEs are a different tool wearing the same word. WITH RECURSIVE lets a step refer to itself, which is how you walk a hierarchy such as an employee reporting chain or a folder tree. It shares the syntax and almost nothing else, and it is worth learning separately once ordinary CTEs feel automatic.