SQL Window Functions: The Complete Guide

What is a window function?

A window function computes a value across a set of rows that are related to the current row — the 'window' — without collapsing those rows into one output row. Unlike GROUP BY, which produces one row per group, a window function keeps every input row and adds a computed column.

The syntax is function_name() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY splits rows into groups, ORDER BY orders rows within each group, and the optional frame clause (ROWS BETWEEN ... AND ...) narrows the window further.

The four families of window functions

Ranking: ROW_NUMBER gives every row a unique number, RANK shares a number across ties but leaves gaps, DENSE_RANK shares ties with no gaps. Use ROW_NUMBER for 'pick one row per group', RANK or DENSE_RANK for 'top N with ties'.

Offset: LAG(col, n) reads n rows back, LEAD(col, n) reads n rows ahead. These power previous/next row comparisons such as rising temperature or gaps between orders.

Aggregate windows: SUM, AVG, MIN, MAX over a window produce running totals and rolling averages when combined with an ORDER BY and frame.

Distribution and navigation: FIRST_VALUE, LAST_VALUE, NTILE, and PERCENT_RANK are less common but appear in a few questions.

Frames: ROWS BETWEEN and why they matter

The default frame for an aggregate window with ORDER BY is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running total. For a rolling average you need an explicit bounded frame, e.g. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row average.

Without ORDER BY, the frame is the whole partition, which is what makes SUM(amount) OVER () a percentage-of-total computation.

The number-one mistake: filtering on a window function

Window functions run after WHERE, so you cannot filter on them directly. To keep only rows where rnk = 1 you must wrap the query in a subquery or CTE and filter outside. This pattern — rank first, filter second — is the single most common structure in window-function interview questions.

Practice what you learned

• easyRising Temperature
• mediumConsecutive Login Days
• mediumConsecutive Numbers
• mediumFirst and Last Order Per Customer
• mediumGame Play Analysis IV
• mediumImmediate Food Delivery
• mediumLatest Event Per User
• mediumLongest Login Streak
• mediumMonthly Sales Ranking
• mediumNth Highest Salary
• mediumOrders Gap Analysis
• mediumPercentage of Total Sales
• mediumRank Scores
• mediumRunning Total
• mediumSeven-Day Rolling Average
• hardTop Three Per Category

Topics

• Window Functions
• Ranking
• Running Totals

Frequently asked questions

What is the difference between RANK and DENSE_RANK?

Both assign the same number to tied values. RANK then skips numbers after a tie (1, 1, 3), while DENSE_RANK continues without gaps (1, 1, 2).

Can I use a window function in WHERE?

No — window functions are evaluated after WHERE. Wrap the query in a subquery or CTE and filter on the window result outside.