CTEs (WITH) and Subqueries

Why CTEs

CTEs (Common Table Expressions) improve readability.

• Break complex queries into steps
• Reuse derived results

Example: revenue by country for paying users

CTE example

WITH paying_users AS (
  SELECT user_id, country
  FROM users
  WHERE plan != 'free'
),
revenue_by_user AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
)
SELECT
  u.country,
  SUM(r.revenue) AS revenue
FROM paying_users u
JOIN revenue_by_user r ON r.user_id = u.user_id
GROUP BY u.country
ORDER BY revenue DESC;

Subqueries

Subqueries are inline “nested” queries.

Subquery example

SELECT *
FROM (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
) t
WHERE t.revenue >= 1000;

Practical guidance

• Prefer CTEs for multi-step analytics.
• Keep each CTE small and well-named.

A CTE is a named pipeline stage

Think of each WITH name AS (...) block as a labeled step in a pipeline. Later steps (and the final SELECT) can reference any earlier CTE by name, the same way a variable in a script holds a value you compute once and reuse.

diagram CTE pipeline mermaid

  flowchart LR
  A["users table"] --> B["paying_users AS (...)"]
  C["orders table"] --> D["revenue_by_user AS (...)"]
  B --> E["Final SELECT joins both CTEs"]
  D --> E
  E --> F["Result: revenue by country"]

Each WITH block is a named, reusable step; later steps and the final SELECT can reference any earlier CTE by name.

Analyst’s tip

A CTE only exists for the duration of the one query it’s attached to — it is not a saved table, so you can’t reference it from a different query later. If you find yourself copying the same CTE into multiple queries, that’s usually a sign it should become a real view or a pandas helper function instead.

🧪 Try It Yourself

Exercise 1 – A single CTE

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Two CTEs joined together

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Filter with a subquery in WHERE

pch.exerciseTag pch.exerciseTitle

Next

Date and Time Analytics in SQL applies these same aggregation and window patterns to the most common analytics dimension of all: time.