Mastering CTEs in SQL

and when to use them over subqueries

Got BDE?⚡️

Hi data baddies 💅🏼

As you guys know, I have launched my new FREE 5-day Data Career Kickstart course, and this course helps you start mastering the #1 skill you need to land high-paying data roles, so you stop wasting time learning the wrong things! 🚀

You can access the FREE course here:

---

The model wasn’t wrong— the data feeding it was 3 days old, and it was confidently working with stale data.

This is the part nobody talks about when they’re hyping up RAG and AI agents— the pipeline underneath still matters:

↳ Overnight batch loads feeding a system that needs real-time accuracy
↳ Manual refresh workflows that break and nobody notices
↳ Legacy tooling that was built before AI exploded

You need to know which pipelines to modernize first and how to move from batch to incremental replication without blowing up what’s already working.

Join me live w/ Kim Fessel and Manish Patel from CData to get into exactly this: how to move from batch to incremental replication, when CDC is the right call, and what separates teams that modernize incrementally from teams stuck in planning mode.

Migrating From Patches to Proactive

---

CTEs in SQL

CTEs are one of the most important skills you need to go from beginner to intermediate, and they’re one of the most commonly misunderstood. I used them for years at my first startup job without knowing what they were called. Then I walked into interviews and got asked about CTEs like it was nothing, and I had NO IDEA what the interviewer was talking about. I was literally using CTEs every day at work and did not know the name of it. That knowledge gap probably cost me real opportunities, so today I am making sure that does not happen to you. 😉

What is a CTE?

CTEs are a common table expression, and in simple terms, a CTE is a temporary result set defined within the scope of the query. Think of it as a table you build with a query, give a name, and tell SQL to hold onto for later. You can then reference it just like any regular table, as many times as you need, but only within that same query.

Here is the basic syntax:

WITH cte_name AS (
  SELECT *
  FROM table1
)

SELECT *
FROM cte_name;

Why do you need CTEs?

There are two situations where CTEs come up: when you WANT to use them, and when you absolutely HAVE to.

1. When you WANT to use them:
   Sometimes you opt into a CTE purely for readability, even if a subquery or regular query would technically work. I do this all the time when writing queries that will end up in a dashboard or get handed off to a teammate. CTEs let you define your logic one step at a time, which makes the code significantly easier to follow. CTEs are great for breaking up complex logic into different steps and building the logic one step at a time (as opposed to putting everything into one massive query!). It is just a nice thing to do for the people who have to read your work later, including the future you.

2. When you HAVE to use them:
   The most common example is nested aggregate functions. SQL does not allow you to nest aggregate functions directly, so writing AVG(SUM(column)) will throw an error. A CTE is the fix; you calculate the sum first, then take the average in the outer query:

   WITH order_counts AS (
     SELECT
       user_id,
       SUM(ordered) AS num_orders
     FROM table1
     GROUP BY 1
   )
   
   SELECT AVG(num_orders) AS avg_per_user
   FROM order_counts;

Another really common situation is working with data at different levels of granularity in the same query. Say you have orders at the day level, but you also want to include the company’s monthly total in the same row. You build a CTE that aggregates by month, then join it back to the daily dataset:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS monthly_orders
  FROM orders
  GROUP BY 1
)

SELECT
  o.order_id,
  o.order_date,
  m.monthly_orders
FROM orders o
LEFT JOIN monthly m
  ON DATE_TRUNC('month', o.order_date) = m.month;

Try doing that without a CTE or a subquery, and I guarantee you will have a hard time. 😅

---

Benefits of CTEs:

1. They are reusable.
   And it’s one of their biggest benefit. They’re really useful if you have to refer to an output multiple times in the same query. They’re only usable within that query, though. So if you don’t rerun the CTE, you won’t be able to use it.

2. They are readable.
   A really important part of coding is making it useful long-term, especially if someone else takes over or needs to edit your work. CTEs make it much easier to understand code because you define output and sections one part at a time. This gives the reader the advantage of slowly digesting and interpreting your code one step at a time.

3. They can be recursive.

   Although I haven’t had many use cases for this in the real world, it is a more advanced programming concept and is worth knowing and learning. This is where CTEs can stand out over subqueries since subqueries don’t have this capability.

CTEs vs. Subqueries

A CTE is essentially the inside-out version of a subquery, and almost every CTE can be rewritten as a subquery and vice versa, with recursive CTEs being the exception.

So when do you pick one over the other? There is not always a clear answer, but here is my general rule: if the logic is complex, reused multiple times, or being handed off to someone else, go with CTE. If it is a simple one-off lookup embedded in a larger query, a subquery works fine.

In my day-to-day work, I regularly write queries with three to five CTEs and sometimes more. Some are mandatory because the logic requires it, and others I add simply to keep things clean and readable for my team.

---

CTEs are one of the most powerful tools in your SQL toolkit, and the analysts who truly understand them, not just how to write them, but when AND why to use them, are the ones who stand out in interviews and on the job.

Master this, and you will feel the difference immediately. And if you're ready to fast-track that mastery, don't forget to grab your spot in my FREE 5-day Data Career Kickstart course! 😉

Bye BDEs 💅🏼

Jess Ramos 💕

---

⚡️ Social Highlights:

---

⚡️If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗YouTube, 🔗LinkedIn, 🔗Instagram, and 🔗TikTok. And of course, subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!