SQLAdvanced SQL Queriesadvanced26 min read

Window Functions

Perform advanced analytics across rows without collapsing individual records. Learn OVER, PARTITION BY, window ordering, ranking, running totals, moving averages, and comparing rows with PostgreSQL window functions.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain how window functions calculate values across related rows without collapsing individual result rows
✓Use OVER() and PARTITION BY to define calculation windows
✓Distinguish window ORDER BY from the final query ORDER BY
✓Calculate running totals, cumulative sums, and moving averages
✓Rank rows using ROW_NUMBER, RANK, and DENSE_RANK
✓Compare rows using LAG and LEAD
✓Understand window frames, NULL handling, and common performance considerations

Introduction to Window Functions

## What is a window function?

A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY, it can return a calculated value for every individual row.

Imagine a sales table:

| sale_id | employee | amount |
|---:|---|---:|
| 1 | Asha | 100 |
| 2 | Ravi | 200 |
| 3 | Asha | 150 |
| 4 | Ravi | 300 |

Suppose you want to display every sale and also show the total sales for that employee.

With GROUP BY, the result normally contains one row per employee. With a window function, the result keeps all four sales and adds the employee's total to each row.

## The OVER() clause

The OVER() clause tells SQL which rows to consider for a window calculation.

SELECT
sale_id,
employee,
amount,
SUM(amount) OVER () AS overall_total
FROM sales;

Here, every row receives the total of all amounts in the query's input result.

## PARTITION BY

PARTITION BY divides rows into separate calculation groups.

SUM(amount) OVER (
PARTITION BY employee
)

This calculates a separate total for each employee while retaining each sale row.

## Window ORDER BY

ORDER BY inside OVER() determines the order used by functions such as ROW_NUMBER and by ordered calculations such as running totals.

It does not guarantee the final order of the query result. Use an outer ORDER BY when you need predictable presentation.

Window functions are useful for reporting, ranking, running balances, period-over-period comparisons, and analytics without losing row-level detail.

Core Syntax and OVER()

## Basic syntax

function_name(expression) OVER (
PARTITION BY column_name
ORDER BY column_name
ROWS BETWEEN ...
)

The components are:

- **Function:** The calculation, such as SUM, AVG, ROW_NUMBER, or LAG.
- **OVER():** Declares the window.
- **PARTITION BY:** Optional; divides rows into independent groups.
- **ORDER BY:** Optional for some functions, required for meaningful ordered calculations.
- **Window frame:** Optional; defines which rows around the current row participate in frame-sensitive calculations.

You can use OVER() without PARTITION BY or ORDER BY. In that case, the window generally includes all rows in the query's input result.

For example, SUM(amount) OVER () returns the overall total on every row.

The following query uses a self-contained dataset so you can run it without creating a permanent table.

Calculate an Overall Total

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH sales(sale_id, employee, amount) AS (
    VALUES
        (1, 'Asha', 100),
        (2, 'Ravi', 200),
        (3, 'Asha', 150),
        (4, 'Ravi', 300)
)
SELECT
    sale_id,
    employee,
    amount,
    SUM(amount) OVER () AS overall_total
FROM sales
ORDER BY sale_id;

Output

sale_id | employee | amount | overall_total --------+----------+--------+-------------- 1 | Asha | 100 | 750 2 | Ravi | 200 | 750 3 | Asha | 150 | 750 4 | Ravi | 300 | 750

The total is 100 + 200 + 150 + 300 = 750. OVER() calculates the sum across all input rows and repeats that value on each result row. The outer ORDER BY makes the displayed order predictable.

PARTITION BY: Calculate Within Groups

## Calculate each employee's total sales

PARTITION BY creates a separate window for each employee.

Unlike GROUP BY, it does not collapse all sales from the same employee into one row. Each sale remains visible.

For the sample sales:

- Asha's total is 100 + 150 = 250.
- Ravi's total is 200 + 300 = 500.

The window calculation repeats the employee total on each sale belonging to that employee.

You can partition by more than one column. For example, PARTITION BY employee, sale_year would create a separate window for each employee-year combination.

