SQL CTE: Common Table Expressions with Examples

-- Single CTE
WITH active_users AS (
  SELECT user_id, name, email
  FROM users
  WHERE status = 'active'
    AND last_login >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  au.name,
  au.email,
  COUNT(o.order_id) AS recent_orders
FROM active_users au
LEFT JOIN orders o ON au.user_id = o.user_id
  AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY au.name, au.email
ORDER BY recent_orders DESC;

-- CTE with explicit column aliases
WITH monthly_revenue (month, region, revenue) AS (
  SELECT
    DATE_TRUNC('month', order_date),
    region,
    SUM(amount)
  FROM orders
  GROUP BY 1, 2
)
SELECT month, region, revenue
FROM monthly_revenue
WHERE revenue > 50000
ORDER BY month, revenue DESC;

-- Find departments where average salary
-- exceeds the company average
WITH company_avg AS (
  SELECT AVG(salary) AS avg_salary
  FROM employees
)
SELECT
  department,
  ROUND(AVG(salary), 0) AS dept_avg,
  (SELECT avg_salary FROM company_avg) AS company_avg
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT avg_salary FROM company_avg);

Interview note: The company_avg CTE is referenced twice: once in SELECT and once in HAVING. Without a CTE, you would write the same subquery in both places. CTEs eliminate that duplication.

-- Walk an employee hierarchy from the CEO down
WITH RECURSIVE org_tree AS (
  -- Anchor: start at the CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of current level
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Interview note: Always include a depth or iteration counter and mention the safety limit. PostgreSQL defaults to no limit. SQL Server defaults to 100 (OPTION (MAXRECURSION N) to change it). In interviews, say: I would add a WHERE depth < 20 in the recursive member to prevent infinite loops from bad data.

WITH monthly_orders AS (
  -- Step 1: aggregate orders by user and month
  SELECT
    user_id,
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY user_id, DATE_TRUNC('month', order_date)
),
ranked AS (
  -- Step 2: rank users within each month
  SELECT *,
    RANK() OVER (
      PARTITION BY month ORDER BY total_spent DESC
    ) AS spend_rank
  FROM monthly_orders
),
top_spenders AS (
  -- Step 3: keep top 10 per month
  SELECT * FROM ranked WHERE spend_rank <= 10
)
-- Step 4: join back to users for names
SELECT
  u.name,
  ts.month,
  ts.order_count,
  ts.total_spent,
  ts.spend_rank
FROM top_spenders ts
INNER JOIN users u ON ts.user_id = u.id
ORDER BY ts.month, ts.spend_rank;

Interview note: Name your CTEs after what they compute, not after where they sit in the pipeline. Names like step1 and temp make the interviewer do extra work to understand your query. Names like monthly_orders and top_spenders are self-documenting.

-- Subquery version
SELECT department, avg_sal
FROM (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) dept_avgs
WHERE avg_sal > (
  SELECT AVG(salary) FROM employees
);

-- CTE version
WITH dept_avgs AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
),
company_avg AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)
SELECT d.department, d.avg_sal
FROM dept_avgs d
WHERE d.avg_sal > (SELECT avg_sal FROM company_avg);

-- Generate every date in January 2024
WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS dt

  UNION ALL

  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt < DATE '2024-01-31'
)
SELECT dt FROM date_series;

WITH RECURSIVE category_path AS (
  -- Anchor: top-level categories (no parent)
  SELECT
    id,
    name,
    name AS full_path,
    1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: append child name to parent path
  SELECT
    c.id,
    c.name,
    cp.full_path || ' > ' || c.name,
    cp.depth + 1
  FROM categories c
  INNER JOIN category_path cp ON c.parent_id = cp.id
)
SELECT full_path, depth
FROM category_path
ORDER BY full_path;

-- Guard against cycles with a depth limit
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 50  -- safety limit
)
SELECT * FROM org_tree;

-- SQL Server: set explicit recursion limit
-- SELECT * FROM org_tree OPTION (MAXRECURSION 200);

Interview note: Mentioning cycle detection unprompted shows production experience. Say: In production I would add a depth limit and optionally track visited IDs in an array to detect cycles explicitly. PostgreSQL supports array accumulation for this: WHERE NOT e.id = ANY(visited_ids).