What are window functions in SQL and when would you use them?

A window function performs a calculation across a set of rows related to the current row, without collapsing them the way GROUP BY does. You keep every row and gain an aggregate alongside it.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

• PARTITION BY divides rows into groups — like GROUP BY, but without collapsing them.
• ORDER BY inside OVER determines the ordering for running calculations and ranking.

The main families:

• Ranking — ROW_NUMBER (always unique), RANK (ties share a rank, leaving gaps), DENSE_RANK (ties share, no gaps).
• Offset — LAG and LEAD to reach previous or next rows, which is how you calculate period-over-period change without a self-join.
• Aggregate — SUM, AVG, COUNT over a window, for running totals and moving averages.

The classic use is "get the most recent row per group": number rows with ROW_NUMBER() partitioned by the group and ordered by date descending, then filter to rn = 1 in an outer query — because window functions cannot be used in WHERE.