When do you use SQL window functions?

Direct answer

What a strong answer covers

How it works

A window function computes a value for each row using a set of related rows, without the GROUP BY collapse. PARTITION BY defines which rows belong together, ORDER BY defines their sequence, and an optional frame clause such as ROWS BETWEEN limits the range. ROW_NUMBER, RANK, and DENSE_RANK produce orderings; LAG and LEAD reach neighbouring rows; SUM or AVG over a window produce running or moving totals. They replace self-joins and correlated subqueries, and they are evaluated after WHERE, so filtering on a window result needs a subquery or CTE.

Example

Finding each customer’s most recent order is ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) followed by a filter for row number 1 in an outer query. A retention view uses LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) to compute days since the previous order for every row.

Common mistakes

• Confusing RANK, which leaves gaps after ties, with DENSE_RANK.
• Filtering on a window column in the same query’s WHERE clause.
• Forgetting the frame default so a running total becomes a group total when ORDER BY is missing.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.