A window partition is a calculation group, not a physical table and not a permanent grouping of the data.

Total Sales per Employee

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH sales(sale_id, employee, amount) AS (
    VALUES
        (1, 'Asha', 100),
        (2, 'Ravi', 200),
        (3, 'Asha', 150),
        (4, 'Ravi', 300)
)
SELECT
    sale_id,
    employee,
    amount,
    SUM(amount) OVER (
        PARTITION BY employee
    ) AS employee_total
FROM sales
ORDER BY sale_id;

Output

sale_id | employee | amount | employee_total --------+----------+--------+--------------- 1 | Asha | 100 | 250 2 | Ravi | 200 | 500 3 | Asha | 150 | 250 4 | Ravi | 300 | 500

Asha's rows form one partition and Ravi's rows form another. SUM calculates the total within each partition, but all four original rows remain in the result.

Running Totals and Cumulative Sums

## What is a running total?

A running total adds values in a specified order and keeps a cumulative sum as it moves through the rows.

For example, if a bank account receives transactions of 100, 50, and -20, its running balance from a starting balance of zero is 100, 150, and 130.

A common pattern is:

SUM(amount) OVER (
ORDER BY transaction_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

The frame means:

- UNBOUNDED PRECEDING: Start at the first row of the partition.
- CURRENT ROW: End at the current row.
- ROWS: Count physical rows in the ordered window.

Use a deterministic ordering, usually with a unique tie-breaker such as transaction_id. If multiple transactions share the same date, ordering only by date may not fully determine which transaction comes first.

The explicit ROWS frame is important. With ORDER BY, the default frame in PostgreSQL can include peer rows tied on the ordering values, which may cause multiple rows to receive the same cumulative total.

Running Total by Transaction

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH transactions(transaction_id, amount) AS (
    VALUES
        (1, 100),
        (2, 50),
        (3, -20),
        (4, 80)
)
SELECT
    transaction_id,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM transactions
ORDER BY transaction_id;

Output

transaction_id | amount | running_total ---------------+--------+-------------- 1 | 100 | 100 2 | 50 | 150 3 | -20 | 130 4 | 80 | 210

Each row adds its amount to all preceding rows in the specified order. The negative transaction reduces the running total from 150 to 130. The explicit ROWS frame makes the calculation advance one row at a time.

Moving Averages and Window Frames

## What is a moving average?

A moving average calculates an average over a changing group of nearby rows. It can smooth short-term variation in sales, traffic, prices, or other metrics.

Suppose daily sales are 10, 20, 30, and 40. A three-row moving average is:

- First row: 10
- Second row: (10 + 20) / 2 = 15
- Third row: (10 + 20 + 30) / 3 = 20
- Fourth row: (20 + 30 + 40) / 3 = 30

The first two rows use fewer than three rows because the frame begins at the start of the available data.

## Define the frame

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This includes the current row and up to two preceding rows.

It means the last three rows of the ordered partition, not necessarily the last three calendar days. If a date is missing, the window still counts rows.

## ROWS versus RANGE

- ROWS describes a frame by row positions.
- RANGE describes a frame based on ordering values and peer groups. For suitable numeric or date ordering expressions, it can represent a value-based range.
- GROUPS counts peer groups with equal ordering values.

The frame type changes which rows participate in the calculation. For time-based analytics with missing dates, consider whether you need a calendar table or a date-based RANGE frame rather than simply counting rows.

AVG ignores NULL inputs when computing the average. A frame containing no non-NULL values produces NULL.

Three-Row Moving Average

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH daily_sales(day_number, amount) AS (
    VALUES
        (1, 10),
        (2, 20),
        (3, 30),
        (4, 40)
)
SELECT
    day_number,
    amount,
    ROUND(
        AVG(amount) OVER (
            ORDER BY day_number
            ROWS BETWEEN 2 PRECEDING
                     AND CURRENT ROW
        ),
        2
    ) AS moving_average
FROM daily_sales
ORDER BY day_number;

Output

day_number | amount | moving_average -----------+--------+--------------- 1 | 10 | 10.00 2 | 20 | 15.00 3 | 30 | 20.00 4 | 40 | 30.00

The frame contains the current row and at most two preceding rows. The first two averages use only the rows available. PostgreSQL's ROUND(value, 2) formats the numeric average to two decimal places.

Ranking: ROW_NUMBER, RANK, and DENSE_RANK

## Three functions for ranking

Window functions can assign positions to rows based on their values.

### ROW_NUMBER()

Assigns a unique sequential number to every row in the window. Tied values still receive different row numbers. Add a tie-breaker if the assignment must be deterministic.

### RANK()

Rows tied on the ordering values receive the same rank. The next rank skips positions.

For scores 100, 90, 90, and 80, the ranks are 1, 2, 2, and 4.

### DENSE_RANK()

Rows tied on the ordering values receive the same rank, but the next distinct value does not skip a rank.

For scores 100, 90, 90, and 80, the dense ranks are 1, 2, 2, and 3.

These functions are useful for leaderboards, ranking products by revenue, and selecting the highest-paid employees in each department.

To select the top three rows per department, calculate ROW_NUMBER in a CTE or subquery and filter it in the outer query. Window functions cannot generally be used directly in the same SELECT's WHERE clause.

Compare Ranking Functions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH scores(player, score) AS (
    VALUES
        ('Asha', 100),
        ('Ravi', 90),
        ('Meera', 90),
        ('Kabir', 80)
)
SELECT
    player,
    score,
    ROW_NUMBER() OVER (
        ORDER BY score DESC, player
    ) AS row_num,
    RANK() OVER (
        ORDER BY score DESC
    ) AS rank_num,
    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS dense_rank_num
FROM scores
ORDER BY score DESC, player;

Output

player | score | row_num | rank_num | dense_rank_num -------+-------+---------+----------+--------------- Asha | 100 | 1 | 1 | 1 Meera | 90 | 2 | 2 | 2 Ravi | 90 | 3 | 2 | 2 Kabir | 80 | 4 | 4 | 3

ROW_NUMBER uses score and player for deterministic ordering. RANK and DENSE_RANK use score alone, so Meera and Ravi tie. RANK skips from 2 to 4, whereas DENSE_RANK advances from 2 to 3.

LAG and LEAD: Compare Adjacent Rows

## Access values from other rows

LAG(expression) accesses a value from a preceding row in the ordered window. LEAD(expression) accesses a value from a following row.

They are useful for:

- Comparing this month's revenue with last month's revenue.
- Finding changes between consecutive transactions.
- Detecting gaps in event sequences.
- Calculating period-over-period growth.

The basic pattern is:

LAG(amount) OVER (
ORDER BY month_number
)

The default offset is 1. If no preceding row exists, LAG returns NULL unless a default value is supplied.

For growth calculations, handle a previous value of zero explicitly. Dividing by zero causes an error, and a NULL previous value means there is no comparison row.

Compare Each Value with the Previous Value

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH monthly_sales(month_number, amount) AS (
    VALUES
        (1, 100),
        (2, 130),
        (3, 120),
        (4, 180)
),
compared AS (
    SELECT
        month_number,
        amount,
        LAG(amount) OVER (
            ORDER BY month_number
        ) AS previous_amount
    FROM monthly_sales
)
SELECT
    month_number,
    amount,
    previous_amount,
    amount - previous_amount AS change
FROM compared
ORDER BY month_number;

Output

month_number | amount | previous_amount | change -------------+--------+-----------------+------- 1 | 100 | NULL | NULL 2 | 130 | 100 | 30 3 | 120 | 130 | -10 4 | 180 | 120 | 60

LAG returns the amount from the preceding month in the ordered result. The first row has no previous row, so previous_amount and change are NULL. The CTE makes the window result available to the outer SELECT.

Filtering Window Results

## Why use a CTE or subquery?

Window functions are evaluated after the WHERE clause in the logical processing of a SELECT. Therefore, you cannot normally filter on a window-function result directly in the WHERE clause of that same SELECT.

For example, this pattern is invalid:

SELECT
employee_id,
ROW_NUMBER() OVER (
ORDER BY salary DESC
) AS position
FROM employees
WHERE position <= 3;

Instead, calculate the row number in a CTE or subquery, then filter it in the outer query.

## Top N per group

To find the top two salaries in each department:

1. Partition rows by department.
2. Order each department by salary descending.
3. Assign ROW_NUMBER.
4. Filter row numbers in the outer query.

Decide how ties should behave before choosing a ranking function. ROW_NUMBER returns at most N rows per partition, whereas filtering RANK or DENSE_RANK can return more than N rows when ties occur.

Top Two Employees per Department

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH employees(employee_id, employee_name, department, salary) AS (
    VALUES
        (1, 'Asha', 'Engineering', 90000),
        (2, 'Ravi', 'Engineering', 85000),
        (3, 'Meera', 'Engineering', 85000),
        (4, 'Kabir', 'Sales', 70000),
        (5, 'Neha', 'Sales', 75000)
),
ranked AS (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, employee_id
        ) AS position
    FROM employees
)
SELECT
    employee_id,
    employee_name,
    department,
    salary,
    position
