When One Query Isn't Enough: A Love Letter to CTEs and Subqueries in PostgreSQL

Introduction

If you've spent enough time writing SQL, you've probably reached that point where a query starts simple and then somehow turns into a monster.

You begin with:

SELECT *
FROM trips;

Then someone asks, "Can we find the drivers whose average fare is above the overall average?"

No problem. You add an AVG().

Then they ask, "Only include drivers who have completed at least five trips."

You add a COUNT().

Then comes, "And their average rating should be below 3."

Before you know it, you're staring at a query with nested queries inside nested queries, wondering whether you wrote SQL or accidentally created a puzzle.

This is where subqueries and Common Table Expressions (CTEs) become extremely useful.

Both techniques help us deal with complex SQL, but they do it in slightly different ways. Once you understand the difference, writing analytical queries becomes much more comfortable.

In this article, we'll explore what CTEs and subqueries are, how they differ, when to use each one, and walk through practical PostgreSQL examples.

So, What Exactly Is a Subquery?

A subquery is simply a query inside another query.

The idea is straightforward: PostgreSQL first uses the inner query to produce a result, and the outer query uses that result to complete the bigger task.

For example, suppose we want to find drivers whose average fare is higher than the average fare for all trips.

We could write:

SELECT driver_id,
       AVG(fare) AS avg_fare
FROM trips
GROUP BY driver_id
HAVING AVG(fare) > (
    SELECT AVG(fare)
    FROM trips
);

The part inside the parentheses is the subquery:

SELECT AVG(fare)
FROM trips;

It calculates the overall average fare.

The main query then calculates the average fare for each driver and asks:

"Is this driver's average higher than the overall average?"

If yes, PostgreSQL returns the driver.

That's the basic idea behind a subquery: solve a smaller problem first, then use its answer to solve the bigger problem.

Where Do Subqueries Come In Handy?

Subqueries can appear in several places.

One common example is with WHERE.

Imagine you want to find customers who have spent more than a particular benchmark. The subquery can calculate that benchmark, while the main query identifies the customers who exceed it.

For example, we can find riders who have spent more than the average rider spending:

SELECT rider_id,
       SUM(total_fare) AS total_spend
FROM trips
GROUP BY rider_id 
HAVING SUM(total_fare) > ( SELECT AVG(rider_total) 
                          FROM (
                                SELECT rider_id, 
                                       SUM(total_fare) AS rider_total
                                FROM trips
                                GROUP BY rider_id 
                              ) AS rider_spending
);

"In this example, the inner query first calculates each rider's total spending by grouping trips by rider_id and summing total_fare, giving us a total spending amount per rider. The next level of the subquery uses AVG(rider_total) to calculate the average spending across all riders. The outer query repeats the per-rider spending calculation and uses the HAVING clause to compare each rider's total against that average. As a result, the query returns only riders whose spending exceeds the average. In simple terms, the inner query establishes the benchmark, while the outer query uses it to identify riders who spent above average."

Another useful application is EXISTS.

EXISTS checks whether a related record exists.

For example:

SELECT r.rider_id,
       r.rider_name
FROM riders r
WHERE EXISTS (
    SELECT 1
    FROM trips t
    WHERE t.rider_id = r.rider_id
);

This asks PostgreSQL whether a trip exists for each rider.

If a matching trip exists, the query returns that rider.

EXISTS is particularly useful when you don't actually need data from the inner query. You only want to know whether a matching record exists.

Subquery in FROM

A subquery can also act like a temporary table:

SELECT *
FROM (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
) AS driver_stats
WHERE avg_fare > 1000;

In this example, the subquery is placed inside the FROM clause, allowing the outer query to treat its result like a temporary table. The inner query groups trips by driver_id and calculates each driver's average fare using AVG(fare), then assigns this result the alias driver_stats. The outer query selects all columns from this temporary result and filters using WHERE avg_fare > 1000, returning only drivers whose average fare exceeds 1,000. This approach is useful when you want to perform a calculation first and then filter or further analyze the results."

Then What Is a CTE?

A CTE, or Common Table Expression, allows us to define a temporary named result set at the beginning of a query.

Instead of embedding the smaller query directly inside the main query, you assign it a name using the WITH keyword.

For example:

WITH driver_stats AS (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
)

SELECT *
FROM driver_stats
WHERE avg_fare > 1000;

Here, driver_stats is the CTE.

Inside the CTE, we calculate the average fare for every driver.

Then, in the main query, we treat that result almost like a temporary table and filter it.

The biggest advantage is that the query becomes easier to read.

Instead of trying to understand everything at once, you can look at the query step by step.

CTEs Are Great for Multi-Step Problems

This is where CTEs really shine.

Let's say the operations manager asks:

"Show me drivers who have completed at least four trips but have an average rider rating below 3."

There are two things we need to calculate:

1. The number of trips completed by each driver.
2. The average rating for each driver.

We can separate those tasks:

WITH driver_trips AS (
    SELECT driver_id,
           COUNT(*) AS total_trips
    FROM trips
    GROUP BY driver_id
),

driver_ratings AS (
    SELECT driver_id,
           AVG(rider_rating) AS avg_rating
    FROM trips
    GROUP BY driver_id
)

SELECT dt.driver_id,
       dt.total_trips,
       dr.avg_rating
FROM driver_trips dt
JOIN driver_ratings dr
    ON dt.driver_id = dr.driver_id
WHERE dt.total_trips >= 4
  AND dr.avg_rating < 3.0;

