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

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.

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.

  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"]

Check your understanding

🧪 Try It Yourself

Exercise 1 – A single CTE

Press Run to see what your code prints.

Exercise 2 – Two CTEs joined together

Press Run to see what your code prints.

Exercise 3 – Filter with a subquery in WHERE

Press Run to see what your code prints.

Next

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