Advanced SQL | Databases - Wyatt's Notes

function_name([arguments]) OVER (
    [window_name]
    [PARTITION BY partition_expr, ...]
    [ORDER BY sort_expr [ASC|DESC] [NULLS {FIRST|LAST}], ...]
    [frame_clause]
)

-- Frame boundaries
ROWS BETWEEN start AND end
RANGE BETWEEN start AND end
GROUPS BETWEEN start AND end

-- Start/end boundary options:
-- UNBOUNDED PRECEDING  -- first row of partition
-- UNBOUNDED FOLLOWING  -- last row of partition
-- n PRECEDING          -- n rows before current row
-- n FOLLOWING          -- n rows after current row
-- CURRENT ROW          -- current row

-- Running total (ROWS: 3-row moving sum)
SELECT order_date, amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3day
FROM daily_sales;

-- Cumulative total (ROWS: all rows from start)
SELECT order_date, amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative
FROM daily_sales;

-- Given data with duplicate dates:
-- 2024-01-01 | 100
-- 2024-01-01 | 200
-- 2024-01-02 | 150
-- 2024-01-03 | 300

-- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
-- For row 2 (2024-01-01, 200): sees rows 1 and 2 → SUM = 300
-- For row 3 (2024-01-02, 150): sees rows 2 and 3 → SUM = 350

-- RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
-- "1 PRECEDING" in RANGE means "ORDER BY value - 1"
-- For row 2 (date=Jan 1): sees all rows where date >= Jan 0 → sees rows 1, 2 → SUM = 300
-- For row 3 (date=Jan 2): sees all rows where date >= Jan 1 → sees rows 1, 2, 3 → SUM = 650

-- GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
-- For row 2 (date=Jan 1): sees current group (Jan 1) and 1 group before (none) → SUM = 300
-- For row 3 (date=Jan 2): sees current group (Jan 2) and 1 group before (Jan 1) → SUM = 650

-- Exclude the current row from the frame
SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE CURRENT ROW
) AS sum_excluding_current

-- Exclude other rows with the same ORDER BY value (peers)
SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE GROUP
) AS sum_excluding_peers

-- Exclude both current row and its ties
SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE TIES
) AS sum_excluding_ties_and_current

SELECT
    department_id,
    emp_id,
    salary,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank,
    DENSE_RANK() OVER w AS dense_rank,
    SUM(salary) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

SELECT
    department_id,
    emp_id,
    salary,
    AVG(salary) OVER dept_avg AS dept_avg_salary,
    salary - AVG(salary) OVER dept_avg AS delta_from_avg,
    ROW_NUMBER() OVER dept_order AS salary_rank
FROM employees
WINDOW
    dept AS (PARTITION BY department_id),
    dept_avg AS (dept),
    dept_order AS (dept ORDER BY salary DESC);

-- NTILE divides rows into n roughly equal buckets
SELECT emp_id, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

-- PERCENT_RANK: (rank - 1) / (total_rows - 1), range [0, 1]
SELECT emp_id, salary,
    PERCENT_RANK() OVER (ORDER BY salary DESC) AS pct_rank
FROM employees;

-- CUME_DIST: proportion of rows with value <= current row, range (0, 1]
SELECT emp_id, salary,
    CUME_DIST() OVER (ORDER BY salary DESC) AS cumulative_dist
FROM employees;

WITH new_orders AS (
    INSERT INTO orders (customer_id, total, status)
    VALUES (42, 250.00, "pending')
    RETURNING order_id, customer_id, total
),
inventory_update AS (
    UPDATE inventory i
    SET quantity = i.quantity - 1
    FROM new_orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE i.product_id = oi.product_id
    RETURNING i.product_id, i.quantity
),
audit_entry AS (
    INSERT INTO audit_log (action, details)
    SELECT 'order_created', json_build_object(
        'order_id', o.order_id,
        'customer_id', o.customer_id,
        'total', o.total
    )
    FROM new_orders o
    RETURNING log_id
)
SELECT o.order_id, o.total, a.log_id AS audit_id
FROM new_orders o
JOIN audit_entry a ON TRUE;

-- Inlined (faster for single-reference CTEs):
WITH active_users AS (
    SELECT user_id, email FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE email LIKE '%@company.com';

-- Force materialization (useful when referenced multiple times):
WITH active_users AS MATERIALIZED (
    SELECT user_id, email FROM users WHERE is_active = TRUE
)
SELECT
    (SELECT COUNT(*) FROM active_users) AS total_active,
    (SELECT COUNT(*) FROM active_users WHERE email LIKE '%@company.com') AS company_users;

-- Org chart with full path
WITH RECURSIVE org_tree AS (
    SELECT
        emp_id,
        first_name,
        manager_id,
        1 AS depth,
        ARRAY[first_name] AS path_names
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.first_name,
        e.manager_id,
        t.depth + 1,
        t.path_names || e.first_name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT
    emp_id,
    first_name,
    depth,
    array_to_string(path_names, ' -> ') AS reporting_chain
FROM org_tree
ORDER BY path_names;

-- Find all reachable nodes from a starting node
WITH RECURSIVE bfs AS (
    SELECT
        from_node,
        to_node,
        0 AS hops,
        ARRAY[from_node] AS visited
    FROM edges
    WHERE from_node = 'A'

    UNION ALL

    SELECT
        e.from_node,
        e.to_node,
        b.hops + 1,
        b.visited || e.to_node
    FROM edges e
    JOIN bfs b ON e.from_node = b.to_node
    WHERE NOT (e.to_node = ANY(b.visited))
      AND b.hops < 10
)
SELECT DISTINCT to_node, MIN(hops) AS shortest_path
FROM bfs
GROUP BY to_node
ORDER BY shortest_path;