SQL CTEs: Write Clearer Queries with Common Table Expressions

WITH recent_orders AS (
  SELECT customer_id, order_id, total_amount, order_date
  FROM orders
  WHERE order_date >= '2025-01-01'
)
SELECT
  c.customer_name,
  COUNT(ro.order_id) as order_count,
  SUM(ro.total_amount) as total_spent
FROM customers c
JOIN recent_orders ro ON c.customer_id = ro.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

WITH active_customers AS (
  SELECT customer_id, customer_name
  FROM customers
  WHERE status = 'active'
),
customer_orders AS (
  SELECT
    ac.customer_id,
    ac.customer_name,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent
  FROM active_customers ac
  LEFT JOIN orders o ON ac.customer_id = o.customer_id
  GROUP BY ac.customer_id, ac.customer_name
)
SELECT *
FROM customer_orders
WHERE order_count >= 5 OR total_spent > 1000
ORDER BY total_spent DESC;

WITH RECURSIVE employee_hierarchy AS (
  -- Anchor: start with top-level employees
  SELECT employee_id, employee_name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: get employees who report to current level
  SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, employee_name;

WITH sales_data AS (
  SELECT
    product_id,
    SUM(quantity) as total_quantity,
    SUM(quantity * price) as total_revenue
  FROM order_items
  GROUP BY product_id
),
enriched_data AS (
  SELECT
    p.product_name,
    p.category,
    sd.total_quantity,
    sd.total_revenue,
    sd.total_revenue / NULLIF(sd.total_quantity, 0) as avg_price
  FROM sales_data sd
  JOIN products p ON sd.product_id = p.product_id
),
categorized_data AS (
  SELECT
    *,
    CASE
      WHEN total_revenue > 10000 THEN 'High Performer'
      WHEN total_revenue > 5000 THEN 'Medium Performer'
      ELSE 'Low Performer'
    END as performance_tier
  FROM enriched_data
)
SELECT * FROM categorized_data
ORDER BY total_revenue DESC;

WITH customer_stats AS (
  SELECT
    customer_id,
    COUNT(*) as order_count,
    AVG(total_amount) as avg_order_value
  FROM orders
  GROUP BY customer_id
)
SELECT
  cs1.customer_id as customer_1,
  cs2.customer_id as customer_2,
  ABS(cs1.avg_order_value - cs2.avg_order_value) as value_difference
FROM customer_stats cs1
JOIN customer_stats cs2 ON cs1.customer_id < cs2.customer_id
WHERE ABS(cs1.avg_order_value - cs2.avg_order_value) < 10
ORDER BY value_difference;

WITH expensive_calculation AS (
  SELECT
    date_trunc('month', order_date) as month,
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as monthly_total,
    AVG(total_amount) as avg_order
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY date_trunc('month', order_date), customer_id
)
SELECT
  month,
  SUM(order_count) as total_orders,
  AVG(monthly_total) as avg_customer_spend,
  COUNT(DISTINCT customer_id) as active_customers
FROM expensive_calculation
GROUP BY month
ORDER BY month;