Master SQL window functions with visual examples. Learn ROW_NUMBER, RANK, LAG, LEAD, and more with before/after diagrams and interactive quizzes.
Jump into the SQLNoir case files and put these tips to work.
Window functions are the secret weapon that separates SQL beginners from SQL power users. They let you calculate running totals, compare rows to previous values, and rank records without the complexity of subqueries or self-joins. This guide covers everything you need to master them.
Window functions perform calculations across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into a single result, window functions keep all your rows intact while adding calculated columns.
Think of it as sliding a magnifying glass across your data. At each row, the function looks at a "window" of related rows and calculates a value. The OVER() clause defines what rows are in that window.
Window functions preserve all rows while adding calculated columns
Here's the key difference between GROUP BY and window functions:
SELECT district, COUNT(*) as crime_count FROM crime_scenes GROUP BY district; -- Result: One row per district -- district | crime_count -- Downtown | 15 -- Waterfront | 8
SELECT
case_id,
district,
COUNT(*) OVER(PARTITION BY district) as district_crime_count
FROM crime_scenes;
-- Result: Every row kept, count added as column
-- case_id | district | district_crime_count
-- 001 | Downtown | 15
-- 002 | Downtown | 15
-- 003 | Waterfront | 8Every original row is preserved. The count is calculated for each row's partition.
The OVER() clause is the heart of every window function. It defines which rows belong to the window and in what order they're processed.
SUM(amount)OVER (PARTITION BY districtORDER BY crime_date)Color-coded breakdown of window function syntax
PARTITION BY divides your data into groups. It works like GROUP BY but without collapsing rows. If you omit it, the entire result set is treated as one partition.
ORDER BY determines the order of calculation within each partition. For running totals, this controls which rows are included as the function "slides" through the data.
SELECT
crime_date,
district,
reported_crimes,
SUM(reported_crimes) OVER(
PARTITION BY district
ORDER BY crime_date
) as running_total
FROM daily_crime_stats;
-- Result:
-- crime_date | district | reported_crimes | running_total
-- 2026-01-01 | Downtown | 5 | 5
-- 2026-01-02 | Downtown | 3 | 8
-- 2026-01-03 | Downtown | 7 | 15
-- 2026-01-01 | Waterfront | 2 | 2
-- 2026-01-02 | Waterfront | 4 | 6The running total resets for each district partition.
Want to practice PARTITION BY on real crime data? SQLNoir's detective cases use these patterns to analyze evidence across multiple tables.
The three ranking functions assign numbers to rows based on their position. The difference is how they handle ties. This is THE most common window function interview question.
| Function | Ties Behavior | Gap After Ties | Best For |
|---|---|---|---|
| ROW_NUMBER() | Ignores ties | N/A | Pagination, unique IDs |
| RANK() | Same rank | Yes (skips) | Competition rankings |
| DENSE_RANK() | Same rank | No gaps | Top-N with ties |
ROW_NUMBER vs RANK vs DENSE_RANK
SELECT
suspect_name,
prior_convictions,
ROW_NUMBER() OVER(ORDER BY prior_convictions DESC) as row_num,
RANK() OVER(ORDER BY prior_convictions DESC) as rank,
DENSE_RANK() OVER(ORDER BY prior_convictions DESC) as dense_rank
FROM suspects;
-- Result (notice the ties at 5 convictions):
-- suspect_name | prior_convictions | row_num | rank | dense_rank
-- Mike | 7 | 1 | 1 | 1
-- Sara | 5 | 2 | 2 | 2
-- John | 5 | 3 | 2 | 2
-- Lisa | 3 | 4 | 4 | 3
-- Tom | 1 | 5 | 5 | 4Sara and John both have 5 convictions. ROW_NUMBER assigns them 2 and 3. RANK gives both 2, then skips to 4. DENSE_RANK gives both 2, then continues to 3.
Standard aggregate functions gain new powers with OVER(). You can calculate running totals, moving averages, cumulative counts, and percentages of total.
detective | cases_solved Mike | 5 Sara | 3 John | 7
detective | cases_solved | running_total Mike | 5 | 5 Sara | 3 | 8 John | 7 | 15
SUM() OVER(ORDER BY) adds running totals
SELECT
detective,
cases_solved,
SUM(cases_solved) OVER(ORDER BY detective) as running_total,
ROUND(
100.0 * cases_solved / SUM(cases_solved) OVER(),
1
) as pct_of_total
FROM case_closures;
-- Result:
-- detective | cases_solved | running_total | pct_of_total
-- John | 7 | 7 | 46.7
-- Mike | 5 | 12 | 33.3
-- Sara | 3 | 15 | 20.0SUM() OVER() with no ORDER BY calculates the grand total for percentage calculations.
SELECT
crime_date,
daily_crimes,
ROUND(
AVG(daily_crimes) OVER(
ORDER BY crime_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
),
1
) as moving_avg_3day
FROM daily_crime_stats;
-- Result:
-- crime_date | daily_crimes | moving_avg_3day
-- 2026-01-01 | 5 | 5.0
-- 2026-01-02 | 8 | 6.5
-- 2026-01-03 | 3 | 5.3
-- 2026-01-04 | 6 | 5.7The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the average to the current row and two previous rows.
LAG looks backward to access previous row values. LEAD looks forward to access next row values. These are essential for month-over-month comparisons, day-over-day changes, and time-series analysis.
month | crimes Jan | 45 Feb | 52 Mar | 38
month | crimes | prev_month | change Jan | 45 | NULL | NULL Feb | 52 | 45 | +7 Mar | 38 | 52 | -14
LAG() enables month-over-month comparisons
SELECT
month,
crimes,
LAG(crimes, 1) OVER(ORDER BY month) as prev_month,
crimes - LAG(crimes, 1) OVER(ORDER BY month) as change,
LEAD(crimes, 1) OVER(ORDER BY month) as next_month
FROM monthly_stats;
-- LAG(crimes, 1) means "1 row before"
-- LAG(crimes, 2) would mean "2 rows before"
-- LEAD(crimes, 1) means "1 row ahead"The optional second parameter is the offset (default 1). The optional third parameter is a default value for NULLs.
LAG(column, 1, 0) to return 0 instead of NULL when there's no previous row. This prevents NULL contamination in calculations.Think you understand LAG and time-series analysis? A body was found at Coral Bay Marina. Can you analyze surveillance timestamps to find gaps in the killer's alibi?
Start InvestigationNTILE splits your data into a specified number of roughly equal buckets. NTILE(4) creates quartiles. NTILE(10) creates deciles. It handles uneven distributions automatically.
SELECT
suspect_name,
prior_convictions,
NTILE(4) OVER(ORDER BY prior_convictions DESC) as risk_quartile
FROM suspects;
-- Result:
-- suspect_name | prior_convictions | risk_quartile
-- Mike | 12 | 1 -- Top 25% (highest risk)
-- Sara | 9 | 1
-- John | 7 | 2
-- Lisa | 5 | 2
-- Tom | 3 | 3
-- Jane | 2 | 3
-- Bob | 1 | 4
-- Alice | 0 | 4 -- Bottom 25% (lowest risk)Quartile 1 contains the top 25% of suspects by prior convictions.
NTILE(5) to instantly identify your top 20% of anything. Customers, suspects, crime hotspots.These functions grab specific positional values from the window. FIRST_VALUE gets the first row's value. LAST_VALUE gets the last. NTH_VALUE gets any arbitrary position.
SELECT
case_id,
district,
crime_date,
FIRST_VALUE(crime_date) OVER(
PARTITION BY district
ORDER BY crime_date
) as earliest_in_district,
LAST_VALUE(crime_date) OVER(
PARTITION BY district
ORDER BY crime_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as latest_in_district
FROM crime_reports;Notice the explicit frame for LAST_VALUE. Without it, you get unexpected results.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to include all rows and get the true last value.Window frames let you specify exactly which rows are included in the calculation. This is how you build moving averages, trailing sums, and other sliding window calculations.
Frame boundary options: from first row to last row
SELECT
crime_date,
daily_crimes,
ROUND(
AVG(daily_crimes) OVER(
ORDER BY crime_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
),
1
) as moving_avg_7day
FROM daily_crime_stats;
-- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
-- includes the current row + 6 previous rows = 7 daysROWS BETWEEN counts physical rows. RANGE BETWEEN uses logical value ranges (useful for date gaps).
| Frame Type | How It Works | Use Case |
|---|---|---|
| ROWS BETWEEN | Physical row count | Moving averages (N rows back) |
| RANGE BETWEEN | Logical value range | Date ranges with gaps |
| UNBOUNDED PRECEDING | From first row | Running totals from start |
| UNBOUNDED FOLLOWING | To last row | Reverse running totals |
Window frame options
With so many window functions available, how do you know which one to use? Here's a quick decision guide.
Decision flowchart for choosing window functions
| Need | Function | Example Use Case |
|---|---|---|
| Unique sequential numbers | ROW_NUMBER() | Pagination, deduplication |
| Rank with ties | RANK() / DENSE_RANK() | Leaderboards, top-N |
| Previous/next value | LAG() / LEAD() | Month-over-month, day-over-day |
| Running total | SUM() OVER(ORDER BY) | Cumulative sales, balance |
| Moving average | AVG() with frame | 7-day average, smoothing |
| Percentile buckets | NTILE(n) | Top 25%, quartiles |
| First/last in group | FIRST_VALUE / LAST_VALUE | Earliest, latest records |
Quick reference: Window function selection
Ready to put window functions to work?
SQLNoir's intermediate and advanced cases require exactly these patterns. Rank suspects, analyze time sequences, find patterns across partitions.
Start Your Investigation →Window functions have some tricky gotchas. Here are the mistakes that trip up most learners.
SELECT * FROM suspects WHERE ROW_NUMBER() OVER(ORDER BY risk) = 1
•Window functions can't be used in WHERE
•Will throw an error
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY risk) AS rn
FROM suspects
)
SELECT * FROM ranked WHERE rn = 1•Wrap in CTE first
•Filter on the alias in outer query
Window functions are evaluated after WHERE. Wrap in a CTE first.
ROW_NUMBER() OVER()
•Without ORDER BY, row numbers are arbitrary
•Results change between runs
ROW_NUMBER() OVER(ORDER BY id)
•Always specify ORDER BY for ranking
•Results are consistent
Always specify ORDER BY for deterministic ranking
LAST_VALUE(name) OVER( ORDER BY date )
•Default frame ends at current row
•LAST_VALUE returns the current row, not the actual last
LAST_VALUE(name) OVER(
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)•Explicit frame includes all rows
•Returns the true last value
LAST_VALUE needs an explicit frame to work as expected
These patterns come up constantly in SQL interviews. Master them and you'll handle most window function questions with confidence.
| Pattern | Function | Key Technique |
|---|---|---|
| Top N per group | ROW_NUMBER() | PARTITION BY + filter rn <= N |
| Running total | SUM() OVER(ORDER BY) | Cumulative with ORDER BY |
| Month-over-month | LAG() | Compare current to previous |
| Deduplication | ROW_NUMBER() | Keep row where rn = 1 |
| Percentile ranking | NTILE(100) or PERCENT_RANK() | Bucket into percentages |
Common interview patterns
WITH ranked_suspects AS (
SELECT
suspect_name,
district,
risk_score,
ROW_NUMBER() OVER(
PARTITION BY district
ORDER BY risk_score DESC
) as rank_in_district
FROM suspects
)
SELECT *
FROM ranked_suspects
WHERE rank_in_district <= 3;
-- Returns top 3 highest-risk suspects for each districtThis "top N per group" pattern appears in almost every SQL interview.
Q1.Which function gives unique numbers even for tied values?
Q2.What does LAG(sales, 2) return?
Q3.You want to filter on ROW_NUMBER() = 1. What do you need to do?
Q4.NTILE(4) creates what?
Functions that perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY. They use OVER() to define the window of rows to operate on.
ROW_NUMBER gives unique numbers (1,2,3,4) even for ties. RANK gives same rank for ties but skips numbers after (1,2,2,4). DENSE_RANK gives same rank for ties without gaps (1,2,2,3).
No. Window functions are evaluated after WHERE. Wrap your query in a CTE or subquery first, then filter on the window function alias in the outer query.
PARTITION BY divides the result set into groups (partitions) for the window function to operate on. It's like GROUP BY but doesn't collapse rows.
Use window functions when you need to keep all rows while adding calculated columns (running totals, rankings, comparisons). Use GROUP BY when you want to collapse rows into summary statistics.
Window functions transform what's possible with SQL. Running totals, rankings, time-series comparisons, and percentile analysis all become straightforward once you understand OVER(), PARTITION BY, and ORDER BY.
The key patterns to remember: use ROW_NUMBER for unique numbering and deduplication, RANK/DENSE_RANK for leaderboards, LAG/LEAD for comparing rows, and SUM/AVG with frames for running and moving calculations.
Practice these patterns and they'll become second nature. Window functions are one of those SQL skills that, once learned, you'll use constantly.