WITH (CTEs)

-- 1) Basic CTE — readable replacement for nested subqueries
WITH recent_orders AS (
    SELECT customer_id, SUM(total_cents) AS spent_cents, COUNT(*) AS n
    FROM orders
    WHERE created_at >= NOW() - INTERVAL 30 DAY
      AND status     = 'paid'
    GROUP BY customer_id
)
SELECT c.id, c.name, ro.spent_cents, ro.n
FROM customers c
JOIN recent_orders ro ON ro.customer_id = c.id
WHERE ro.spent_cents > 100000
ORDER BY ro.spent_cents DESC;

-- 2) Multiple CTEs in one query
WITH
  recent_orders AS (
    SELECT customer_id, SUM(total_cents) AS spent
    FROM orders
    WHERE created_at >= NOW() - INTERVAL 30 DAY
    GROUP BY customer_id
  ),
  loyalty AS (
    SELECT customer_id, COUNT(*) AS years
    FROM customer_anniversaries
    GROUP BY customer_id
  )
SELECT c.name, r.spent, COALESCE(l.years, 0) AS years
FROM customers c
LEFT JOIN recent_orders r ON r.customer_id = c.id
LEFT JOIN loyalty l       ON l.customer_id = c.id
ORDER BY r.spent DESC NULLS LAST
LIMIT 50;

-- 3) Recursive CTE — walk a tree (org chart, comment threads, BOM)
CREATE TABLE employees (
    id        INT PRIMARY KEY,
    name      VARCHAR(100),
    manager_id INT NULL,
    salary_cents BIGINT
);

-- Find everyone in the reporting chain under employee 1
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1                                  -- anchor: the starting node

    UNION ALL

    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id              -- recursive step
)
SELECT id, name, depth FROM org ORDER BY depth, name;

-- 4) Recursive CTE — generate a date series (no calendar table needed)
WITH RECURSIVE days AS (
    SELECT DATE('2025-01-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2025-12-31'
)
SELECT d FROM days;

-- MySQL caps recursion at @@cte_max_recursion_depth (default 1000).
-- SET SESSION cte_max_recursion_depth = 5000;     -- when you need more

-- 5) Stepwise transformations — each CTE refines the previous
WITH
  raw AS (
    SELECT user_id, JSON_EXTRACT(payload, '$.event') AS event,
                 created_at
    FROM analytics_events
    WHERE created_at >= NOW() - INTERVAL 7 DAY
  ),
  per_user AS (
    SELECT user_id,
                 SUM(CASE WHEN event = '"signup"' THEN 1 ELSE 0 END)   AS signups,
                 SUM(CASE WHEN event = '"purchase"' THEN 1 ELSE 0 END) AS purchases
    FROM raw
    GROUP BY user_id
  ),
  funnel AS (
    SELECT signups, purchases, purchases / NULLIF(signups, 0) AS rate
    FROM per_user
    WHERE signups > 0
  )
SELECT AVG(rate) AS avg_conversion FROM funnel;

-- 6) CTE vs subquery — when each wins
-- CTE wins for:
--   • Reading top-to-bottom
--   • Reusing the same subset multiple times in one query
--   • Recursive structures (only CTE can express them)
--   • Stepwise debugging (run just the WITH ... SELECT * FROM step;)
-- Subquery wins for:
--   • Single tiny use, especially correlated
--   • Pre-MySQL 8.0

-- 7) UPDATE / DELETE with a CTE (MySQL 8.0.1+)
WITH dupes AS (
    SELECT id,
                 ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE u FROM users u
JOIN dupes d ON d.id = u.id AND d.rn > 1;

-- 8) Materialization — MySQL does NOT have an INLINE/MATERIALIZE hint
-- The optimizer may evaluate a CTE once or inline it per reference.
-- For very heavy CTEs reused N times, consider a temp table:
CREATE TEMPORARY TABLE t_recent AS
    SELECT customer_id, SUM(total_cents) AS spent
    FROM orders WHERE created_at >= NOW() - INTERVAL 30 DAY
    GROUP BY customer_id;
CREATE INDEX ix_t_recent ON t_recent (customer_id);
-- ... use t_recent multiple times ...
DROP TEMPORARY TABLE t_recent;

-- 9) Avoiding accidental recursion
-- A CTE name that appears in its own definition outside RECURSIVE is an error.
-- Always write WITH RECURSIVE when the body references the CTE.

-- 10) Recursive UNION rules
--   • UNION ALL: efficient, may produce duplicates
--   • UNION:     dedupes per step, more expensive
--   • The recursive arm must reference the CTE exactly once
--   • No aggregates, GROUP BY, or DISTINCT in the recursive arm

-- 11) Performance tips
--   • Index columns the recursive step joins on
--   • Keep the anchor selective; broad anchors expand badly
--   • Add explicit LIMIT or depth guard for safety
--   • EXPLAIN ANALYZE  to see how the optimizer treats your CTE

-- 12) Common bugs
--   • WITH RECURSIVE without the RECURSIVE keyword → error
--   • cte_max_recursion_depth hit → 'Recursive query aborted after 1000 iterations'
--   • Naming a CTE the same as a real table → confusion (parser prefers the CTE)
--   • Trying to UPDATE a CTE directly → CTEs are read-only; UPDATE the base table joined to the CTE
--   • Comma between CTE and main query (e.g. WITH x AS (...), SELECT ...) → no comma before SELECT
--   • Expecting MATERIALIZE — MySQL inlines or materializes at its discretion

WITH recent AS (
    SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL 7 DAY
)
SELECT user_id, SUM(total) AS spent
FROM recent
GROUP BY user_id;