CTEs: Readable Query Pipelines

A CTE is a named query step at the top of a statement. It starts with WITH, gives a subquery a temporary name, and lets the final query read like a small pipeline instead of a stack of parentheses.

You have already used subqueries and derived tables. CTEs do not unlock a new kind of result; they unlock a clearer way to build one step at a time.

WITH: naming a step

CTE stands for common table expression. The shape is WITH step_name AS (...) SELECT ... FROM step_name;. The named step exists only for this statement. It is not stored, and it does not change the database. Here we name the line-level revenue calculation before summarizing by branch:

WITH line_revenue AS (
  SELECT cs.line_id,
         cs.ticket_id,
         cs.branch,
         cs.channel,
         cs.category,
         cs.quantity * cs.unit_price * (1 - cs.discount_pct / 100.0) AS net_amount
  FROM cafe_sales AS cs
)
SELECT lr.branch,
       round(sum(lr.net_amount), 2) AS revenue
FROM line_revenue AS lr
GROUP BY lr.branch
ORDER BY revenue DESC, lr.branch;

 branch | revenue
--------+---------
 Center |  198.20
 Harbor |  164.31
 Campus |  105.08
(3 rows)

The final query never has to repeat quantity * unit_price * (1 - discount_pct / 100.0). It reads from line_revenue, the name we gave that expression-bearing result. Same answer as a derived table, but the first thing your reader sees is the step name, not an opening parenthesis.

Debug by selecting a middle step

The practical gift of CTEs is not just pretty formatting. When a query gets suspicious, you can temporarily replace the final query with a SELECT from a middle CTE and inspect the shape you are about to build on.

WITH line_revenue AS (
  SELECT cs.line_id,
         cs.ticket_id,
         cs.branch,
         cs.channel,
         cs.category,
         cs.quantity * cs.unit_price * (1 - cs.discount_pct / 100.0) AS net_amount
  FROM cafe_sales AS cs
)
SELECT lr.line_id,
       lr.ticket_id,
       lr.branch,
       round(lr.net_amount, 2) AS net_amount
FROM line_revenue AS lr
ORDER BY lr.line_id
LIMIT 5;

 line_id | ticket_id | branch | net_amount
---------+-----------+--------+------------
       1 |      1001 | Center |       2.50
       2 |      1001 | Center |       3.20
       3 |      1002 | Campus |       3.80
       4 |      1002 | Campus |       3.60
       5 |      1003 | Harbor |       8.00
(5 rows)

That debugging move is simple and powerful: keep the WITH, change the final SELECT, run the query, check the row count and columns, then move one step later. It is the same "peel back to the earliest verifiable stage" habit from 4.4, now with named stages.

Build pipelines: one transformation per CTE

Readable CTEs are not a place to hide complexity. They are a place to separate it. Give each CTE one job, and make later CTEs read from earlier ones:

WITH line_revenue AS (
  SELECT cs.line_id,
         cs.ticket_id,
         cs.branch,
         cs.channel,
         cs.category,
         cs.quantity * cs.unit_price * (1 - cs.discount_pct / 100.0) AS net_amount
  FROM cafe_sales AS cs
),
ticket_totals AS (
  SELECT lr.ticket_id,
         lr.branch,
         lr.channel,
         sum(lr.net_amount) AS ticket_total
  FROM line_revenue AS lr
  GROUP BY lr.ticket_id, lr.branch, lr.channel
),
branch_stats AS (
  SELECT tt.branch,
         count(*) AS tickets,
         round(sum(tt.ticket_total), 2) AS revenue,
         round(avg(tt.ticket_total), 2) AS avg_ticket
  FROM ticket_totals AS tt
  GROUP BY tt.branch
)
SELECT bs.branch,
       bs.tickets,
       bs.revenue,
       bs.avg_ticket
FROM branch_stats AS bs
WHERE bs.avg_ticket >= 11.00
ORDER BY bs.avg_ticket DESC, bs.branch;

 branch | tickets | revenue | avg_ticket
--------+---------+---------+------------
 Harbor |      13 |  164.31 |      12.64
 Center |      17 |  198.20 |      11.66
(2 rows)

Read it top to bottom:

• line_revenue computes one clean line amount.
• ticket_totals changes the grain to one row per ticket.
• branch_stats changes the grain to one row per branch.
• The final query filters the already-computed branch summaries.

That last line is the binding pattern for later: compute first, filter one step later. Today it filters an average ticket value. In Section 13, when a future result is computed too late to use directly in WHERE, this same pipeline shape will solve it: compute the value in one named step, then filter from the next step.

CTE, subquery, or view?

Choose the container by how long the name needs to live.

Use a subquery when the inner query is short and local, especially when it fits naturally in WHERE or FROM without making the reader scroll. Use a CTE when a single statement has multiple conceptual steps, when you want to reference a step more than once, or when debugging a middle result would help. Use a view when the query deserves a reusable database object shared across statements or across people; views are a schema tool, so Section 11 gives them their proper treatment.

Try the full pipeline here. Then debug it by changing the final query to read named columns from ticket_totals and running again.

You now have two ways to tame nested SQL: derived tables for small one-off results, and CTEs for named pipelines. Next lesson changes the shape of the result itself with set operations: stacking, intersecting, and subtracting compatible result sets.