CTEs: Making Complex Queries Readable

A CTE, common table expression, lets you name an intermediate step, so a long query reads like a sequence of clearly labeled steps instead of a wall of nested subqueries.

Compare the subquery version from Lesson 3.3 to the same logic written as a CTE:

WITH user_totals AS (

SELECT user_id, SUM(amount) AS total_spent

FROM orders

GROUP BY user_id

),

segmented AS (

SELECT

user_id,

CASE

WHEN total_spent >= 1000 THEN 'high_value'

WHEN total_spent >= 100 THEN 'mid_value'

ELSE 'low_value'

END AS value_segment

FROM user_totals

)

SELECT value_segment, COUNT(*) AS num_users

FROM segmented

GROUP BY value_segment;

Nothing about what this query calculates changed. What changed is readability: each WITH block is a named, self-contained step, user_totals, then segmented, then the final aggregation, that you and anyone reviewing your query can reason about one piece at a time. As a PM, you'll read far more SQL than you write, especially other people's dashboard queries, and CTEs are the difference between a query you can actually follow and one that's an unreadable wall of nested parentheses.

YOUR MOVE THIS WEEK

Find the longest, most nested query you have access to. See if it uses CTEs. If it doesn't, and it's genuinely hard to read, that's a legitimate, low-stakes suggestion to bring to whoever maintains it: "would this be easier to review as a CTE?"