Window Functions

What is it?

A window function calculates a value across a related set of rows while keeping each input row in the result. Unlike GROUP BY, it does not collapse those rows into one row per group.

Why is it useful?

Use window functions to rank records, compare a row with its neighbors, or calculate running and partitioned metrics without losing row-level detail.

A starting shape

function_name(expression) OVER (PARTITION BY group_column ORDER BY sort_column)

Rank each customer's orders

SELECT order_id, customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS recent_order_rank FROM orders;

Here, PARTITION BY restarts the ranking for each customer. The secondary order_id sort makes ties more predictable.

Choose the current record

A reporting model can use ROW_NUMBER to select the most recent valid status for each account, with a stable secondary key to make tie handling deterministic.

Common mistakes

• Forgetting that ORDER BY inside OVER controls the window, not final output order
• Ignoring ties or leaving ordering nondeterministic
• Using GROUP BY when each source row must remain visible

Questions to practice explaining