SQL CTEs: WITH Clause and Recursive Queries

SQL CTEs — Common Table Expressions

CTEs let you name a subquery and reference it like a table. They make complex queries dramatically more readable and enable recursive queries that would be impossible with regular SQL.

-- Basic CTE
WITH active_users AS (
    SELECT id, name, email, created_at
    FROM   users
    WHERE  active = 1 AND email_verified = 1
)
SELECT u.name, COUNT(p.id) AS post_count
FROM   active_users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'published'
GROUP BY u.id, u.name
ORDER BY post_count DESC;

-- Multiple CTEs
WITH
  user_stats AS (
      SELECT user_id, COUNT(*) AS posts, SUM(views) AS total_views
      FROM   posts WHERE status = 'published'
      GROUP BY user_id
  ),
  top_authors AS (
      SELECT user_id FROM user_stats WHERE posts >= 5
  )
SELECT u.name, s.posts, s.total_views
FROM   users u
JOIN   user_stats  s ON s.user_id = u.id
JOIN   top_authors t ON t.user_id = u.id
ORDER BY s.total_views DESC
LIMIT 10;

Recursive CTEs

-- Employee hierarchy tree
WITH RECURSIVE org_chart AS (
    -- Anchor: start with CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth, CAST(name AS CHAR(1000)) AS path
    FROM   staff WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find employees reporting to previous level
    SELECT s.id, s.name, s.manager_id, oc.depth + 1,
           CONCAT(oc.path, ' > ', s.name)
    FROM   staff s
    JOIN   org_chart oc ON oc.id = s.manager_id
)
SELECT REPEAT('  ', depth) || name AS org_chart, path
FROM   org_chart
ORDER BY path;

-- Output:
-- CEO
--   Engineering Head
--     Senior Dev
--       Junior Dev
--   Marketing Head

CTE for Running Totals

WITH daily_sales AS (
    SELECT DATE(created_at) AS day, SUM(amount) AS revenue
    FROM orders WHERE status = 'paid'
    GROUP BY DATE(created_at)
),
running AS (
    SELECT day, revenue,
           SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue,
           AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
    FROM daily_sales
)
SELECT * FROM running ORDER BY day;