snippetModeratepending

SQL common table expressions (CTEs) — readable complex queries

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

CTEWITH clauserecursive CTEsubqueryreadable SQLhierarchical

linux

Problem

Complex SQL queries with nested subqueries are hard to read, debug, and maintain. Need a way to break them into named, composable steps.

Solution

CTEs (WITH clauses) let you name subqueries and reference them like tables. Supports recursive CTEs for hierarchical data.

Code Snippets

CTEs for readable queries and recursive tree traversal

-- Basic CTE: break complex query into steps
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
  SELECT u.id, u.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
  FROM active_users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.created_at > NOW() - INTERVAL '90 days'
  GROUP BY u.id, u.name
)
SELECT name, order_count, total_spent,
       RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
FROM user_orders
WHERE order_count >= 3
ORDER BY total_spent DESC;

-- Recursive CTE: org chart / tree traversal
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive case: employees under each manager
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT REPEAT('  ', depth - 1) || name AS org_chart, depth
FROM org_tree
ORDER BY depth, name;

Revisions (0)

No revisions yet.