#12 Common Table Expressions (CTEs)

SQL Crash Course #12

Josep Ferrer

and

Cornellius Yudha Wijaya

Apr 17, 2025

Cross-post from DataBites

Let's continue our SQL Learning with CTEs! -

Cornellius Yudha Wijaya

All the course material is stored in the SQL Crash Course repository.

Hi everyone! Josep and Cornellius Yudha Wijaya from Non-Brand Data here 👋🏻

As promised, today we are publishing the next two issues of our SQL Crash Course – From Zero to Hero! 🚀

I am sure you are here to continue our SQL Crash Course Journey!📚

If this is your first time or you’ve forgotten what we’ll cover, we will examine seven key SQL topics, each divided into multiple posts across Non-Brand Data and DataBites.

📚 Previously in SQL Basics…

Remember last Thursday we already saw:

📌 #9 – Case Expressions
📌 #10 – Functions (String, Date, Numeric)

Today, we will explore an exciting new topic in our SQL learning:

Advanced SQL🤩

In today’s topic, we have two new brand issues:

📌 #11 – Subqueries:

Cornellius Yudha Wijaya

Non-Brand Data

will walk you through the subquery concept.

📌 #12 - Common Table Expressions (CTEs): The article you are reading right now, where you will grasp CTEs and what they are used for.

So don’t miss out—let’s keep the SQL momentum going!

🧱 What are Common Table Expressions (CTEs)?

CTEs are named temporary result sets you define at the beginning of your SQL query using the WITH clause.

You can reference them multiple times within the same query, making your SQL modular, clean, and easy to read.

Think of CTEs as building blocks that let you break down complex queries into logical, bite-sized steps.

CTEs often follow this structure:

WITH 

cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT ...
FROM cte_name
WHERE ...;

You can even chain multiple CTEs together:

WITH 
cte1 AS (
...
),

cte2 AS (
SELECT ...
FROM cte1
WHERE ...
)

SELECT ...
FROM cte2;

✅ When to Use CTEs:

👉🏻 When you want to modularize your logic for clarity
👉🏻 When you need to reuse intermediate steps in your query
👉🏻 When working with nested aggregations or multi-step filters
👉🏻 When debugging or collaborating with others

Let’s see CTEs in action!👇🏻

#1. Modular Metric Calculation

🧩 Real-world scenario:
"Compute department-level revenue and rank them by performance."

Without CTEs, everything is packed into one long query:

SELECT 
  n.name, 
  SUM(i.points) AS total_points
FROM newsletters n
JOIN posts p ON n.id = p.newsletter_id
JOIN interactions i ON p.id = i.post_id
GROUP BY n.name
ORDER BY total_points DESC;

With CTEs, we separate concerns:

WITH newsletter_points AS (
  SELECT 
    n.id AS newsletter_id,
    SUM(i.points) AS total_points
  FROM newsletters n
  JOIN posts p ON n.id = p.newsletter_id
  JOIN interactions i ON p.id = i.post_id
  GROUP BY n.id
)
SELECT n.name, np.total_points
FROM newsletter_points np
JOIN newsletters n ON n.id = np.newsletter_id
ORDER BY np.total_points DESC;

🧠 Why CTE: Improves readability, keeps each logical unit isolated, and allows reuse for dashboards or additional transformations.

#2. Reusable Intermediate Filters

🧩 Real-world scenario:
"List top-performing employees only from high-revenue departments."

With multiple subqueries, this can quickly get messy.
CTEs make it clear and structured:

WITH high_engagement_newsletters AS (
  SELECT 
    n.id AS newsletter_id
  FROM newsletters n
  JOIN posts p ON n.id = p.newsletter_id
  JOIN interactions i ON p.id = i.post_id
  GROUP BY n.id
  HAVING SUM(i.points) > 10
),
top_posts AS (
  SELECT 
    p.id, 
    p.name, 
    SUM(i.points) AS total_post_points
  FROM posts p
  JOIN interactions i ON p.id = i.post_id
  GROUP BY p.id, p.name
)
SELECT tp.name, tp.total_post_points
FROM top_posts tp
JOIN posts p ON tp.id = p.id
WHERE p.newsletter_id IN (
  SELECT newsletter_id FROM high_engagement_newsletters
);

🧠 Why CTE: Makes filtering logic modular and self-explanatory. Separates what qualifies a newsletter from what makes a post significant.

#3. Step-by-Step Aggregation

🧩 Real-world scenario:
"Find the average performance per region after normalizing by team size."

Instead of deeply nested subqueries, use layered CTEs:

WITH post_points AS (
  SELECT 
    p.id AS post_id, 
    p.newsletter_id, 
    SUM(i.points) AS total_points
  FROM posts p
  JOIN interactions i ON p.id = i.post_id
  GROUP BY p.id, p.newsletter_id
),
normalized_post_points AS (
  SELECT 
    pp.post_id,
    pp.newsletter_id,
    pp.total_points / COUNT(i.id) AS normalized_score
  FROM post_points pp
  JOIN interactions i ON pp.post_id = i.post_id
  GROUP BY pp.post_id, pp.newsletter_id, pp.total_points
),
newsletter_avg_score AS (
  SELECT 
    n.id AS newsletter_id,
    AVG(npp.normalized_score) AS avg_normalized_score
  FROM newsletters n
  JOIN normalized_post_points npp ON n.id = npp.newsletter_id
  GROUP BY n.id
)
SELECT n.name, nas.avg_normalized_score
FROM newsletter_avg_score nas
JOIN newsletters n ON nas.newsletter_id = n.id;

🧠 Why CTE: Breaks down multi-step logic into focused blocks

—total points → normalized per post → average per newsletter.

Super maintainable and easy to expand later.

🎯 Summary: Why You Should Love CTEs

CTEs = Clean, Transparent, and Reusable SQL.

🔹 Modularity: Break logic into logical units
🔹 Reusability: Reference CTEs across the same query
🔹 Readability: Easier to understand than nested subqueries
🔹 Maintainability: Simplifies debugging and collaboration

💡 Pro Tip: CTE vs Subquery

While subqueries are great for quick logic, prefer CTEs when:

✅ You have multiple steps
✅ You want to reuse intermediate results
✅ You need collaboration-ready SQL
✅ You’re debugging a complex transformation

👉🏻 SQL playground with all CTEs expressions examples

How to Get Started 🚀

Over the coming weeks, we’ll guide you through:

✅ SQL Fundamentals
✅ Intermediate SQL
✅ Advanced SQL
✅ Database Operations
✅ Writing Efficient Queries

Once you grasp the basics, practice is key! Start working on real-world projects and solving hands-on data problems.

What’s Next? ➡️

This is the first of many posts about the upcoming SQL Courses. It will only explain what SQL is in its crude form.

To get the full experience and fully immersed in the learning:

👉 Subscribe to Databites.tech (By Josep)

👉 Subscribe to Non-Brand Data (By Cornellius)

👉 Check out the SQL Crash Course GitHub repo

👉 Share with your friend and whoever needs it!

🗓️ Every Thursday, you will have two new issues in your inbox!

Let’s dive in and make SQL less scary, more fun, and way more useful! 🚀

Josep & Cornellius

🎉 DataBites just hit 8,000 subs & #76 in Rising Tech on Substack — thanks to you!
To celebrate, get 20% off for life on the paid plan until April 13.
👉🏻 Grab it here if you want full access to guides, cheatsheets, and more.