🧠 CTEs: A Useful Partner for Window Functions

Xorthax has learned how to calculate values using window functions. But now he runs into a practical problem:

“How do I filter based on those results?”

That’s where CTEs come in.

🚀 What Is a CTE?

A CTE (Common Table Expression) is a temporary result set that you can reference in a query.
It is not a window function, but often works alongside them.

WITH name AS (
  SELECT ...
)
SELECT ...
FROM name;

Think of it as breaking your query into steps:

• Step 1: Build a result
• Step 2: Use that result

🤝 Why Use CTEs with Window Functions?

Window functions are calculated in the SELECT step.

But filtering usually happens earlier in the query.

That means you often can’t directly filter on a window function—you need a second step.

📊 Example: First Order per Customer

🧾 Sample Data

WITH numbered_orders AS (
  SELECT CustomerID, OrderDate, Amount,
         ROW_NUMBER() OVER (
           PARTITION BY CustomerID
           ORDER BY OrderDate
         ) AS row_num
  FROM Orders
)
SELECT *
FROM numbered_orders
WHERE row_num = 1;

Result:

Each customer now has only their first order.

🧠 What’s Happening Here?

In the CTE:

• ROW_NUMBER() assigns a number to each row
• The numbering resets for each customer

Then in the outer query:

• We filter to keep only row_num = 1

This two-step process is very common when working with window functions.

⚠️ Why not just use WHERE?

Window functions are calculated after the WHERE clause.

So you usually can’t write something like:

WHERE ROW_NUMBER() = 1

Instead, you calculate the window function first (in a CTE), then filter in the outer query.

💡 Other Benefits of CTEs

CTEs aren’t just for filtering.

They also help:

• Organize complex queries
• Break logic into clear steps
• Make your SQL easier to read

🔍 A Note on QUALIFY

Some SQL engines support a QUALIFY clause.

This lets you filter directly on window functions without using a CTE.

SELECT CustomerID, OrderDate, Amount,
       ROW_NUMBER() OVER (
         PARTITION BY CustomerID
         ORDER BY OrderDate
       ) AS row_num
FROM Orders
QUALIFY row_num = 1;

Even if your database supports QUALIFY, the CTE pattern is still useful and widely used.

🧪 Practice What You Just Learned

WITH ranked_orders AS (SELECT customerid, orderid, orderdate, totalamount, ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY orderdate) AS rn FROM orders) SELECT customerid, orderid, orderdate, totalamount FROM ranked_orders WHERE rn = 1 ORDER BY customerid;

➡️ Next Step

You’ve now learned the core building blocks of window functions.

Next, you’re ready to start applying them to real problems.

Want to go deeper?