CTEs (WITH)

-- 1) Basic CTE — read top-to-bottom
WITH recent_orders AS (
    SELECT id, user_id, total, created_at
    FROM orders
    WHERE created_at >= now() - interval '30 days'
)
SELECT u.email, sum(o.total) AS revenue
FROM recent_orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.email
ORDER BY revenue DESC
LIMIT 50;

-- 2) Multiple CTEs — pipeline of named steps
WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
), per_user AS (
    SELECT user_id, count(*) n, sum(total) revenue
    FROM paid
    GROUP BY user_id
), top_50 AS (
    SELECT * FROM per_user ORDER BY revenue DESC LIMIT 50
)
SELECT t.*, u.email FROM top_50 t JOIN users u ON u.id = t.user_id;

-- 3) Recursive CTE — walk a tree (e.g. nested categories)
WITH RECURSIVE descendants AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE id = 5                              -- start node
    UNION ALL
    SELECT c.id, c.parent_id, c.name, d.depth + 1
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants ORDER BY depth, name;

-- 4) Recursive CTE — generate a series of dates (no need for generate_series)
WITH RECURSIVE days AS (
    SELECT date '2026-06-01' AS d
    UNION ALL
    SELECT d + 1 FROM days WHERE d < date '2026-06-30'
)
SELECT d FROM days;

-- 5) Modifying CTE — INSERT/UPDATE/DELETE inside a WITH and return rows
WITH deactivated AS (
    UPDATE users
    SET status = 'inactive'
    WHERE last_seen < now() - interval '180 days'
    RETURNING id, email
)
INSERT INTO audit_log(action, user_id, payload)
SELECT 'deactivate', id, jsonb_build_object('email', email)
FROM deactivated;

-- 6) MATERIALIZED vs NOT MATERIALIZED (PG 12+) — control inlining
WITH base AS NOT MATERIALIZED (
    SELECT * FROM big_table WHERE region = 'AU'
)
SELECT count(*) FROM base WHERE active;
-- NOT MATERIALIZED lets the planner push predicates IN; better most of the time.

-- 7) Window function combined with CTE
WITH ranked AS (
    SELECT o.*,
           row_number() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders o
)
SELECT * FROM ranked WHERE rn <= 3;       -- top-3 orders per user

WITH recent AS (
    SELECT * FROM orders WHERE created_at >= now() - interval '7 days'
)
SELECT user_id, sum(total) AS spent
FROM recent
GROUP BY user_id
ORDER BY spent DESC;