Window functions

Key points

• A window function computes across a set of rows related to the current row, but unlike GROUP BY it does not collapse them. Every input row still appears in the output.
• PARTITION BY divides the rows into groups; ORDER BY inside OVER decides the ordering within each. Together they define the window.
• The ranking family differs in how ties are handled: ROW_NUMBER always gives distinct numbers, RANK leaves gaps after a tie, and DENSE_RANK does not.
• LAG and LEAD read the previous or next row directly, which turns "compare each month to the one before" from a self-join into one line.
• A frame clause narrows the window further - ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a seven-day moving average.
• Window functions run after WHERE and GROUP BY, so you cannot filter on one in the same WHERE clause. Wrap it in a subquery or CTE and filter outside.

Example

-- Top 3 orders per customer. Before window functions this needed a
-- correlated subquery or a self-join.
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders o
) ranked
WHERE rn <= 3;          -- filtered OUTSIDE, because rn does not exist in WHERE

-- The three ranking functions, on the values 100, 90, 90, 80:
SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,   -- 1, 2, 3, 4
       RANK()       OVER (ORDER BY score DESC) AS rnk,       -- 1, 2, 2, 4
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense      -- 1, 2, 2, 3
FROM players;

-- Month-on-month change, without joining the table to itself.
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;

-- Running total and a 7-day moving average.
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales;

-- Each row against its own group aggregate - no GROUP BY, no join.
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department) AS vs_dept
FROM employees;

Window functions keep every row while computing across related ones - and they run after WHERE, so filter on them in an outer query.