Advanced SQL — CTEs & Window Functions

WITH active_users AS (
    SELECT id, username, email
    FROM users
    WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM active_users;

WITH
    active_users AS (
        SELECT id, email FROM users
        WHERE last_login > NOW() - INTERVAL '30 days'
    ),
    recent_orders AS (
        SELECT user_id, COUNT(*) AS order_count
        FROM orders
        WHERE created_at > NOW() - INTERVAL '30 days'

WITH
    order_totals AS (
        SELECT order_id, SUM(amount_cents) AS total
        FROM order_items
        GROUP BY order_id
    ),
    user_spend AS (
        SELECT o.user_id, SUM(ot.total) AS lifetime_spend
        FROM order_totals ot
        JOIN orders o ON o.id

WITH RECURSIVE org_tree AS (
    -- Anchor: top-level manager (CEO)
    SELECT id, name, manager_id, 1 AS depth, ARRAY[name] AS path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive: employees who report to someone in the tree
    SELECT
        e.id,
        e.name

WITH RECURSIVE comment_thread AS (
    -- Anchor: top-level comments
    SELECT id, parent_id, content, 1 AS depth, id::TEXT AS path
    FROM comments
    WHERE parent_id IS NULL AND post_id = 42
 
    UNION ALL
 
    -- Recursive: replies
    SELECT
        c.id, c.parent_id

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees;

  name   | department   | salary   | overall_rank | dept_rank
---------+-------------+----------+--------------+-----------
 Frank   | Engineering  | 1800000  | 1            | 1
 Charlie | Engineering  | 1500000  | 2            | 2
 Alice   | Engineering  | 1200000  | 3            | 3
 Eve     | Marketing    | 1100000  | 4            | 1
 Bob     | Marketing    | 800000   | 5            | 2
 Diana   | Sales        | 950000   | 6            | 1
 Grace   | Sales        | 700000   | 7            | 2

SELECT
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC),
    DENSE_RANK() OVER (ORDER BY salary DESC)
FROM employees;

-- Previous month's salary (for comparison)
SELECT
    name,
    salary,
    LAG(salary) OVER (PARTITION BY name ORDER BY year) AS prev_salary,
    salary - LAG(salary) OVER (PARTITION BY name ORDER BY year) AS raise_amount
FROM salary_history;

SELECT
    created_at::DATE AS order_date,
    total_cents,
    SUM(total_cents) OVER (ORDER BY created_at::DATE) AS running_total
FROM orders
WHERE status = 'completed';

SELECT
    created_at::DATE AS order_date,
    COUNT(*) AS daily_orders,
    AVG(COUNT(*)) OVER (
        ORDER BY created_at::DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM orders
GROUP BY created_at::DATE;

SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

WITH
    -- Step 1: Find pageview sessions with gaps > 30 min
    pageviews_with_gaps AS (
        SELECT
            user_id,
            page_url,
            created_at,
            LAG(created_at) OVER (
                PARTITION BY user_id ORDER BY created_at
            ) AS prev_pageview_at
        FROM analytics_pageviews
    ),
    -- Step 2: Mark new sessions
    sessions AS (
        SELECT