FROM ranked
WHERE position <= 2
ORDER BY department, position;

Output

ROW_NUMBER assigns a separate sequence within each department. employee_id breaks salary ties deterministically, so Ravi is selected ahead of Meera. The outer WHERE filters the already calculated positions.

Common Mistakes

## Mistake 1: Confusing GROUP BY with window functions

GROUP BY combines rows into groups and normally returns one result row per group. Window functions retain the rows on which the calculation is performed.

Use GROUP BY when you need one summary row per group. Use a window function when you need row-level details alongside a group calculation.

## Mistake 2: Assuming PARTITION BY sorts rows

PARTITION BY divides rows into calculation groups. It does not specify the order of rows within a group. Add ORDER BY inside OVER() when the calculation depends on order.

## Mistake 3: Forgetting a deterministic tie-breaker

ROW_NUMBER() OVER (ORDER BY score DESC) may assign tied rows in an unspecified order. Add a stable unique column when consistent row numbering is required.

## Mistake 4: Misunderstanding RANK and DENSE_RANK

RANK leaves gaps after ties. DENSE_RANK does not. ROW_NUMBER gives each row a different number even when the ranking values tie.

## Mistake 5: Using the default frame for every running calculation

An ordered window's default frame can include peer rows with equal ordering values. For a row-by-row running total, explicitly specify a ROWS frame and use a deterministic ordering.

