Mastering SQL Window Functions: A Comprehensive Guide

-- GROUP BY: collapses to 1 row per department
SELECT   department,
         AVG(salary) AS avg_salary
FROM     employees
GROUP BY department;
-- Window function: keeps ALL rows, adds avg per department alongside
SELECT   employee_id,
         name,
         department,
         salary,
         AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM     employees;

function_name(expression)
OVER (
    PARTITION BY col1, col2    -- divide rows into independent groups
    ORDER BY     col3 ASC      -- sort rows within each partition
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- frame
)

SELECT
    employee_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
-- Result: within each department, employees numbered 1,2,3... by salary desc
-- Useful for: deduplication, pagination, top-N per group

SELECT
    name,
    department,
    salary,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;
-- salary: 95000, 90000, 90000, 80000
-- RANK:         1,      2,      2,     4   (gap at 3)
-- DENSE_RANK:   1,      2,      2,     3   (no gap)

-- Divide employees into salary quartiles within each department
SELECT
    name,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile = 1 -> top 25%, quartile = 4 -> bottom 25%

WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
    FROM employees
)
SELECT name, department, salary
FROM   ranked
WHERE  dr <= 3
ORDER BY department, salary DESC;

SELECT
    order_date,
    order_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY customer_id, order_date;
-- Each row shows the cumulative spend for that customer up to that order

-- 7-day rolling average of daily sales
SELECT
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM daily_sales
ORDER BY sale_date;

-- Each product's revenue as % of its category total
SELECT
    product_name,
    category,
    revenue,
    ROUND(
        revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category),
        2
    ) AS pct_of_category
FROM product_sales
ORDER BY category, revenue DESC;

-- Employee salary vs. department average and overall company average
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department)  AS dept_avg,
    AVG(salary) OVER ()                          AS company_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_dept_avg
FROM employees
ORDER BY department, salary DESC;

SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month)          AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        * 100.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
        2
    ) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

SELECT
    user_id,
    session_id,
    page_visited,
    LAG(page_visited) OVER (PARTITION BY user_id ORDER BY visited_at) AS prev_page
FROM page_views
HAVING page_visited = prev_page   -- consecutive same-page visits
ORDER BY user_id, visited_at;

SELECT
    customer_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
    DATEDIFF(
        LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date),
        order_date
    ) AS days_until_next_order
FROM orders
ORDER BY customer_id, order_date;

SELECT
    name,
    department,
    salary,
    -- Highest salary in the department (first after ORDER BY salary DESC)
    FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_max_salary,
    -- Lowest salary in the department
    LAST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_min_salary
FROM employees;
-- NOTE: LAST_VALUE requires explicit UNBOUNDED FOLLOWING frame;
--       otherwise the default frame ends at CURRENT ROW.

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

SELECT
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
    CUME_DIST()    OVER (ORDER BY salary) AS cumulative_dist
FROM employees
ORDER BY salary;
-- pct_rank = 0.0 for the lowest salary, 1.0 for the highest
-- cume_dist = fraction of employees earning <= this salary

-- ROWS: based on physical row position
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Always sums exactly 3 rows: current + 2 before
-- RANGE: based on logical value proximity
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW)
-- Sums all rows within a 2-day window before the current date
-- If multiple rows share the same date, all are included

-- Without WINDOW clause — repetitive and fragile
SELECT
    name, department, salary,
    SUM(salary)  OVER (PARTITION BY department ORDER BY hire_date) AS running_sal,
    AVG(salary)  OVER (PARTITION BY department ORDER BY hire_date) AS running_avg,
    COUNT(*)     OVER (PARTITION BY department ORDER BY hire_date) AS running_cnt
FROM employees;
-- With WINDOW clause — define once, reuse freely
SELECT
    name, department, salary,
    SUM(salary)  OVER dept_window AS running_sal,
    AVG(salary)  OVER dept_window AS running_avg,
    COUNT(*)     OVER dept_window AS running_cnt
FROM employees
WINDOW dept_window AS (
    PARTITION BY department ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT
    year,
    region,
    total_sales,
    LAG(total_sales) OVER (PARTITION BY region ORDER BY year) AS prev_year_sales,
    ROUND(
        (total_sales - LAG(total_sales) OVER (PARTITION BY region ORDER BY year))
        * 100.0
        / NULLIF(LAG(total_sales) OVER (PARTITION BY region ORDER BY year), 0),
        1
    ) AS yoy_growth_pct
FROM annual_sales
ORDER BY region, year;

SELECT
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS lifetime_value,
    COUNT(*) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS order_number
FROM orders
ORDER BY customer_id, order_date;

-- Detect missing invoice numbers
SELECT
    invoice_id,
    LAG(invoice_id) OVER (ORDER BY invoice_id) AS prev_invoice,
    invoice_id - LAG(invoice_id) OVER (ORDER BY invoice_id) - 1 AS gap_count
FROM invoices
HAVING gap_count > 0
ORDER BY invoice_id;

-- Define a new session when gap between events > 30 minutes
WITH flagged AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                     PARTITION BY user_id ORDER BY event_time
                 ) > INTERVAL '30' MINUTE
            THEN 1 ELSE 0
        END AS new_session_flag
    FROM clickstream
)
SELECT
    user_id,
    event_time,
    SUM(new_session_flag) OVER (
        PARTITION BY user_id ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_number
FROM flagged
ORDER BY user_id, event_time;