sql-window-functions

function_name(arguments) OVER (
    [PARTITION BY partition_columns]
    [ORDER BY sort_columns]
    [frame_clause]
)

GROUP BY:  Many rows -> One row per group (aggregates collapse)
OVER:      Many rows -> Same rows, adds calculated column (no collapse)

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

name    | department | salary  | rank_overall | rank_in_dept
--------|------------|---------|--------------|-------------
Alice   | Sales      | 90000   | 1            | 1
Bob     | Sales      | 85000   | 2            | 2
Carol   | IT         | 80000   | 3            | 1

SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM players;

name    | score | rank
--------|-------|-----
Alice   | 100   | 1
Bob     | 100   | 1
Carol   | 95    | 3    -- Skips 2

SELECT 
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM players;

name    | score | rank
--------|-------|-----
Alice   | 100   | 1
Bob     | 100   | 1
Carol   | 95    | 2    -- No skip

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

SELECT 
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

SELECT 
    date,
    sales,
    LAG(sales, 1) OVER (ORDER BY date) AS previous_day,
    LEAD(sales, 1) OVER (ORDER BY date) AS next_day,
    sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_sales;

LAG(sales, 1, 0) OVER (ORDER BY date)  -- Returns 0 if no previous row

SELECT 
    date,
    price,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_id 
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_price,
    LAST_VALUE(price) OVER (
        PARTITION BY product_id 
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_price
FROM prices;

SELECT 
    name,
    salary,
    NTH_VALUE(name, 2) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_paid
FROM employees;

SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

SELECT 
    date,
    amount,
    AVG(amount) OVER (ORDER BY date) AS running_avg
FROM transactions;

SELECT 
    date,
    COUNT(*) OVER (ORDER BY date) AS cumulative_count
FROM events;

SELECT 
    department,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept
FROM employees;

ROWS BETWEEN frame_start AND frame_end
RANGE BETWEEN frame_start AND frame_end
GROUPS BETWEEN frame_start AND frame_end

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SELECT 
    date,
    amount,
    AVG(amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM daily_sales;

SELECT 
    date,
    amount,
    AVG(amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS centered_avg_7
FROM daily_sales;

WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

SELECT 
    product,
    sales,
    ROUND(100.0 * sales / SUM(sales) OVER (), 2) AS pct_of_total,
    ROUND(100.0 * sales / SUM(sales) OVER (PARTITION BY category), 2) AS pct_of_category
FROM products;

SELECT 
    id,
    LEAD(id) OVER (ORDER BY id) AS next_id,
    LEAD(id) OVER (ORDER BY id) - id - 1 AS gap_size
FROM records
HAVING gap_size > 0;

WITH numbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY date) AS rn,
        ROW_NUMBER() OVER (PARTITION BY status ORDER BY date) AS status_rn
    FROM events
)
SELECT 
    *,
    rn - status_rn AS island_id
FROM numbered;

SELECT 
    year,
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY year, month) AS revenue_last_year,
    revenue - LAG(revenue, 12) OVER (ORDER BY year, month) AS yoy_change,
    ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY year, month)) / 
          NULLIF(LAG(revenue, 12) OVER (ORDER BY year, month), 0), 2) AS yoy_pct
FROM monthly_revenue;

SELECT 
    transaction_date,
    description,
    CASE WHEN type = 'credit' THEN amount ELSE -amount END AS signed_amount,
    SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) 
        OVER (ORDER BY transaction_date, id) AS balance
FROM transactions
WHERE account_id = 123;

WITH event_gaps AS (
    SELECT 
        *,
        EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp))) / 60 AS minutes_since_last
    FROM events
),
session_starts AS (
    SELECT 
        *,
        CASE WHEN minutes_since_last > 30 OR minutes_since_last IS NULL THEN 1 ELSE 0 END AS is_new_session
    FROM event_gaps
)
SELECT 
    *,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_id
FROM session_starts;

SELECT 
    date,
    amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM transactions
WINDOW w AS (ORDER BY date ROWS UNBOUNDED PRECEDING);

SELECT 
    department,
    employee,
    salary,
    SUM(salary) OVER dept AS dept_total,
    AVG(salary) OVER dept AS dept_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
WINDOW dept AS (PARTITION BY department);

python scripts/sql_sandbox.py --demo --query "SELECT region, month, revenue,
    SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total FROM sales"
python scripts/sql_sandbox.py --csv orders.csv --query-file query.sql