## Mistake 6: Filtering a window alias in WHERE

The WHERE clause cannot normally reference a window function calculated in the same SELECT list. Use a CTE or subquery, then filter in the outer query.

## Mistake 7: Ignoring NULL values

SUM and AVG ignore NULL inputs. LAG and LEAD can return NULL when the requested row does not exist or when the value in that row is NULL. Handle these cases according to the business requirement.

Practice Exercises

Complete the following exercises using PostgreSQL.

### Exercise 1: Overall total

Using a sales dataset, show every sale and the total amount across all sales.

Requirement: Use SUM(amount) OVER () and preserve every input row.

### Exercise 2: Department total

Show each employee's salary and the total salary for their department.

Requirement: Use PARTITION BY department.

### Exercise 3: Running balance

Given ordered transactions of 200, -50, 100, and -30, calculate the running balance starting at zero.

Requirement: Use SUM with an explicit ROWS frame.

### Exercise 4: Moving average

Given daily values 10, 20, 30, 40, and 50, calculate a three-row moving average.

Requirement: Use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

### Exercise 5: Ranking ties

Given scores 100, 90, 90, and 80, calculate ROW_NUMBER, RANK, and DENSE_RANK.

Requirement: Explain why the three results differ.

### Exercise 6: Previous period

Given monthly revenue, show each month's revenue and the preceding month's revenue.

Requirement: Use LAG and handle the first row.

### Exercise 7: Top two per department

Return the two highest-paid employees in each department.

Requirement: Use ROW_NUMBER in a CTE and filter in the outer query.

