CTEs and Window Functions: Analytical SQL

-- Without CTE (hard to read)
SELECT name, total_spent
FROM (
    SELECT c.name, SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE o.status = 'completed'
    GROUP BY c.id, c.name
) AS customer_totals
WHERE total_spent > 1000;

-- With CTE (same result, much clearer)
WITH customer_totals AS (
    SELECT c.name, SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE o.status = 'completed'
    GROUP BY c.id, c.name
)
SELECT name, total_spent
FROM customer_totals
WHERE total_spent > 1000
ORDER BY total_spent DESC;

WITH
monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
),
prev_month AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    prev_revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) AS growth_pct
FROM prev_month
ORDER BY month;

-- Running total of revenue over time
SELECT
    created_at::DATE                        AS date,
    amount,
    SUM(amount) OVER (ORDER BY created_at)  AS running_total
FROM orders
WHERE status = 'completed'
ORDER BY created_at;

-- Rank customers by total spending
SELECT
    c.name,
    SUM(o.amount)                           AS total_spent,
    RANK() OVER (ORDER BY SUM(o.amount) DESC) AS spending_rank
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Partition by — separate window per group
SELECT
    c.name,
    o.status,
    o.amount,
    SUM(o.amount) OVER (PARTITION BY o.status) AS status_total
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- ROW_NUMBER: unique number per row within partition
SELECT
    customer_id,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at
    ) AS order_number   -- 1st, 2nd, 3rd order per customer

FROM orders;

-- LEAD and LAG: access adjacent rows
SELECT
    created_at::DATE    AS date,
    amount,
    LAG(amount)  OVER (ORDER BY created_at) AS prev_day_amount,
    LEAD(amount) OVER (ORDER BY created_at) AS next_day_amount
FROM orders;

-- NTILE: divide rows into N buckets
SELECT
    name,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM customer_totals;