Simple CTEs for Data Prep: Examples and Real-World Use Cases

Simple CTEs for Data Prep: Examples and Real-World Use Cases

Looks like you’ve already nailed the basics of CTEs and maybe even write WITH almost on autopilot. Today, let’s dive a bit deeper — and see how to use CTEs for prepping data in real-life situations. Imagine you’re about to build a report or a complex SQL query: first, you need to lay out the ingredients — and only then cook up a tasty analytics “soup.”

CTEs are a great tool for those in-between steps: filtering, counting, aggregating, calculating averages — all the stuff you need for meaningful data prep. You can break a gnarly query into clear logical blocks, each doing just one thing: picking the right records, calculating an average, or prepping data for the final SELECT. This makes your code easier to read, gets rid of repeated chunks, and lets you skip temp tables if you don’t need them.

This CTE approach is especially handy when you’re prepping data for reports, building complex filters, or want to “clean up” your data before further processing. In this sense, CTEs aren’t just a technical trick — they’re a legit strategy for building logic step by step, without losing track of what’s going on.

Ready? Let’s jump into some examples.

Filtering Data with CTEs

CTEs are an awesome way to “pull out” just the data you need from a big table, so you can work only with what actually matters. Instead of writing clunky nested queries, you filter your data first, give that step a name — and then work with the result like it’s a regular table.

Let’s say we have a students table that stores student grades:

Table students

Let’s say you want to select everyone with a grade above 85. With a CTE, this is super clear:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Result:

What’s nice here?

You filtered out the rows you need ahead of time and gave that step a name — excellent_students. Now you can use this result further: join it with another table, filter again, or calculate the average grade. Everything’s readable, simple, and not confusing — especially if your query is big.

Aggregating Data with CTEs

Now let’s look at a case where you need to count records or calculate averages. For example, we have an enrollments table that stores which students are enrolled in which courses.

Table enrollments

We want to know how many students are enrolled in each course.

Example query:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Result:

Key points:

• We grouped the data by course_id and counted the number of students for each course.
• The course_enrollments table now has this info, and you can use it for further analysis.

Prepping Data for Reports

If you need to build a detailed report based on several data processing steps, CTEs are a real lifesaver. They let you break all the logic into clear blocks without creating extra temp tables. Imagine you have a grades table with grades and a students table with student info. You need a report with only those students whose average grade is above 80.

Table grades

Table students

Instead of a clunky nested query, you can just build it step by step:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

In the first step (avg_grades), we calculated the average grade for each student and filtered only those who did well — above 80. In the second step (students_with_grades), we neatly joined this data with the students table to get first and last names. The final SELECT gives you a clean table you can drop right into a report — everything’s already calculated, filtered, and nicely formatted.

Result:

This is exactly what makes CTEs so handy: you can focus on logic and structure, without getting distracted by side stuff like creating and dropping temp tables.

Calculating Complex Metrics

Sometimes you need to combine different data in one query. For example, let’s say we want to calculate for each course:

1. The number of students.
2. The average grade for the course.

Example query:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Mistakes to Avoid

When working with CTEs, it’s easy to get tripped up and make a couple of classic mistakes.

The first — over-materialization. If you create too many CTEs, PostgreSQL might save their results as temp tables, even if you only need them once. As a result, your query could run slower than you’d like.

The second mistake — bad filter placement. If you apply filters in the wrong order or at different steps, your final result might not be what you expected. For example, you could accidentally filter out important data too early.

So, it’s best to use CTEs where your data goes through several sequential transformations — that’s where this tool really shines and helps you write clean, readable, and efficient code.