On this page
The core idea
A window function calculates a value across a set of rows related to the current row, while keeping every row in the result. The OVER clause defines that set: PARTITION BY splits rows into groups, and ORDER BY sets the order inside each group. Functions such as ROW_NUMBER, RANK, SUM, AVG and LAG then work within each partition. Unlike GROUP BY, which collapses rows into one per group, a window function adds a column to each row, so you can compare a row with its group or with neighbouring rows.
A practical approach
Step 1
Describe the calculation in words: 'for each learner, rank their quiz attempts by score' or 'for each month, show revenue and the change from the previous month'.
Step 2
Translate 'for each ...' into PARTITION BY and 'by ...' into ORDER BY inside OVER().
Step 3
Choose the function: ROW_NUMBER for a unique sequence, RANK or DENSE_RANK when ties should share a position, SUM for running totals and LAG or LEAD to read the previous or next row.
Step 4
Filter on the window result in an outer query or common table expression, because window functions cannot appear in WHERE.
Step 5
Check edge cases: ties, the first row in each partition where LAG returns NULL and partitions with a single row.
Worked example
A learning dashboard needs the three most recent webinar registrations per course. The first version ran one query per course from application code. Rewriting it as a single query with ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY registered_at DESC) AS n, then selecting rows where n <= 3 in an outer query, returns the same result in one round trip and stays correct as courses are added.
A mistake to avoid
Be careful with the default frame for aggregate window functions. With ORDER BY and no frame clause, SUM uses rows from the start of the partition up to the current row and its peers, which gives a running total; without ORDER BY it sums the whole partition. State the frame explicitly when the difference matters.
Try it yourself
Write a query that shows each month's sign-ups, the previous month's sign-ups using LAG and the percentage change. Then add a column ranking months within each year by sign-ups.
Your implementation checklist
- I can explain the difference between GROUP BY and a window function
- PARTITION BY and ORDER BY match the question's wording
- The ranking function matches how ties should behave
- Filters on window results are applied in an outer query
- Running totals use an explicit frame when it matters
Share or cite this guide
Innovate with Ajay Editorial (2026-10-07). Use SQL Window Functions for Rankings, Running Totals and Comparisons. https://innovatewithajay.com/blogs/use-sql-window-functions