Window Functions

-- The shape: function() OVER (PARTITION BY ... ORDER BY ...)
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department)          AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    COUNT(*)   OVER (PARTITION BY department)           AS dept_size,
    SUM(salary) OVER ()                                 AS company_payroll
FROM employees;

-- PARTITION BY splits the rows into groups; without it the window is everything.
-- Unlike GROUP BY, every original row is still in the output.

-- Ranking
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    NTILE(4)     OVER (ORDER BY salary DESC)                         AS quartile
FROM employees;

-- ROW_NUMBER: 1,2,3,4    always unique
-- RANK:       1,2,2,4    ties share, then it skips
-- DENSE_RANK: 1,2,2,3    ties share, no gap

-- "Top N per group" — the classic use of ROW_NUMBER
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 <= 3;             -- top 3 earners in each department

-- Comparing a row to its neighbours
SELECT
    day,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY day) AS previous_day,
    LEAD(revenue, 1) OVER (ORDER BY day) AS next_day,
    revenue - LAG(revenue) OVER (ORDER BY day) AS change,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
          / NULLIF(LAG(revenue) OVER (ORDER BY day), 0), 1) AS pct_change,
    FIRST_VALUE(revenue) OVER (ORDER BY day) AS first_day_revenue
FROM daily_sales
ORDER BY day;

-- Frames control WHICH rows in the partition are included.
SELECT
    day,
    revenue,

    -- Running total: everything from the start up to this row
    SUM(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    -- 7-day moving average
    AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7day,

    -- Centred 3-day window
    AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS avg_centered,

    -- Share of the grand total
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM daily_sales;

-- Reusable window definitions with a WINDOW clause
SELECT
    day,
    SUM(revenue) OVER w AS running_total,
    AVG(revenue) OVER w AS running_avg
FROM daily_sales
WINDOW w AS (ORDER BY day ROWS UNBOUNDED PRECEDING);