SQL window functions explained with examples
A window function computes a value across a set of related rows but, unlike GROUP BY, keeps every row. You get the detail and the summary side by side.
OVER and PARTITION BY
OVER() turns an aggregate or ranking function into a window function. PARTITION BY splits the rows into groups that are calculated separately, like GROUP BY without collapsing them.
SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees;
ORDER BY inside OVER
Adding ORDER BY inside OVER makes the function cumulative or positional: SUM becomes a running total, and RANK, ROW_NUMBER, LAG and LEAD all need an order.
You can't filter on them in WHERE
Window functions are computed after WHERE, so filtering on one (say, rank <= 3) needs a subquery or CTE: compute the window in the inner query, filter in the outer one.
SELECT * FROM (
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 2;Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Forgetting PARTITION BY, so everything is ranked company-wide.
- Filtering on a window function in WHERE.
- Using GROUP BY when the question needs every row kept.
- LAST_VALUE with the default frame, which stops at the current row.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- Top 3 earners per departmentWindow functions · DENSE_RANK()Free
- Running revenue totalSUM() OVER · running totalsPro
- Consecutive-day loginsLAG() · date arithmeticPro
- Paid above department averageAVG() OVER · compare to groupPro
- Share of total spendSUM() OVER () · percent of totalPro
- Month-over-month growthCTE + LAG · growth ratePro
- Median salary by departmentPERCENTILE_CONT · ordered-set aggregatesPro
- Days until next orderLEAD() · PARTITION BYPro
- Third-highest salary per departmentNth highest · DENSE_RANK() vs ROW_NUMBER()Pro
- Largest charge per accountROW_NUMBER() · deterministic tie-breakerPro
- Salary quartilesNTILE() · bucketsPro
- 3-transaction moving averageWindow frames · ROWS vs RANGEPro
- First vs latest order valueFIRST_VALUE · LAST_VALUE · frame trapPro
- Customer share of country revenueSUM() OVER (PARTITION BY) · percent of groupPro
- Year-over-year growth by quarterSelf-join on period · YoY %Pro
- 7-day moving average revenueRANGE window frame · calendar moving averagePro
- New vs returning usersFirst-seen month · running distinct countPro
- First-touch vs last-touch attributionROW_NUMBER both ways · attributionPro
- Sessions from raw pageviewsLAG · running SUM · sessionizationPro
- Pricing to checkout within an hourFunnel with a time window · range joinPro