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;

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;

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 (...)WITH name AS (...) block as a labeled step in a pipeline. Later steps (and the final SELECTSELECT) 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.

🧪 Try It Yourself

Exercise 1 – A single CTE

Exercise 2 – Two CTEs joined together

Exercise 3 – Filter with a subquery in WHERE

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

If this helped you, consider buying me a coffee ☕

Buy me a coffee