### Exercise 8: Identify growth

Calculate the difference between the current and previous month's revenue. Explain how you would calculate percentage growth safely when the previous revenue can be zero or NULL.

For each exercise, test your query with ties, missing values, and small datasets before relying on it for production analytics.

Knowledge Check

### Question 1

What is the main difference between GROUP BY and a window function?

A. Window functions always remove duplicates.
B. GROUP BY normally collapses rows into group summaries, while window functions preserve individual result rows.
C. GROUP BY requires an index.
D. Window functions cannot calculate sums.

**Answer:** B. Window functions can calculate across related rows while retaining row-level detail.

### Question 2

What does PARTITION BY do inside OVER()?

A. Sorts the final output.
B. Divides rows into separate calculation windows.
C. Deletes duplicate records.
D. Creates a permanent table.

**Answer:** B. Each partition is calculated independently.

### Question 3

Which function assigns a unique sequential number to every row?

A. RANK
B. DENSE_RANK
C. ROW_NUMBER
D. LAG

**Answer:** C. ROW_NUMBER assigns a separate number to each row.

### Question 4

What is the difference between RANK and DENSE_RANK?

A. RANK handles only numbers.
B. DENSE_RANK removes rows.
C. RANK can leave gaps after ties, while DENSE_RANK does not.
D. They always return different values.

**Answer:** C. For scores 100, 90, 90, and 80, RANK returns 1, 2, 2, 4; DENSE_RANK returns 1, 2, 2, 3.

### Question 5

What does LAG(amount) normally return?

A. The amount from a preceding row in the ordered window.
B. The maximum amount in the table.
C. The next row's amount.
D. The total amount in the partition.

**Answer:** A. LAG accesses a preceding row's value.

### Question 6

What does ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mean?

A. Use exactly two rows in total.
B. Use the current row and up to two preceding rows.
C. Use the next two rows.
D. Use all rows in the table.

**Answer:** B. The frame can contain up to three rows, depending on the rows available.

### Question 7

Why might a running total use an explicit ROWS frame?

A. To remove NULL values from the table.
B. To define a row-by-row frame rather than relying on the default peer-sensitive frame.
C. To create a primary key.
D. To prevent all duplicate records.

**Answer:** B. An explicit frame makes the intended cumulative calculation clear.

### Question 8

Why is a CTE useful when filtering ROW_NUMBER results?

A. It makes the ranking function recursive.
B. It allows the outer query to filter the calculated window result.
C. It automatically creates an index.
D. It converts every value to text.

**Answer:** B. The outer query can filter the row number computed by the inner query.

SQL Interview Questions

### 1. What is a window function?

A window function calculates a value across related rows while preserving the individual rows in the result.

### 2. What does OVER() do?

OVER defines the window used by a window function. It can specify partitioning, ordering, and a frame.

### 3. What is the difference between GROUP BY and PARTITION BY?

GROUP BY forms groups and normally produces one output row per group. PARTITION BY divides rows for a window calculation while keeping individual rows in the result.

### 4. What is a running total?

A running total is a cumulative sum calculated in a defined order, often using SUM with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

### 5. Explain ROW_NUMBER, RANK, and DENSE_RANK.

ROW_NUMBER assigns a unique sequence to every row. RANK assigns equal ranks to ties and leaves gaps. DENSE_RANK assigns equal ranks to ties without leaving gaps.

### 6. What is the difference between LAG and LEAD?

LAG accesses a preceding row's value. LEAD accesses a following row's value according to the specified window ordering.

### 7. How do you find the top N records in each group?

Calculate ROW_NUMBER, RANK, or DENSE_RANK partitioned by the group and ordered by the desired ranking value. Filter the calculated rank in an outer query. Choose the ranking function based on how ties should be handled.

### 8. Can a window function be used in WHERE?

Not normally in the same SELECT level where the window function is calculated. Put the calculation in a CTE or subquery, then filter in the outer query.

### 9. What is a window frame?

A window frame specifies which rows around the current row participate in a frame-sensitive calculation. ROWS, RANGE, and GROUPS define frames differently.

