SQL Window Functions: Complete Guide with Examples (2026)

Window functions are arguably the most tested SQL concept in technical interviews for Data Analysts, Analytics Engineers, and Data Scientists.

Most tutorials jump straight into complex syntax like OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) without explaining the fundamental reason why window functions exist.

In this guide, we will build your understanding from the absolute ground up: starting with the limitations of GROUP BY, seeing why row collapsing causes problems, and mastering every major window function using concrete sample tables. For chaining window functions with named steps, see our SQL CTE Guide.

1. The Fundamental Problem: Why GROUP BY Isn't Enough

To understand window functions, you must first understand the collapse trap of GROUP BY.

Imagine we have a company database with an employees table:

Sample Table: employees

What Happens When You Use GROUP BY

Suppose your manager asks: "What is the average salary in each department?"

You write a standard GROUP BY query:

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Result of GROUP BY:

GROUP BY succeeded in calculating the averages, but look at what happened to the table:

1. The original 6 rows were collapsed into 2 rows.
2. Individual details were destroyed: You lost Sarah, Alex, Rohan, Priya, David, and Ananya.
3. Individual salaries were erased: You can no longer see what Alex makes.

The Business Question That Breaks GROUP BY

Now your manager asks a slightly deeper business question:

"Show me every employee with their name and salary, alongside their department's average salary, and calculate how much above or below the department average they are."

With basic GROUP BY, you cannot do this in a single query. Because GROUP BY collapses the rows, you can't display individual employee names alongside group aggregates without writing messy subqueries or self-joins:

-- The messy, slow way WITHOUT window functions (Self-Join):
SELECT
  e.name,
  e.department,
  e.salary,
  d.avg_salary,
  (e.salary - d.avg_salary) AS diff_from_avg
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) d ON e.department = d.department;

This query requires scanning the table twice, creating an intermediate subquery, and joining it back to the original table. It is verbose, slow on large datasets, and error-prone.

2. The Window Function Solution: Aggregate Without Collapsing

This is the exact reason Window Functions were invented.

A window function looks at a group of rows (a "window") to calculate an aggregate or rank, but keeps every individual row intact in the final output.

Here is how you solve the exact same business question using a window function:

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Output:

Notice the Magic:

• All 6 original rows are preserved.
• The dept_avg_salary column is calculated specifically for each department.
• We calculated diff_from_avg on the fly in a single scan without any joins or subqueries!

GROUP BY vs Window Functions: Direct Comparison

3. Deconstructing the OVER() Clause

The OVER() clause is what turns a regular SQL function into a window function. It defines the boundaries of the window the function looks at.

FUNCTION() OVER (
  PARTITION BY column1
  ORDER BY column2
  ROWS BETWEEN frame_start AND frame_end
)

Let's break down each part of the OVER() clause:

Step 1: Empty OVER() (Whole Table Window)

If you provide an empty OVER(), the window is the entire dataset:

SELECT
  name,
  salary,
  AVG(salary) OVER () AS company_avg_salary
FROM employees;

Result: Every employee row receives the exact same overall company average salary (₹10,00,000).

Step 2: PARTITION BY (Group Slices)

PARTITION BY divides the rows into logical groups (like GROUP BY), but calculates the value per group without combining rows:

SELECT
  name,
  department,
  salary,
  MAX(salary) OVER (PARTITION BY department) AS highest_dept_salary
FROM employees;

Result: Sarah, Alex, and Rohan all see Engineering's maximum (₹15,00,000), while Priya, David, and Ananya see Marketing's maximum (₹10,00,000).

Step 3: ORDER BY (Window Ordering & Cumulative Frame)

When you add ORDER BY inside OVER(), it tells SQL to process the window in a specific sequence. This enables rankings and running totals:

SELECT
  name,
  department,
  salary,
  SUM(salary) OVER (PARTITION BY department ORDER BY salary ASC) AS running_dept_payroll
FROM employees;

4. The 4 Families of Window Functions

SQL window functions fall into 4 main categories:

Let's explore each family with real examples.

5. Ranking Functions: ROW_NUMBER vs RANK vs DENSE_RANK

The most common interview question asks candidates to rank items (e.g., "Find the top 3 products per category" or "Find the 2nd highest salary").

All three functions assign ranks, but they treat tied values very differently:

Sample Dataset with Ties:

Suppose we have an exam scores table:

Let's run all three ranking functions simultaneously:

SELECT
  student,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores;

Side-by-Side Result:

The Golden Rule of Ranking:

1. ROW_NUMBER(): Always strictly sequential (1, 2, 3, 4, 5). Never produces ties. Use this for pagination or deduplicating records.
2. RANK(): Assigns tied rows the same rank, but skips numbers to account for the tie (1, 2, 2, 4). Use this for sports leaderboards (e.g., two people get silver medals, no one gets bronze).
3. DENSE_RANK(): Assigns tied rows the same rank, with no gaps (1, 2, 2, 3). Use this for "Find the Nth highest value" questions where tied 1st place shouldn't skip 2nd place!

Real Interview Scenario: Finding the 2nd Highest Salary Per Department

WITH ranked_salaries AS (
  SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) as salary_rank
  FROM employees
)
SELECT name, department, salary
FROM ranked_salaries
WHERE salary_rank = 2;

6. Value & Navigation Functions: LAG() and LEAD()

LAG() and LEAD() allow you to look at previous or subsequent rows without performing self-joins. This is essential for period-over-period growth, session interval analysis, and churn detection.

• LAG(column, offset, default): Fetches a value from offset rows before the current row.
• LEAD(column, offset, default): Fetches a value from offset rows after the current row.

Sample Table: monthly_revenue

Calculating Month-Over-Month (MoM) Growth Percentage

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_dollar_change,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct
FROM monthly_revenue;

Result:

7. Aggregate Window Functions: Running Totals & Moving Averages

1. Cumulative Running Total

To calculate a cumulative total that sums numbers up to the current row:

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_revenue;

Result:

2. 7-Day Rolling Moving Average

Moving averages smooth out erratic day-to-day spikes to reveal true underlying trends. We specify the sliding frame using ROWS BETWEEN:

SELECT
  date,
  daily_active_users,
  ROUND(
    AVG(daily_active_users) OVER (
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    0
  ) AS moving_avg_7d
FROM daily_metrics;

How the frame works: 6 PRECEDING AND CURRENT ROW takes the current day plus the 6 previous days = 7 days total.

8. The Critical Trap: Why You Can't Use Window Functions in WHERE

Every SQL practitioner eventually writes this query and gets greeted with an error:

-- ❌ THIS WILL FAIL WITH A SYNTAX ERROR:
SELECT *
FROM orders
WHERE ROW_NUMBER() OVER (ORDER BY order_date DESC) = 1;

Error: Window functions are not allowed in WHERE

Why Does This Happen? (SQL Order of Execution)

SQL clauses do not execute in the order they are written (SELECT appears first in query text, but executes near the very end):

Because WHERE executes at Step 2, window functions (computed in Step 5) have not been evaluated yet!

The Fix: Wrap in a CTE or Subquery

To filter on a window function, you compute it inside a Common Table Expression (WITH clause) or subquery, then filter the resulting table in the outer query:

-- ✅ THE CORRECT WAY:
WITH ranked_orders AS (
  SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT order_id, customer_id, order_date, total_amount
FROM ranked_orders
WHERE rn = 1;

9. Quick Reference Cheatsheet

10. Practice These on Real Databases

Understanding window functions conceptually is the first step. Writing them fluently under interview conditions requires hands-on execution.