SQL for Data Science (UC Davis)
A UC Davis Coursera course teaching SQL specifically for data analysis workflows, covering querying, joining and aggregating real datasets.
A window function performs a calculation across a set of rows related to the current row, called its window, without collapsing those rows into one the way
GROUP BYdoes. You write it with anOVER (...)clause:PARTITION BYsplits rows into groups, andORDER BYsets the order within each group. Window functions handle rankings, running totals, period-over-period changes and moving averages.
Window functions are what separate "knows SQL" from "fluent in SQL". They appear in most intermediate analyst interviews, and they replace a lot of awkward self-joins and subqueries. This guide uses small tables so you can check every result by hand.
Monthly revenue by region:
| region | month | revenue |
|---|---|---|
| North | 2026-01 | 100 |
| North | 2026-02 | 120 |
| North | 2026-03 | 90 |
| South | 2026-01 | 80 |
| South | 2026-02 | 80 |
| South | 2026-03 | 110 |
GROUP BY region gives two rows: North 310, South 270. The monthly detail is gone.
A window function keeps all six rows and adds the region total beside each one:
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales;
Each North row shows 310 and each South row shows 270. Now you can calculate each month's share of its region's total in the same query.
function(...) OVER (
PARTITION BY ... -- which rows belong together (optional)
ORDER BY ... -- order within each partition (optional)
ROWS BETWEEN ... -- which rows around the current one count (optional)
)
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales;
| region | month | revenue | running_total |
|---|---|---|---|
| North | 2026-01 | 100 | 100 |
| North | 2026-02 | 120 | 220 |
| North | 2026-03 | 90 | 310 |
| South | 2026-01 | 80 | 80 |
| South | 2026-02 | 80 | 160 |
| South | 2026-03 | 110 | 270 |
Adding ORDER BY inside OVER turns a group total into a cumulative one. PARTITION BY restarts it for each region.
LAG looks at a previous row, and LEAD looks at a following one:
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS change
FROM monthly_sales;
For North: February's change is +20 (120 vs 100) and March's is −30 (90 vs 120), a 25% drop. For South: February 0, March +30 (+37.5%). The first month in each region has no previous row, so LAG returns NULL.
This is the standard way to answer "month-over-month growth" questions. It's also a very common interview task.
The three ranking functions differ only in how they treat ties. Product sales:
| product | sales | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| A | 500 | 1 | 1 | 1 |
| B | 400 | 2 | 2 | 2 |
| C | 400 | 3 | 2 | 2 |
| D | 300 | 4 | 4 | 3 |
SELECT product, sales,
ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num,
RANK() OVER (ORDER BY sales DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rnk
FROM product_sales;
"The top 3 products in each category" is one of the most common real requests. Window functions can't go directly in WHERE, so rank first in a CTE, then filter:
WITH ranked AS (
SELECT category, product, sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM product_sales
)
SELECT category, product, sales
FROM ranked
WHERE rn <= 3;
Some databases (Snowflake, BigQuery, DuckDB, Databricks) also support QUALIFY, which filters on window results directly: QUALIFY ROW_NUMBER() OVER (...) <= 3.
SELECT region, month, revenue,
AVG(revenue) OVER (
PARTITION BY region ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m
FROM monthly_sales;
For North in March: (100 + 120 + 90) ÷ 3 ≈ 103.3. For the first two months the window holds fewer than three rows, so check whether your report should show those partial averages.
NTILE(4): splits rows into quartiles, for example to segment customers by spend.FIRST_VALUE / LAST_VALUE: the first or last value in the window. Mind the frame, as explained below.PERCENT_RANK / CUME_DIST: relative position as a fraction.COUNT(*) OVER (PARTITION BY ...): group sizes shown on every row.WHERE, so wrap it in a CTE or subquery, or use QUALIFY where it's supported.ORDER BY and no explicit frame, many databases use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Rows with tied order values are then treated together, and LAST_VALUE returns the current row instead of the last one. Write ROWS BETWEEN ... explicitly when it matters.ORDER BY column.LAG compares with the previous row, not the previous month. If a month is missing, you'll compare March with January without noticing.If you use Power BI, you'll notice similar ideas: running totals and "previous period" measures in DAX play the same role that window functions play in SQL. See What Is DAX?.
Window functions are Stage 4 of our SQL roadmap, after joins and CTEs. Practice them with the window-function questions in our 50 SQL interview questions. For structured learning, SQL for Data Science (UC Davis) builds toward analytical SQL, and Introduction to SQL (DataCamp) is a good place to start if you're still on the basics. Browse all Data Analysis courses.
What's the difference between GROUP BY and PARTITION BY?
GROUP BY collapses each group into one row. PARTITION BY (inside OVER) defines groups for a calculation but keeps every row.
Are window functions supported in MySQL? Yes, from MySQL 8.0. PostgreSQL, SQL Server, Oracle, SQLite (3.25+), BigQuery, Snowflake and other major databases also support them.
Are window functions slow? They're usually efficient, and often faster than the self-joins they replace. On very large tables, partitioning and sorting cost time, so filter data early.
What's the difference between RANK and DENSE_RANK? Both give tied rows the same rank. RANK skips the following numbers (1, 2, 2, 4), while DENSE_RANK doesn't (1, 2, 2, 3).
Window functions calculate across related rows without collapsing them: running totals with SUM() OVER (ORDER BY), period changes with LAG, rankings with ROW_NUMBER, RANK and DENSE_RANK, top-N with a CTE, and moving averages with an explicit ROWS frame. Learn these five patterns and you'll handle most analytical SQL you meet at work or in interviews.
A UC Davis Coursera course teaching SQL specifically for data analysis workflows, covering querying, joining and aggregating real datasets.
A free-tier-friendly, interactive introduction to SQL covering SELECT, WHERE, JOIN and GROUP BY, taught through in-browser coding exercises.