SQL Window Functions: Advanced Analytics Made Simple
Go beyond basic aggregation with window functions. Learn 9 powerful patterns for rankings, running totals, moving averages, and comparative analysis without complex joins.
Why Window Functions Are Essential for Advanced SQL
Basic aggregate functions with GROUP BY are powerful, but they collapse rows into summaries. What if you need to rank products within categories while showing all product details? Or calculate running totals without losing individual transactions? Or compare each row to the previous one? Window functions solve these problems elegantly.
Before window functions, these analyses required complex self-joins, correlated subqueries, or multiple query passes. A simple top-3-per-category query could become a nested mess. Window functions provide a cleaner, more performant alternative that keeps your SQL readable and maintainable.
Window functions are standard in modern SQL and appear frequently in data analyst and engineering interviews. They enable sophisticated analytics in single queries: customer lifetime value calculations, time-series analysis, cohort studies, and ranking scenarios. Understanding window functions separates intermediate SQL users from advanced practitioners.
This guide covers 9 essential window function patterns that handle the most common analytical scenarios. Each pattern includes practical examples you can adapt to your own data analysis needs.
Window Functions vs Aggregate Functions
The key distinction: aggregate functions collapse rows, window functions don't.
| Feature | Aggregate + GROUP BY | Window Function |
|---|---|---|
| Output rows | One row per group | One row per input row |
| Reduces row count | Yes — collapses rows | No — all rows preserved |
| Access non-grouped columns | No — only aggregated cols | Yes — any column available |
| Order within calculation | No | Yes — ORDER BY in OVER() |
| Frame control | No | Yes — ROWS/RANGE BETWEEN |
| Partition data | Yes — via GROUP BY | Yes — via PARTITION BY |
| Use in WHERE clause | No — use HAVING | No — wrap in subquery/CTE |
| Typical use case | Summaries and totals | Rankings, running totals, comparisons |
Quick rule:
If you need fewer rows than you started with, use GROUP BY. If you need the same number of rows plus analytical calculations, use window functions.
Common Mistakes with Window Functions
Trying to Use Window Functions in WHERE Clause
Window functions execute after WHERE clause, so you can't filter on their results directly. Use a subquery or CTE instead: wrap your window function query, then filter in the outer query. This is a common pattern for getting top N per group.
Forgetting UNBOUNDED FOLLOWING for LAST_VALUE
The default window frame for LAST_VALUE only goes up to the current row, not the actual last row in the partition. This surprises developers who expect the true last value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the entire partition.
Confusing Window Functions with GROUP BY
Window functions don't collapse rows like GROUP BY does. Each input row produces one output row. PARTITION BY divides data for window calculations but doesn't reduce row count. If you need summary rows, use GROUP BY. If you need all rows plus analytical calculations, use window functions.
Not Understanding Frame Specifications
Window frames (ROWS BETWEEN ... AND ...) control which rows the function considers. Forgetting to specify frames can give unexpected results, especially with aggregate window functions like SUM or AVG. Default frames vary by function, so explicitly define them for clarity.
Using Window Functions Without ORDER BY When Needed
ROW_NUMBER, RANK, LAG, LEAD, and running totals require ORDER BY to make sense. Without it, row order is undefined, giving non-deterministic results. Always include ORDER BY in the OVER clause when the calculation depends on row order.
9 Essential Window Function Patterns
These patterns solve the most common analytical scenarios you'll encounter in data analysis and reporting.
ROW_NUMBER - Assign Sequential Numbers
Give each row a unique sequential number within partitions
Example:
SELECT
product_name,
category,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
FROM products;When to Use:
ROW_NUMBER assigns a unique number to each row, starting at 1. Use PARTITION BY to restart numbering for each group. Essential for pagination, getting top N per category, or removing duplicates. Unlike RANK, it never assigns the same number to multiple rows.
RANK and DENSE_RANK - Handle Ties Differently
Rank rows with different tie-breaking strategies
Example:
SELECT
student_name,
score,
RANK() OVER (ORDER BY score DESC) as rank,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM exam_results;
-- RANK: 1, 2, 2, 4 (skips 3)
-- DENSE_RANK: 1, 2, 2, 3 (no gaps)When to Use:
Both functions handle ranking with ties. RANK skips numbers after ties (1, 2, 2, 4), while DENSE_RANK doesn't skip (1, 2, 2, 3). Use RANK for competition-style rankings. Use DENSE_RANK when you need consecutive numbers without gaps.
LAG and LEAD - Access Previous and Next Rows
Compare current row with adjacent rows
Example:
SELECT
order_date,
total_amount,
LAG(total_amount) OVER (ORDER BY order_date) as previous_order,
LEAD(total_amount) OVER (ORDER BY order_date) as next_order,
total_amount - LAG(total_amount) OVER (ORDER BY order_date) as change_from_previous
FROM orders;When to Use:
LAG accesses the previous row, LEAD accesses the next row. Perfect for calculating change over time, comparing consecutive values, or finding gaps in sequences. Specify offset (default 1) and default value for edge cases.
Running Totals with SUM Window Function
Calculate cumulative sums across ordered rows
Example:
SELECT
order_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM orders;When to Use:
Window SUM creates running totals by summing all rows from the start up to the current row. Essential for cumulative metrics like year-to-date revenue, running inventory balances, or cumulative user counts. The frame clause controls which rows to sum.
Moving Averages with Window Frames
Calculate averages over a sliding window of rows
Example:
SELECT
order_date,
total_amount,
AVG(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg_3day,
AVG(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7day
FROM daily_sales;When to Use:
Moving averages smooth out fluctuations by averaging values over a sliding window. Use ROWS BETWEEN to define the window size. Common for trend analysis, stock prices, or smoothing noisy data. Adjust the window size based on your needs.
PARTITION BY - Group Window Calculations
Reset window functions for each group independently
Example:
SELECT
category,
product_name,
sales,
AVG(sales) OVER (PARTITION BY category) as category_avg,
sales - AVG(sales) OVER (PARTITION BY category) as diff_from_avg,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category
FROM product_sales;When to Use:
PARTITION BY divides data into groups, and window functions operate independently within each partition. Like GROUP BY but doesn't collapse rows. Calculate per-group statistics while keeping all row details. Essential for comparative analysis.
FIRST_VALUE and LAST_VALUE
Get first or last value in a window frame
Example:
SELECT
product_name,
order_date,
price,
FIRST_VALUE(price) OVER (
PARTITION BY product_name
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as first_price,
price - FIRST_VALUE(price) OVER (
PARTITION BY product_name
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as price_change
FROM price_history;When to Use:
FIRST_VALUE gets the first value in the window frame, LAST_VALUE gets the last. Use with UNBOUNDED FOLLOWING to ensure you get the actual last value. Useful for comparing current values to initial or final values, calculating total change, or baseline comparisons.
NTILE - Divide Data into Buckets
Split rows into a specified number of roughly equal groups
Example:
SELECT
customer_id,
total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) as quartile,
NTILE(10) OVER (ORDER BY total_spent DESC) as decile
FROM customer_totals;When to Use:
NTILE divides ordered rows into N buckets of approximately equal size. Use for creating quartiles, deciles, or percentiles. Perfect for customer segmentation (top 25%, bottom 25%), A/B test groups, or any bucket-based analysis.
Combining Multiple Window Functions
Use several window functions together for complex analysis
Example:
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
PERCENT_RANK() OVER (ORDER BY salary) as overall_percentile
FROM employees;When to Use:
Real analysis combines multiple window functions. Calculate rankings, averages, and comparisons in a single query. Each window function can have different PARTITION BY and ORDER BY clauses. This enables sophisticated analysis without self-joins or subqueries.
Your First Steps with Window Functions
Step 1: Start with ROW_NUMBER for Rankings
Begin with ROW_NUMBER to understand the basic structure: function name, OVER keyword, ORDER BY clause. Practice with and without PARTITION BY to see the difference.
-- Simple ranking without partitions
SELECT product_name, price,
ROW_NUMBER() OVER (ORDER BY price DESC) as overall_rank
FROM products;
-- Ranking within categories
SELECT product_name, category, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;Step 2: Practice Running Totals with Window Frames
Learn window frames by creating running totals. Experiment with different frame specifications to see how they affect results. This builds intuition for more complex patterns.
-- Running total (cumulative sum)
SELECT order_date, amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM orders;
-- 7-day moving average
SELECT order_date, amount,
AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as avg_7day
FROM daily_sales;Step 3: Solve Real Problems with Combined Functions
Apply window functions to actual analysis scenarios. Get top N per group, compare to previous periods, or calculate percentiles. Combining multiple window functions reveals their full power.
-- Top 3 products per category
SELECT * FROM (
SELECT
category,
product_name,
sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
FROM products
) WHERE rn <= 3;
-- Compare to previous month
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) as change
FROM monthly_sales;Frequently Asked Questions About Window Functions
What is the difference between window functions and GROUP BY?
GROUP BY aggregates rows into summary rows, losing individual row details. Window functions perform calculations across rows while keeping all original rows. With GROUP BY, you get one row per group. With window functions, you get one row per input row, plus the calculated window values.
When should I use RANK vs DENSE_RANK vs ROW_NUMBER?
Use ROW_NUMBER when you need unique sequential numbers (no duplicates). Use RANK for competition-style rankings where ties get the same rank and the next rank skips numbers. Use DENSE_RANK when you want consecutive ranks with no gaps after ties. Choose based on how you want to handle tied values.
What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?
This is a window frame specification. UNBOUNDED PRECEDING means from the very first row, CURRENT ROW means up to the current row. Together, they create a running total or cumulative calculation. Other options include N PRECEDING (N rows before), N FOLLOWING (N rows after), and UNBOUNDED FOLLOWING (to the last row).
Can I use window functions in WHERE clause?
No, window functions cannot appear in WHERE clauses because WHERE filters rows before window calculations happen. Instead, use a subquery or CTE: SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (...) as rn FROM table) WHERE rn = 1. This pattern is common for getting top N per group.
How do I get the top 3 items per category with window functions?
Use ROW_NUMBER or RANK with PARTITION BY in a subquery, then filter in the outer query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn FROM products) WHERE rn <= 3. This is more efficient than self-joins for top-N queries.
What is the difference between ROWS and RANGE in window frames?
ROWS counts physical rows (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row plus 2 rows before). RANGE considers logical ranges based on values in ORDER BY column. ROWS is more intuitive and commonly used. RANGE is useful when you need to include all rows with the same value.
Can I use multiple PARTITION BY columns?
Yes, you can partition by multiple columns: PARTITION BY region, category. This creates separate windows for each unique combination. For example, with region and category, each region-category combination gets its own independent set of rankings or calculations.
Why is LAST_VALUE returning unexpected results?
LAST_VALUE with default frame specification (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) only looks up to the current row, not the actual last row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the entire partition and get the true last value.
Are window functions slower than GROUP BY?
Not necessarily. Window functions avoid self-joins and correlated subqueries, often improving performance. They do require sorting (for ORDER BY) and potentially more memory (for PARTITION BY). For simple aggregates, GROUP BY might be faster, but for complex analysis, window functions are usually more efficient than alternative approaches.
Window Functions Practice Problems
Test your understanding. Try writing the query before expanding the solution.
Problem 1: Top 2 Products per Category
Write a query that returns the top 2 best-selling products in each category. Products with the same sales should both appear.
Solution
SELECT category, product_name, sales
FROM (
SELECT
category,
product_name,
sales,
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dr
FROM products
)
WHERE dr <= 2;
-- Use DENSE_RANK (not ROW_NUMBER) so tied products both appearProblem 2: Month-over-Month Revenue Change
Calculate each month's revenue, the previous month's revenue, and the percentage change between them.
Solution
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month) * 100,
2
) AS pct_change
FROM monthly_revenue
ORDER BY month;Problem 3: Remove Duplicate Rows, Keep Latest
A users table has duplicate email addresses due to a data import error. Keep only the most recently created row for each email.
Solution
DELETE FROM users
WHERE id NOT IN (
SELECT id FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC
) AS rn
FROM users
)
WHERE rn = 1
);
-- ROW_NUMBER assigns 1 to the latest row per email
-- We keep only rn = 1Problem 4: Classify Customers into Spending Quartiles
Segment customers into four equal groups (Q1–Q4) based on total spend, from highest spenders (Q1) to lowest (Q4).
Solution
SELECT
customer_id,
total_spend,
NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile,
CASE NTILE(4) OVER (ORDER BY total_spend DESC)
WHEN 1 THEN 'High Value'
WHEN 2 THEN 'Mid-High'
WHEN 3 THEN 'Mid-Low'
WHEN 4 THEN 'Low Value'
END AS segment
FROM customer_totals
ORDER BY total_spend DESC;Unlock Advanced SQL Analytics
This guide introduces window functions. The complete book provides comprehensive coverage from SQL basics to expert techniques, including advanced window function patterns and optimization strategies.
What You'll Get:
- ✓350+ practical SQL examples from basics to expert level
- ✓140+ SQL interview questions with detailed solutions
- ✓Complete e-commerce database for hands-on practice
- ✓SQL code library with 380+ ready-to-use statements
- ✓2 bonus SQL cheat sheets: Glossary and Dialect Differences
Trusted by developers worldwide
4.8/5 rating - Bestselling SQL book