Window Functions

Calculations Across Neighbouring Rows

Window functions compute a value over a window of rows while keeping every row visible - unlike GROUP BY, which collapses them.

Ranking Rows

SELECT name, total,
       RANK() OVER (ORDER BY total DESC) AS sales_rank,
       DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rank
FROM orders;

Running Totals

SELECT payment_date, amount,
       SUM(amount) OVER (ORDER BY payment_date) AS running_total
FROM payments;

Previous and Next Values

SELECT payment_date,
       amount,
       LAG(amount) OVER (ORDER BY payment_date) AS prev,
       LEAD(amount) OVER (ORDER BY payment_date) AS next
FROM payments;

PARTITION BY Restarts the Window

SELECT customer_id, total,
       RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders;

PARTITION BY splits rows into groups, so RANK restarts at 1 for each customer.

Key Points

• Window functions keep rows and compute over a window.
• ORDER BY inside OVER defines the sequence of the window.
• PARTITION BY restarts calculations per group.
• RANK, SUM(), LAG and LEAD cover most analytics needs.