SQL Window Functions: Complete Tutorial with Examples

Window functions are one of the most powerful features in SQL. They perform calculations across a set of rows related to the current row, without collapsing the result into a single row like GROUP BY does. This tutorial covers every major window function with practical examples.

What Are Window Functions?

A window function performs a calculation across a set of rows called a "window" that is related to the current row. Unlike aggregate functions with GROUP BY, window functions do not reduce the number of rows in the result. Each row keeps its identity while gaining access to calculations across related rows.

The basic syntax is:

function_name() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN start AND end
)

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition, starting at 1.

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

This assigns rank 1 to the highest-paid employee in each department, rank 2 to the second highest, and so on. Common use: finding the top N records per group, deduplication.

RANK() and DENSE_RANK()

RANK() is like ROW_NUMBER() but gives the same rank to tied values. It then skips ranks: if two employees tie at rank 2, the next rank is 4.

DENSE_RANK() also handles ties but does not skip ranks: if two employees tie at rank 2, the next rank is 3.

SELECT
  product_name,
  category,
  revenue,
  RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as dense_rank
FROM products;

LEAD() and LAG()

LAG() accesses a value from a previous row. LEAD() accesses a value from a following row. Both are essential for period-over-period comparisons.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) as month_over_month_change,
  LEAD(revenue) OVER (ORDER BY month) as next_month_revenue
FROM monthly_revenue;

This shows each month's revenue alongside the previous month's revenue and the difference. Invaluable for trend analysis.

SUM, AVG, COUNT OVER

Any aggregate function can be used as a window function by adding OVER(). This lets you calculate running totals, moving averages, and cumulative counts.

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) as running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_day_avg,
  COUNT(*) OVER (PARTITION BY customer_segment) as segment_count
FROM daily_revenue;

PARTITION BY

PARTITION BY divides rows into groups (partitions). The window function calculates independently within each partition. It is like GROUP BY but without collapsing rows.

SELECT
  department,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department) as diff_from_dept_avg
FROM employees;

Each employee's row shows their salary, their department's average, and how far they are from that average.

Frame Clauses (ROWS BETWEEN)

Frame clauses define exactly which rows are included in the window. Options include:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running total from start)
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (7-day moving window)
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (centered 3-row window)
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (from current to end)

SELECT
  date,
  revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as thirty_day_avg,
  MAX(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as all_time_high
FROM daily_revenue;

NTILE()

Divides rows into N roughly equal groups. Useful for percentile calculations and bucketing.

SELECT
  customer_name,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) as spend_quartile
FROM customers;

Quartile 1 is the top 25% of spenders, quartile 4 is the bottom 25%.

FIRST_VALUE() and LAST_VALUE()

Return the first or last value in the window frame. Useful for comparisons to period starts.

SELECT
  date,
  stock_price,
  FIRST_VALUE(stock_price) OVER (ORDER BY date) as ipo_price,
  stock_price / FIRST_VALUE(stock_price) OVER (ORDER BY date) as price_multiple
FROM stock_prices;

Real-World Examples

Revenue trend analysis: Use LAG to calculate month-over-month and year-over-year growth rates for every product line in a single query.

Customer segmentation: Use NTILE to divide customers into deciles by lifetime value, then analyze behavior differences between segments.

Employee compensation analysis: Use RANK within departments to identify pay equity issues and compare individual salaries to department medians.

Sales leaderboard: Use DENSE_RANK to create real-time sales rankings that handle tied values gracefully.

Performance Tips

Window functions can be expensive on large datasets. Minimize the number of PARTITION BY columns. Add indexes on columns used in ORDER BY and PARTITION BY. Avoid using window functions in WHERE clauses (calculate in a CTE first, then filter). Consider materializing intermediate results for complex multi-window queries.

How Skopx Generates Window Functions

When you ask Skopx "what is each employee's salary compared to their department average?", the AI automatically generates the correct window function query with PARTITION BY department and AVG OVER. You do not need to know SQL window function syntax. The platform translates natural language into optimized queries, including complex window functions, running totals, and period comparisons.

Frequently Asked Questions

What is the difference between window functions and GROUP BY?

GROUP BY collapses multiple rows into one row per group. Window functions perform calculations across groups but keep every row in the result. With GROUP BY, you get one row per department showing average salary. With a window function, you get every employee row with the department average added as a column.

When should I use ROW_NUMBER vs RANK vs DENSE_RANK?

Use ROW_NUMBER when you need unique sequential numbers (deduplication, pagination). Use RANK when ties should share the same rank and subsequent ranks should skip (sports rankings). Use DENSE_RANK when ties should share ranks without gaps (academic grading).

Are window functions available in all databases?

All major modern databases support window functions: PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+, BigQuery, Snowflake, and Redshift. Older MySQL versions (5.x) do not support them.

Can I use multiple window functions in the same query?

Yes. You can use as many window functions as needed in a single SELECT statement. Each can have its own PARTITION BY and ORDER BY clauses. For performance, combine window functions that share the same OVER clause.

How do I learn window functions quickly?

Practice with real data. Start with ROW_NUMBER and LAG (the most commonly used), then add running totals with SUM OVER. Most analysts use only 5-6 window functions regularly. Platforms like Skopx let you ask questions in plain English and see the generated SQL, which is an effective way to learn.