Advanced SQL: Window Functions, CTEs & Recursive Queries

-- Reuse from DB-2: customers, products, orders, order_items
 
-- Add an employees table for hierarchy examples
CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    title      VARCHAR(100) NOT NULL,
    department VARCHAR(50)  NOT NULL,
    salary     NUMERIC(10, 2) NOT NULL,
    manager_id INT REFERENCES employees(id),
    hired_at   DATE NOT NULL
);
 
INSERT INTO employees (name, title, department, salary, manager_id, hired_at) VALUES
    ('Sarah Chen',    'CEO',              'Executive',   250000, NULL, '2018-01-15'),
    ('Mike Ross',     'VP Engineering',   'Engineering', 200000, 1,    '2018-06-01'),
    ('Lisa Park',     'VP Sales',         'Sales',       190000, 1,    '2019-03-10'),
    ('Tom Wilson',    'Senior Engineer',  'Engineering', 150000, 2,    '2020-01-20'),
    ('Anna Kim',      'Senior Engineer',  'Engineering', 145000, 2,    '2020-04-15'),
    ('James Liu',     'Engineer',         'Engineering', 120000, 4,    '2021-07-01'),
    ('Emma Davis',    'Engineer',         'Engineering', 115000, 4,    '2021-09-15'),
    ('Chris Lee',     'Sales Manager',    'Sales',       130000, 3,    '2020-02-01'),
    ('Nina Patel',    'Sales Rep',        'Sales',       85000,  8,    '2022-01-10'),
    ('Oscar Brown',   'Sales Rep',        'Sales',       82000,  8,    '2022-06-15');
 
-- Add a categories table for recursive hierarchy
CREATE TABLE categories (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id)
);
 
INSERT INTO categories (name, parent_id) VALUES
    ('All Products',  NULL),
    ('Electronics',   1),
    ('Computers',     2),
    ('Laptops',       3),
    ('Desktops',      3),
    ('Phones',        2),
    ('Furniture',     1),
    ('Desks',         7),
    ('Chairs',        7);

-- Additional orders for time-series analysis (add to existing orders)
INSERT INTO orders (customer_id, total, status, created_at) VALUES
    (1, 299.00,  'completed', '2025-01-15'),
    (2, 549.00,  'completed', '2025-01-22'),
    (1, 1299.00, 'completed', '2025-02-03'),
    (3, 199.00,  'completed', '2025-02-18'),
    (2, 899.00,  'completed', '2025-03-05'),
    (4, 459.00,  'pending',   '2025-03-12'),
    (1, 749.00,  'completed', '2025-03-20'),
    (3, 1599.00, 'completed', '2025-04-01'),
    (2, 329.00,  'completed', '2025-04-15'),
    (4, 899.00,  'completed', '2025-04-22'),
    (1, 199.00,  'completed', '2025-05-10'),
    (3, 649.00,  'completed', '2025-05-18');

WITH cte_name AS (
    SELECT ...
    FROM ...
)
SELECT *
FROM cte_name;

-- Subquery version (hard to read as complexity grows)
SELECT c.name, customer_totals.total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) AS customer_totals ON customer_totals.customer_id = c.id
WHERE customer_totals.total_spent > (
    SELECT AVG(total_spent)
    FROM (
        SELECT SUM(total) AS total_spent
        FROM orders
        WHERE status = 'completed'
        GROUP BY customer_id
    ) AS avg_calc
);

WITH customer_spending AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT c.name, cs.total_spent
FROM customers c
JOIN customer_spending cs ON cs.customer_id = c.id
WHERE cs.total_spent > (SELECT AVG(total_spent) FROM customer_spending);

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
revenue_with_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
        revenue - LAG(revenue) OVER (ORDER BY month) AS growth
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    prev_month_revenue,
    growth,
    CASE
        WHEN prev_month_revenue > 0
        THEN ROUND(growth / prev_month_revenue * 100, 1)
        ELSE NULL
    END AS growth_pct
FROM revenue_with_growth
ORDER BY month;

-- GROUP BY: collapses rows — one row per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Window function: keeps all rows, adds avg alongside each
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

function_name(...) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)

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

-- Top 2 highest-paid employees per department
WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 2;

-- Imagine two employees with the same salary
SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,    -- 1, 2, 3, 4
    RANK()       OVER (ORDER BY salary DESC) AS rank,       -- 1, 2, 2, 4 (gap)
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank  -- 1, 2, 2, 3 (no gap)
FROM employees;

SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue,
    LAG(SUM(total))  OVER (ORDER BY DATE_TRUNC('month', created_at)) AS prev_month,
    LEAD(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS next_month
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Look back 2 rows, default to 0 if no row exists
LAG(revenue, 2, 0) OVER (ORDER BY month)

-- Running total of revenue
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS monthly_revenue,
    SUM(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS running_total
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
) AS monthly;

-- Divide employees into 4 salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- Each employee compared to the highest-paid in their department
SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS highest_paid_name,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS highest_salary,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS diff_from_top
FROM employees;

LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_salary

ROWS BETWEEN <start> AND <end>

-- Running total (default frame with ORDER BY)
SUM(revenue) OVER (ORDER BY month)
 
-- 3-row moving average
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
 
-- Full partition (explicit — useful for ratios)
SUM(salary) OVER (PARTITION BY department) AS dept_total
 
-- Centered 5-row window
AVG(value) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

-- Each employee's salary as a percentage of their department's total
SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees
ORDER BY department, salary DESC;

WITH RECURSIVE cte_name AS (
    -- Anchor: base case
    SELECT ...
    WHERE <starting condition>
 
    UNION ALL
 
    -- Recursive: references cte_name
    SELECT ...
    FROM cte_name
    JOIN other_table ON ...
)
SELECT * FROM cte_name;

WITH RECURSIVE org_chart AS (
    -- Anchor: start with the CEO (no manager)
    SELECT
        id,
        name,
        title,
        manager_id,
        0 AS depth,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive: find direct reports of current level
    SELECT
        e.id,
        e.name,
        e.title,
        e.manager_id,
        oc.depth + 1,
        oc.path || ' → ' || e.name
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name, title, path
FROM org_chart
ORDER BY path;

WITH RECURSIVE category_tree AS (
    -- Anchor: root categories (no parent)
    SELECT
        id,
        name,
        parent_id,
        0 AS depth,
        name AS full_path
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    SELECT
        c.id,
        c.name,
        c.parent_id,
        ct.depth + 1,
        ct.full_path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    full_path
FROM category_tree
ORDER BY full_path;

WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE name = 'Electronics'  -- starting point
 
    UNION ALL
 
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

WITH RECURSIVE traversal AS (
    SELECT id, name, manager_id, ARRAY[id] AS visited
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    SELECT e.id, e.name, e.manager_id, t.visited || e.id
    FROM employees e
    JOIN traversal t ON e.manager_id = t.id
    WHERE e.id != ALL(t.visited)  -- prevent cycles
)
SELECT * FROM traversal;

WITH RECURSIVE traversal AS (
    ...
) CYCLE id SET is_cycle USING path

-- Categorize orders by size
SELECT
    id,
    total,
    CASE
        WHEN total >= 1000 THEN 'Large'
        WHEN total >= 500  THEN 'Medium'
        WHEN total >= 100  THEN 'Small'
        ELSE 'Micro'
    END AS order_size
FROM orders;

-- Order count by status — pivoted into columns
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed

-- COALESCE: first non-NULL value
SELECT COALESCE(nickname, name, 'Anonymous') AS display_name FROM users;
 
-- NULLIF: returns NULL if two values are equal (prevents division by zero)
SELECT revenue / NULLIF(order_count, 0) AS avg_order_value FROM metrics;
 
-- GREATEST / LEAST: max/min across columns (not rows)
SELECT GREATEST(score_a, score_b, score_c) AS best_score FROM exams;
SELECT LEAST(price, sale_price, clearance_price) AS lowest_price FROM products;

-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;  -- PostgreSQL
 
-- Case conversion
SELECT UPPER('hello'), LOWER('HELLO'), INITCAP('hello world');
-- HELLO, hello, Hello World
 
-- Substring and position
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);  -- Post
SELECT POSITION('SQL' IN 'PostgreSQL');        -- 8
SELECT LENGTH('Hello');                        -- 5
 
-- Trim
SELECT TRIM('  hello  ');              -- 'hello'
SELECT LTRIM('  hello');               -- 'hello'
SELECT RTRIM('hello  ');               -- 'hello'
SELECT TRIM(LEADING '0' FROM '00042'); -- '42'
 
-- Replace and split
SELECT REPLACE('2025-01-15', '-', '/');        -- 2025/01/15
SELECT SPLIT_PART('john@example.com', '@', 2); -- example.com
 
-- Pattern matching with REGEXP
SELECT 'abc123' ~ '^[a-z]+[0-9]+$';  -- true (PostgreSQL regex)
SELECT REGEXP_REPLACE('Phone: 123-456-7890', '[^0-9]', '', 'g');  -- 1234567890

-- Current timestamp
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;
 
-- Truncate to a time unit
SELECT DATE_TRUNC('month', TIMESTAMP '2025-03-15 14:30:00');  -- 2025-03-01 00:00:00
SELECT DATE_TRUNC('week',  TIMESTAMP '2025-03-15 14:30:00');  -- 2025-03-10 00:00:00
 
-- Extract parts
SELECT EXTRACT(YEAR  FROM TIMESTAMP '2025-03-15');  -- 2025
SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-03-15');  -- 3
SELECT EXTRACT(DOW   FROM TIMESTAMP '2025-03-15');  -- 6 (Saturday, 0=Sunday)
 
-- Age and intervals
SELECT AGE(TIMESTAMP '2025-01-01', TIMESTAMP '2020-06-15');  -- 4 years 6 mons 16 days
SELECT NOW() - INTERVAL '30 days';  -- 30 days ago
SELECT NOW() + INTERVAL '1 hour';   -- 1 hour from now
 
-- Generate a series of dates (useful for filling gaps)
SELECT generate_series(
    '2025-01-01'::date,
    '2025-12-01'::date,
    '1 month'::interval
) AS month;

-- Revenue by month, including months with $0
WITH months AS (
    SELECT generate_series(
        '2025-01-01'::date,
        '2025-12-01'::date,
        '1 month'::interval
    )::date AS month
)
SELECT
    m.month,
    COALESCE(SUM(o.total), 0) AS revenue
FROM months m
LEFT JOIN orders o
    ON DATE_TRUNC('month', o.created_at)::date = m.month
    AND o.status = 'completed'
GROUP BY m.month
ORDER BY m.month;

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    revenue,
    order_count,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        1
    ) AS growth_pct,
    SUM(revenue) OVER (ORDER BY month) AS ytd_revenue
FROM monthly
ORDER BY month;

WITH first_order AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
activity AS (
    SELECT
        f.cohort_month,
        DATE_TRUNC('month', o.created_at) AS activity_month,
        COUNT(DISTINCT o.customer_id) AS active_customers
    FROM orders o
    JOIN first_order f ON f.customer_id = o.customer_id
    GROUP BY f.cohort_month, DATE_TRUNC('month', o.created_at)
)
SELECT
    cohort_month,
    activity_month,
    EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS months_since_first,
    active_customers
FROM activity
ORDER BY cohort_month, activity_month;

SELECT
    name,
    department,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS dept_avg,
    ROUND(salary - AVG(salary) OVER (PARTITION BY department), 0) AS diff_from_avg,
    NTILE(4) OVER (ORDER BY salary) AS company_quartile,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY department, salary DESC;

WITH customer_products AS (
    SELECT
        c.name AS customer,
        p.name AS product,
        SUM(oi.quantity) AS total_qty,
        ROW_NUMBER() OVER (
            PARTITION BY c.id
            ORDER BY SUM(oi.quantity) DESC
        ) AS rn
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    GROUP BY c.id, c.name, p.id, p.name
)
SELECT customer, product, total_qty
FROM customer_products
WHERE rn = 1;

WITH daily_sales AS (
    SELECT
        DATE_TRUNC('day', o.created_at) AS day,
        c.name AS customer,
        SUM(o.total) AS daily_total
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('day', o.created_at), c.id, c.name
)
SELECT
    day,
    customer,
    daily_total,
    SUM(daily_total) OVER (PARTITION BY customer ORDER BY day) AS cumulative_total,
    RANK() OVER (
        PARTITION BY day
        ORDER BY daily_total DESC
    ) AS daily_rank
FROM daily_sales
ORDER BY day, daily_rank;

-- WRONG: cannot use window function in WHERE
SELECT *, ROW_NUMBER() OVER (...) AS rn
FROM employees
WHERE rn <= 5;  -- ERROR
 
-- CORRECT: wrap in CTE
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (...) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 5;

SELECT
    name,
    salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK()       OVER w AS rank,
    DENSE_RANK() OVER w AS dense_rank
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

WITH expensive_calc AS MATERIALIZED (
    SELECT ... -- complex calculation
)
SELECT * FROM expensive_calc;