SQL CTEs and WITH Clause

A named, temporary result set that makes a query readable top to bottom instead of inside out.

What & Why

A Common Table Expression (CTE) is a named query you define with WITH, then reference later in the same statement as if it were a real table. It exists only for the duration of that one query.

The problem it solves: nested subqueries force you to read from the inside out. A CTE lets you name each step and read the whole query top to bottom, like a short paragraph.

See How It Works

BUSINESS QUESTION

Build monthly feedback counts first, then present them in calendar order.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

WITH monthly_feedback AS (
  SELECT
    DATE_TRUNC('month', created_at)::date AS month_start,
    COUNT(*) AS feedback_count
  FROM product.feedback
  GROUP BY 1
)
SELECT
  month_start,
  feedback_count
FROM monthly_feedback
ORDER BY month_start;

RESULT — monthly feedback counts

month_start	feedback_count
2024-01-01	1
2024-02-01	1
2024-03-01	1
2024-04-01	1

The CTE groups the four representative feedback rows before the final ordered SELECT.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice