SQL CTEs: Writing Queries Like You're Telling a Story

Last session we did subqueries. Nested SELECT inside SELECT, inner query running first, outer query using the result. Powerful. But by the end, some of the harder questions had queries that looked like this:

SELECT r.rider_name,
    (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) AS total_spent
FROM safari.riders r
WHERE (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) > (
    SELECT AVG(spend) FROM (
        SELECT SUM(fare) AS spend FROM safari.trips GROUP BY rider_id
    ) sub
)

I put that on the projector without saying anything. Gave the class 30 seconds to read it.

Then I asked: "If this query broke at 3pm on a Friday, how long would it take you to find the bug?"

Silence. Then someone said: "Too long."

That's the setup for CTEs.

---

What a CTE Actually Is

A CTE - Common Table Expression - is a named, temporary result set that you define before the main query and reference by name. It's not stored anywhere. It doesn't persist after the query runs. But for the duration of that query, it behaves exactly like a table.

WITH cte_name AS (
    -- your inner query here
)
SELECT columns
FROM cte_name
WHERE conditions;

The WITH keyword opens it. The name goes before AS. The inner query goes in brackets. Then the main SELECT reads from the CTE by name like it's reading from any other table.

I put the comparison on the board:

	Subquery	CTE
Where it lives	Embedded inside the main query	Defined before the main query
Has a name	No (alias only in FROM)	Yes - used anywhere in the main query
Readability	Gets messy as complexity grows	Reads like steps in a story
Can be reused	Has to be repeated	Referenced by name, once
Performance	Similar in most cases	Similar in most cases

"A CTE doesn't make your query faster. It makes it readable. And readable queries are the ones that get maintained, debugged, and trusted."

---

The Dataset: SafariRide

Three tables. safari.drivers - driver name, car model, join date, status (Active/Inactive). safari.riders - rider name, city, membership tier. safari.trips - trip date, distance, fare, rider rating, payment method, with foreign keys linking to both drivers and riders.

Forty trips. Ten drivers. Real enough to ask real questions about.

We also introduced something this session that I want to highlight before the questions: query documentation. Every CTE in today's script had a comment above it:

WITH driver_trip_counts AS (
    -- Step 1: Aggregate trips per driver inside the CTE
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
-- Step 2: Join back to get readable names and filter
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
WHERE dtc.total_trips >= 5
ORDER BY dtc.total_trips DESC;

I told the class: "Good documentation isn't about writing more comments - it's about writing the RIGHT comments. Explain WHY you made a choice, not just WHAT the code does. Your future self, and your teammates, will thank you."

Section A: Basic CTEs

A1. Busy Drivers

Which drivers have completed 5 or more trips?

The challenge: the trips table has driver IDs. The drivers table has names. You need to aggregate first, then join, then filter.

WITH driver_trip_counts AS (
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
WHERE dtc.total_trips >= 5
ORDER BY dtc.total_trips DESC;

The CTE does the counting. The outer query joins to get names and filters for the threshold. Two clean steps, each with one job.

A2. Big Spenders

Which riders have spent more than KES 3,000 in total fares?

WITH rider_spend AS (
    SELECT rider_id, SUM(fare) AS total_spent
    FROM safari.trips
    GROUP BY rider_id
)
SELECT r.rider_name, rs.total_spent
FROM safari.riders r
JOIN rider_spend rs ON r.rider_id = rs.rider_id
WHERE rs.total_spent > 3000
ORDER BY rs.total_spent DESC;

The pattern is becoming visible: CTE aggregates, outer query joins and filters. That's the Section A rhythm.

A3. Above-Average Earners

Which drivers earn above the overall average fare?

WITH driver_avg_fare AS (
    SELECT driver_id, ROUND(AVG(fare), 2) AS avg_fare
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, daf.avg_fare
FROM safari.drivers d
JOIN driver_avg_fare daf ON d.driver_id = daf.driver_id
WHERE daf.avg_fare > 955.23
ORDER BY daf.avg_fare DESC;

The overall average (955.23) was calculated separately and noted in a comment. In Section C we'll make that dynamic too - but for now, calculated-once-and-noted is honest and readable.

A4. Popular Payment Methods

Which payment methods were used in more than 12 trips?

WITH payment_counts AS (
    SELECT payment_method, COUNT(*) AS num_trips
    FROM safari.trips
    GROUP BY payment_method
)
SELECT payment_method, num_trips
FROM payment_counts
WHERE num_trips > 12
ORDER BY num_trips DESC;

Notice: no JOIN here. The CTE has everything we need. The outer query just filters it. Not every CTE needs a join - sometimes the aggregation step is all the complexity there is.

A5. Top 3 Busiest Drivers

Just the top 3 - with a tiebreaker.

WITH driver_trip_counts AS (
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
ORDER BY dtc.total_trips DESC, d.driver_name ASC
LIMIT 3;

The second ORDER BY column - d.driver_name ASC - is the tiebreaker. If two drivers share the same trip count, alphabetical order decides who appears first. Without it, ties produce inconsistent results across runs.

A6. Riders Who Need a Check-In Call

Which riders have taken fewer than 2 trips - including riders with zero trips?

This is the question that caught people. An INNER JOIN would silently drop riders with no trips at all, because there's nothing to match on. You need a LEFT JOIN inside the CTE.

WITH rider_trip_counts AS (
    SELECT r.rider_id, COUNT(t.trip_id) AS trip_count
    FROM safari.riders r
    LEFT JOIN safari.trips t ON r.rider_id = t.rider_id
    GROUP BY r.rider_id
)
SELECT rr.rider_name, rtc.trip_count
FROM safari.riders rr
JOIN rider_trip_counts rtc ON rr.rider_id = rtc.rider_id
WHERE rtc.trip_count < 2
ORDER BY rtc.trip_count;

Two things worth calling out: the LEFT JOIN inside the CTE keeps riders who haven't taken any trips. And COUNT(t.trip_id) instead of COUNT(*) - counting a specific column returns 0 when there's no match, because t.trip_id is NULL for unmatched rows. COUNT(*) would incorrectly return 1.

---

Section B: Medium - CTE + JOIN + CASE WHEN

B1. Active Driver Leaderboard

Revenue per driver, but only for Active drivers.

WITH driver_revenue AS (
    SELECT driver_id, SUM(fare) AS total_revenue
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, d.car_model, dr.total_revenue
FROM safari.drivers d
JOIN driver_revenue dr ON d.driver_id = dr.driver_id
WHERE d.status = 'Active'
ORDER BY dr.total_revenue DESC;

The status filter lives in the outer query - not the CTE. The CTE calculates revenue for every driver. The outer query decides which drivers you care about. Keeping the filter outside the CTE makes the CTE reusable. If you later want inactive driver revenue, you change one word in the outer query.

B2. Rating Breakdown

How many trips rated Poor, Good, or Excellent?

CASE WHEN inside a CTE - two sessions combined into one query.

WITH rated AS (
    SELECT trip_id,
        CASE
            WHEN rider_rating <= 2 THEN 'Poor'
            WHEN rider_rating = 3  THEN 'Good'
            ELSE 'Excellent'
        END AS rating_label
    FROM safari.trips
)
SELECT rating_label, COUNT(*) AS num_trips
FROM rated
GROUP BY rating_label
ORDER BY num_trips DESC;

The CTE labels every trip. The outer query counts the labels. Without the CTE, you'd have to repeat the entire CASE WHEN inside a GROUP BY or wrap it in a subquery. With the CTE, the labelling logic is written once, named rated, and the outer query just asks "how many of each label?"

B3 & B6: Premium Riders and Nairobi Riders

Both follow the same pattern - aggregate in the CTE, filter on a rider attribute in the outer JOIN:

-- Premium members only
WHERE r.membership_tier = 'Premium'

-- Nairobi riders only  
WHERE r.city = 'Nairobi'

I pointed out the repetition deliberately. "Notice how the CTE - rider_avg_fare or rider_spend - didn't change at all between these two questions. The aggregation logic is identical. What changed was which riders we cared about, and that change happened in one place: the WHERE clause on the outer query. That's the value of separating the calculation from the filter."

B5. Early vs. Late Month Trips

Did SafariRide do more business in the first or second half of each month?

CASE WHEN combined with EXTRACT - two sessions' worth of tools in one CTE:

WITH labelled_trips AS (
    SELECT trip_id, fare,
        CASE
            WHEN EXTRACT(DAY FROM trip_date) <= 15 THEN 'Early (1-15)'
            ELSE 'Late (16-31)'
        END AS month_period
    FROM safari.trips
)
SELECT month_period, COUNT(*) AS num_trips, SUM(fare) AS total_fare
FROM labelled_trips
GROUP BY month_period
ORDER BY num_trips DESC;

The CTE labels each trip as early or late using EXTRACT(DAY). The outer query summarises both groups. The compounding effect is real - every concept from previous sessions is usable inside a CTE.

---

Section C: Hard - Chained CTEs

C1. Underperforming Drivers

Drivers with 4+ trips AND an average rating below 3.0 - the ones who need coaching.

Two CTEs, chained: the second one reads from the first.

WITH driver_performance AS (
    SELECT driver_id,
        COUNT(*) AS total_trips,
        ROUND(AVG(rider_rating), 2) AS avg_rating
    FROM safari.trips
    GROUP BY driver_id
),
needs_coaching AS (
    SELECT driver_id, total_trips, avg_rating
    FROM driver_performance
    WHERE total_trips >= 4 AND avg_rating < 3.0
)
SELECT d.driver_name, nc.total_trips, nc.avg_rating
FROM safari.drivers d
JOIN needs_coaching nc ON d.driver_id = nc.driver_id
ORDER BY nc.avg_rating ASC;

driver_performance calculates everything. needs_coaching filters it. The outer query joins to get names. Three clear steps, each with a name and a single job.

The rule for chained CTEs: each one can reference any CTE defined before it. They run in order, top to bottom. Think of them as numbered steps - Step 1 can't reference Step 2, but Step 2 can reference Step 1.

C2. The Rewrite That Made the Case for CTEs

This was the session's most important moment. We wrote C2 two ways: subquery first, then CTE.

The subquery version - which we'd written earlier to show the problem:

SELECT r.rider_name,
    (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) AS total_spent
FROM safari.riders r
WHERE (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) > (
    SELECT AVG(spend) FROM (
        SELECT SUM(fare) AS spend FROM safari.trips GROUP BY rider_id
    ) sub
)

The same logic as a CTE:

WITH rider_spend AS (
    SELECT rider_id, SUM(fare) AS total_spent
    FROM safari.trips
    GROUP BY rider_id
),
spend_benchmark AS (
    SELECT AVG(total_spent) AS avg_spend
    FROM rider_spend
)
SELECT r.rider_name, rs.total_spent
FROM safari.riders r
JOIN rider_spend rs ON r.rider_id = rs.rider_id,
    spend_benchmark sb
WHERE rs.total_spent > sb.avg_spend
ORDER BY rs.total_spent DESC;

I asked the class which one they'd rather come back to in six months. Every hand went to the CTE version. Then I pointed at the subquery version and said: "The subquery calculates SUM(fare) for each rider twice - once in SELECT and once in WHERE. That's the same calculation written in two places. The CTE writes it once, names it rider_spend, and references it wherever needed. DRY - Don't Repeat Yourself - applies to SQL as much as it does to Python."

C4. Window Function Preview - RANK() Inside a CTE

The session closer was a preview of the next topic. You can't filter directly on a window function result in a WHERE clause - WHERE RANK() OVER (...) <= 3 is illegal. But you can calculate the rank inside a CTE and filter on it in the outer query.

WITH ranked_riders AS (
    SELECT r.rider_id, r.rider_name,
        SUM(t.fare) AS total_spent,
        RANK() OVER (ORDER BY SUM(t.fare) DESC) AS spend_rank
    FROM safari.riders r
    JOIN safari.trips t ON r.rider_id = t.rider_id
    GROUP BY r.rider_id, r.rider_name
)
SELECT rider_name, total_spent, spend_rank
FROM ranked_riders
WHERE spend_rank <= 3;

This is one of the most practical uses of CTEs in real analytics work. Window functions - RANK, ROW_NUMBER, DENSE_RANK - almost always require a CTE wrapper to filter on their results. We'll go deep on this next session. Today was just a look at the door.

C5 & C6: Busiest and Most Efficient

C5 chained two CTEs to find the best-rated drivers among the busiest. C6 was the "design your own" question - students chose a business question that needed two steps. The one I modelled: revenue per kilometre driven.

WITH driver_totals AS (
    SELECT driver_id, SUM(fare) AS total_fare, SUM(distance_km) AS total_km
    FROM safari.trips
    GROUP BY driver_id
),
efficiency AS (
    SELECT driver_id, total_fare, total_km,
        ROUND(total_fare / total_km, 2) AS revenue_per_km
    FROM driver_totals
)
SELECT d.driver_name, e.revenue_per_km
FROM safari.drivers d
JOIN efficiency e ON d.driver_id = e.driver_id
ORDER BY e.revenue_per_km DESC
LIMIT 5;

The design-your-own question produced the best student queries of the session. Once the CTE pattern is understood, the creativity goes into the business question rather than the syntax.

---

The Three Rules for Good CTEs

1. One job per CTE. If you find yourself explaining what a CTE does and the explanation has an "and" in it - split it.

2. Name it what it contains. driver_trip_counts is clear. temp1 is not. The name should make the outer query readable without needing to go back and read the CTE definition.

3. Comments explain WHY, not WHAT. -- Count trips per driver is obvious from the code. -- Using LEFT JOIN to preserve drivers with zero trips explains a decision the next reader wouldn't know to question.

---

Practice Problems

Section A style:

-- Write a CTE that finds the average distance_km per driver
-- Show only drivers whose average trip distance is above 15km
-- Order longest average first

Section B style:

-- Write a CTE that labels each trip as:
-- 'Short' (distance < 10km), 'Medium' (10–20km), 'Long' (> 20km)
-- Count trips and total fare per label

Section C style:

-- Chain two CTEs:
-- CTE 1: Each rider's trip count and total spend
-- CTE 2: Keep only Gold or Premium tier riders from safari.riders
-- Final: Show riders in CTE 2 who also appear in CTE 1 with total_spent > 2000
-- Ordered by total_spent descending

---

What I Noticed Teaching This Session

1. The subquery-vs-CTE comparison is the most important moment of the session. Showing the nested subquery first - ugly, repeated logic, hard to debug - and then rewriting it as two named CTEs makes the motivation for CTEs visceral rather than theoretical. Don't skip the ugly version.

2. Documentation changed how students thought about their queries. When I asked them to add a comment to each CTE step before running it, they had to articulate what the CTE was doing - and several caught logical errors before running anything. Writing the comment forced the thinking.

What's Next: Window Functions

CTEs gave us a way to filter on window function results. Next session we understand what those functions actually do.

WITH ranked AS (
    SELECT driver_name, total_trips,
        RANK() OVER (ORDER BY total_trips DESC) AS rank,
        ROUND(AVG(total_trips) OVER (), 1) AS avg_trips
    FROM driver_trip_counts
)
SELECT * FROM ranked WHERE rank <= 5;

RANK, ROW_NUMBER, DENSE_RANK, running totals, moving averages - all next week. And every one of them will use the CTE pattern we built today.

---

Try It Yourself

• - Download SafariDDL.sql

Start with Section A, work through in order. When you get to C2, write the subquery version first - even though it's ugly - before writing the CTE version. That contrast is the whole lesson.

---

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
I write weekly about what we covered.
Follow along or drop your questions in the comments.