Window Functions

Beyond Group By

Window functions compute values across a set of rows related to the current row, without collapsing them into a single row like GROUP BY does.

Ranking

SELECT name, total,
       RANK() OVER (ORDER BY total DESC) AS rank
FROM orders;

LAG and LEAD

SELECT created_at,
       amount,
       LAG(amount) OVER (ORDER BY created_at) AS prev_amount
FROM payments;

Running Totals With SUM OVER

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

Partitioning

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

PARTITION BY splits the window into groups, so rankings restart for each customer.

Key Points

• Window functions keep every row while computing over a window.
• RANK, LAG, LEAD and SUM OVER are everyday tools.
• PARTITION BY resets the calculation per group.
• Analytics, reports and rankings all lean on these.