### 10. Why is ORDER BY inside OVER() different from the final ORDER BY?

The window ORDER BY controls the order used for the calculation. The final ORDER BY controls the order in which result rows are returned.

### 11. How do NULL values affect window functions?

SUM and AVG ignore NULL inputs. LAG and LEAD may return NULL when the requested row does not exist or its expression evaluates to NULL. The exact handling depends on the function and expression.

### 12. How would you calculate month-over-month growth?

Use LAG to obtain the previous month's value, then calculate the difference or percentage change. Define behavior for missing previous values and zero denominators before calculating percentages.

### 13. Do window functions automatically improve query performance?

No. Performance depends on the data volume, partitioning, ordering, indexes, and execution plan. Inspect the plan and test against realistic data.

### 14. How would you find the highest-paid employee in each department?

Use ROW_NUMBER or RANK partitioned by department and ordered by salary descending. Use ROW_NUMBER if exactly one employee per department is needed; use RANK if all employees tied for the highest salary should be returned.

Lesson Summary

Window functions are essential for SQL analytics because they calculate across related rows without collapsing individual records.

Remember:

- OVER() defines the window.
- PARTITION BY creates independent calculation groups.
- Window ORDER BY controls the calculation order.
- Explicit ROWS frames clarify running totals and moving averages.
- ROW_NUMBER, RANK, and DENSE_RANK handle ties differently.
- LAG and LEAD compare values across ordered rows.
- Use a CTE or subquery to filter calculated window results.
- Add a deterministic tie-breaker when consistent ordering is required.
- Check NULL behavior, window frames, and execution plans.

You should now be able to explain the core syntax, calculate running totals and moving averages, rank records within groups, and compare adjacent rows using PostgreSQL.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using GROUP BY when individual rows must remain visible

Why it happens: The developer wants a group total but does not realize that GROUP BY normally collapses rows into summary records.

Incorrect Approach

sql

SELECT employee, SUM(amount) AS employee_total
FROM sales
GROUP BY employee;

Correct Solution

sql

SELECT
    sale_id,
    employee,
    amount,
    SUM(amount) OVER (
        PARTITION BY employee
    ) AS employee_total
FROM sales
ORDER BY sale_id;

Key takeaway: The window calculation displays each sale alongside its employee's total. The GROUP BY version returns a summary row for each employee.

#2 Assuming tied rows always have a stable ROW_NUMBER

Why it happens: The ordering expression does not uniquely determine the order of rows with equal values.

Incorrect Approach

sql

ROW_NUMBER() OVER (
    ORDER BY score DESC
)

Correct Solution

sql

ROW_NUMBER() OVER (
    ORDER BY score DESC, player_id
)

Key takeaway: Adding a unique tie-breaker produces a deterministic sequence when the data is unchanged. Use the appropriate unique identifier for the actual table.

#3 Filtering a window function in the same SELECT's WHERE clause

Why it happens: The developer assumes a SELECT-list alias is available to WHERE at the same query level.

Incorrect Approach

sql

SELECT
    employee_id,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS position
FROM employees
WHERE position <= 3;

Correct Solution

sql

WITH ranked AS (
    SELECT
        employee_id,
        ROW_NUMBER() OVER (
            ORDER BY salary DESC, employee_id
        ) AS position
    FROM employees
)
SELECT employee_id, position
FROM ranked
WHERE position <= 3
ORDER BY position;

Key takeaway: Calculate the window result in an inner query and filter it from the outer query.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

Window functions calculate values across a set of related rows while preserving individual result rows.
OVER() defines the window used by a window function.
PARTITION BY divides rows into independent groups without combining each group into a single row.
The ORDER BY inside OVER() controls the ordering used by the window calculation, not the final presentation order.
ROWS and RANGE define window frames and can change running totals and moving averages.
ROW_NUMBER, RANK, and DENSE_RANK handle ranking differently when values are tied.
LAG and LEAD access values from preceding and following rows.
Window functions should be tested with ties, NULL values, and realistic data volumes.

Was this lesson helpful?

Found an error? Report an issue