Look at how the query flows.

First, we calculate the number of trips.

Then, we calculate the average rating.

Finally, we join the two results and apply our conditions.

That's much easier to follow than cramming everything into one enormous query.

CTE vs. Subquery: What's the Real Difference?

At a basic level, both can help you achieve similar results.

For example, this uses a subquery:

SELECT *
FROM (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
) AS driver_stats
WHERE avg_fare > 1000;

We could rewrite it using a CTE:

WITH driver_stats AS (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
)

SELECT *
FROM driver_stats
WHERE avg_fare > 1000;

Both approaches can produce the same result.

The main difference is how the query is organized.

The subquery is embedded directly inside the main query.

The CTE is defined separately at the beginning and given a meaningful name.

That difference matters more as your queries get bigger.

Readability. CTEs are named and front-loaded, so you follow the logic in order. Subqueries, especially nested ones, read inside-out, and that gets old fast once things get complicated.

Reusability. Reference a CTE twice in the same query and you're not repeating yourself. Try that with a subquery, and you'll usually end up copy-pasting the same block, or wrapping it in yet another layer.

Recursion. This is where CTEs pull ahead entirely. PostgreSQL supports WITH RECURSIVE, which is what you reach for with hierarchical data: org charts, category trees, bill-of-materials, anything with a parent-child structure that repeats itself. Subqueries just can't do this on their own.

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

The first part of the CTE, known as the anchor query, finds employees with no manager (manager_id IS NULL). These employees represent the top level of the organization and start at level 1. The second part, connected with UNION ALL, is the recursive query; it joins the employees table back to the CTE to find employees whose manager_id matches an employee already found. Each new level increases the count by 1, and PostgreSQL repeats this process until no more employees remain to find. The final SELECT then displays the complete organizational hierarchy, ordered by level. In simple terms, the recursive CTE starts at the top of the hierarchy and works its way downward, discovering each level of employees along the way.

A Simple Rule to Remember

Here's a practical rule I use when thinking about the two:

If the logic is small and only needed once, a subquery is often perfectly fine.

If the query has several steps, a CTE will usually make your life easier.

For example, this is a good candidate for a subquery:

SELECT driver_id,
       AVG(fare)
FROM trips
GROUP BY driver_id
HAVING AVG(fare) > (
    SELECT AVG(fare)
    FROM trips
);

There's only one small calculation inside the subquery, so there's no real need to introduce a CTE.

But if the problem involves several calculations, filtering stages, and joins, a CTE will generally be easier to understand.

One of the Best Things About CTEs: You Can Chain Them

CTEs don't have to stop after one.

You can create one CTE and then use it to build another.

For example:

WITH driver_trips AS (
    SELECT driver_id,
           COUNT(*) AS total_trips
    FROM trips
    GROUP BY driver_id
),

qualified_drivers AS (
    SELECT driver_id,
           total_trips
    FROM driver_trips
    WHERE total_trips >= 5
)

SELECT *
FROM qualified_drivers;

The first CTE calculates the number of trips.

The second CTE takes that result and keeps only drivers with at least five trips.

The final query simply displays the result.

This approach can be incredibly useful when you're working through a complicated business question one step at a time.

When Would You Use These in Real Projects?

CTEs and subqueries aren't just SQL exercises. They show up frequently in real data work.

You might use them to:

• Calculate total revenue by customer.
• Find drivers performing above or below average.
• Identify customers who have never made a purchase.
• Compare monthly revenue with overall revenue.
• Find the top-performing products.
• Calculate cancellation rates.
• Identify duplicate or suspicious records.
• Prepare data for a Power BI dashboard.
• Break complicated data-cleaning operations into manageable steps.

For example, imagine you're analysing a transport company's bookings.

The business asks:

"Which routes generate the most revenue, and which routes have the highest cancellation rates?"

You might need to calculate revenue first, cancellation counts next, and then combine those results.

A CTE can make that workflow much easier to build and understand.

---

Don't Think of CTEs as Automatically "Better"

It's tempting to think that because CTEs look cleaner, they should always replace subqueries.

That's not necessarily true.

A simple subquery can be perfectly readable and may be the most natural solution.

Likewise, using five CTEs for a problem that can be solved with one straightforward query can make the SQL unnecessarily long.

The important thing is to choose the approach that makes the logic clear without adding unnecessary complexity.

Also, don't confuse readability with performance. Whether a CTE or subquery performs better can depend on the PostgreSQL version, query structure, indexes, materialization behaviour, and the execution plan. When performance matters, use EXPLAIN or EXPLAIN ANALYZE rather than assuming one technique will always be faster.

Final Thoughts

CTEs and subqueries are two tools that can make a big difference once you move beyond basic SQL.

A subquery is essentially a query inside another query. It's especially useful when you need a smaller calculation to provide a value or condition for the main query.

A CTE uses the WITH clause to give an intermediate query a name. This makes it particularly useful when you're dealing with several logical steps.

If you're learning SQL, don't worry about trying to master everything at once. Start with simple subqueries. Once you're comfortable with them, move into CTEs and practise breaking larger problems into smaller stages.

Eventually, you'll start looking at a complicated business question and naturally think:

"Okay, what's the first thing I need to calculate?"

That's really the skill you're developing.

SQL isn't just about knowing commands. It's about learning how to break a problem down logically and then telling the database exactly how to solve it.

And once you get comfortable with CTEs and subqueries, those intimidating SQL queries start looking a lot less intimidating.