Skip to content
Python Central Hub

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.

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did