WITH cte_name AS (
  SELECT ...   -- CTE body
)
SELECT *
FROM cte_name;  -- reference the CTE here

-- Without CTE: deeply nested subquery
SELECT *
FROM (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
) AS stats
WHERE total_spent > 500;

-- With CTE: named step, much more readable
WITH customer_stats AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT *
FROM customer_stats
WHERE total_spent > 500
ORDER BY total_spent DESC;

WITH
-- Step 1: summarize orders per customer
customer_orders AS (
  SELECT
    customer_id,
    COUNT(*)             AS order_count,
    ROUND(SUM(total), 2) AS total_spent,
    MAX(created_at)      AS last_order_date
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
),

-- Step 2: calculate overall averages
overall_stats AS (
  SELECT
    ROUND(AVG(total_spent), 2)  AS avg_lifetime_value,
    ROUND(AVG(order_count), 1)  AS avg_orders_per_customer
  FROM customer_orders
),

-- Step 3: classify customers (references customer_orders)
classified AS (
  SELECT
    co.*,
    CASE
      WHEN co.total_spent >= 2 * os.avg_lifetime_value THEN 'VIP'
      WHEN co.total_spent >= os.avg_lifetime_value      THEN 'Regular'
      ELSE                                                   'Low-value'
    END AS segment
  FROM customer_orders AS co
  CROSS JOIN overall_stats AS os
)

-- Final query: join classified customers with their details
SELECT
  c.customer_id,
  c.first_name,
  c.email,
  cl.order_count,
  cl.total_spent,
  cl.last_order_date,
  cl.segment
FROM customers  AS c
JOIN classified AS cl ON c.customer_id = cl.customer_id
ORDER BY cl.total_spent DESC;

WITH monthly_revenue AS (
  SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    ROUND(SUM(total), 2)             AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
-- Reference the same CTE twice in the main query
SELECT
  cur.month,
  cur.revenue,
  prev.revenue       AS prev_month_revenue,
  ROUND(cur.revenue - COALESCE(prev.revenue, 0), 2) AS growth
FROM monthly_revenue AS cur
LEFT JOIN monthly_revenue AS prev
  ON prev.month = DATE_FORMAT(
       DATE_SUB(STR_TO_DATE(CONCAT(cur.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH),
       '%Y-%m')
ORDER BY cur.month;

WITH RECURSIVE cte_name AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive member: references cte_name
  SELECT ...
  FROM cte_name
  WHERE stop_condition
)
SELECT * FROM cte_name;

-- Traverse the employee hierarchy from CEO down to all reports
WITH RECURSIVE org_chart AS (
  -- Anchor: start at the top (employees with no manager)
  SELECT
    employee_id,
    full_name,
    manager_id,
    job_title,
    0 AS depth,
    CAST(full_name AS CHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: add direct reports of the previous level
  SELECT
    e.employee_id,
    e.full_name,
    e.manager_id,
    e.job_title,
    oc.depth + 1,
    CONCAT(oc.path, ' > ', e.full_name)
  FROM employees  AS e
  JOIN org_chart  AS oc ON e.manager_id = oc.employee_id
)
SELECT
  REPEAT('  ', depth) AS indent,   -- visual indentation
  full_name,
  job_title,
  depth,
  path
FROM org_chart
ORDER BY path;

-- Generate integers 1 through 100 using recursive CTE
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

-- Use it to generate a 30-day calendar
WITH RECURSIVE date_series AS (
  SELECT CURDATE() AS dt
  UNION ALL
  SELECT DATE_ADD(dt, INTERVAL 1 DAY)
  FROM date_series
  WHERE dt < DATE_ADD(CURDATE(), INTERVAL 29 DAY)
)
SELECT dt AS calendar_date, DAYNAME(dt) AS day_of_week
FROM date_series
ORDER BY dt;

-- Generate the first 15 Fibonacci numbers
WITH RECURSIVE fibonacci AS (
  SELECT 1 AS n, 0 AS a, 1 AS b   -- n = position, a = F(n-1), b = F(n)
  UNION ALL
  SELECT n + 1, b, a + b
  FROM fibonacci
  WHERE n < 15
)
SELECT n AS position, a AS fibonacci_number
FROM fibonacci
ORDER BY n;

-- Given a category ID, find all its parent categories up to the root
WITH RECURSIVE ancestors AS (
  -- Anchor: start at the target category
  SELECT category_id, name, parent_id, 0 AS level
  FROM categories
  WHERE category_id = 42    -- starting node

  UNION ALL

  -- Recursive: join to parent
  SELECT c.category_id, c.name, c.parent_id, a.level + 1
  FROM categories   AS c
  JOIN ancestors    AS a ON c.category_id = a.parent_id
)
SELECT category_id, name, level
FROM ancestors
ORDER BY level DESC;   -- root first

-- Increase the recursion depth limit for deeper hierarchies
SET SESSION cte_max_recursion_depth = 5000;

-- Or set it globally
SET GLOBAL cte_max_recursion_depth = 5000;

-- Check current depth
SELECT @@cte_max_recursion_depth;

-- Full customer cohort analysis: acquisition month, orders, revenue
WITH
cohorts AS (
  SELECT
    customer_id,
    DATE_FORMAT(MIN(created_at), '%Y-%m') AS cohort_month
  FROM orders
  GROUP BY customer_id
),
cohort_revenue AS (
  SELECT
    co.cohort_month,
    DATE_FORMAT(o.created_at, '%Y-%m') AS order_month,
    COUNT(DISTINCT o.customer_id)       AS active_customers,
    ROUND(SUM(o.total), 2)              AS revenue
  FROM orders   AS o
  JOIN cohorts  AS co USING (customer_id)
  WHERE o.status = 'delivered'
  GROUP BY co.cohort_month, order_month
)
SELECT
  cohort_month,
  order_month,
  active_customers,
  revenue
FROM cohort_revenue
ORDER BY cohort_month, order_month;