Window Functions

Sql tutorial · PySpark.in

9. Window Functions in SQL

Window Functions are one of the most powerful features in SQL.

They allow us to:

• Perform calculations across rows
• Compare rows with neighboring rows
• Generate rankings
• Calculate running totals
• Analyze grouped data without collapsing rows

Window functions are heavily used in:

• Analytics
• Reporting
• Dashboards
• Data Engineering
• Data Science

What Makes Window Functions Special?

Unlike GROUP BY, window functions do not combine rows into a single row.

Instead:

Window Functions perform calculations
while keeping all original rows.

GROUP BY vs Window Functions

GROUP BY

```

sql

CREATE TABLE employee (

id INTEGER PRIMARY KEY,

name TEXT,

department TEXT,

salary INTEGER

);

INSERT INTO employee (

id,

name,

department,

salary

)

VALUES

(1, 'John', 'HR', 35000),

(2, 'Emma', 'HR', 45000),

(3, 'David', 'IT', 60000),

(4, 'Alex', 'IT', 80000),

(5, 'Sophia', 'Sales', 50000);

SELECT department,
AVG(salary)
FROM employee
GROUP BY department;

```

Output

Rows are collapsed into groups.

Window Function

```

sql

SELECT name,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS avg_salary
FROM employee;

```

Output

All rows remain visible.

Syntax of Window Functions

```

hide

function_name()
OVER (
PARTITION BY column_name
ORDER BY column_name
)

```

Components of Window Functions

Example Table — employee

PARTITION BY

PARTITION BY divides rows into groups.

Window functions are then applied separately inside each group.

Example

```

sql

SELECT name,
department,
salary,
AVG(salary) OVER(
PARTITION BY department
) AS avg_salary
FROM employee;

```

Output

Visual Understanding

HR Group:
35000, 45000
Average = 40000

IT Group:
60000, 80000
Average = 70000

ORDER BY in Window Functions

ORDER BY defines the sequence of rows inside each partition.

It is important for:

• Ranking
• Running totals
• Previous/next row calculations

Ranking Functions

SQL provides special window functions for ranking rows.

1. ROW_NUMBER()

Assigns a unique row number to each row.

Example

```

sql

SELECT name,
salary,
ROW_NUMBER() OVER(
ORDER BY salary DESC
) AS row_num
FROM employee;

```

Output

Important Property

Even if salaries are equal, row numbers remain unique.

2. RANK()

Assigns rank to rows.

If two rows have the same value, they receive the same rank.

The next rank is skipped.

Example

```

sql

SELECT name,
salary,
RANK() OVER(
ORDER BY salary DESC
) AS salary_rank
FROM employee;

```

Example Output

Notice:

Rank 4 is skipped.

3. DENSE_RANK()

Similar to RANK(), but does not skip ranks.

Example

```

sql

SELECT name,
salary,
DENSE_RANK() OVER(
ORDER BY salary DESC
) AS dense_rank
FROM employee;

```

Output

Difference Between RANK and DENSE_RANK

Running Total Using SUM()

Window functions are commonly used for cumulative totals.

Example

```

sql

SELECT name,
salary,
SUM(salary) OVER(
ORDER BY salary
) AS running_total
FROM employee;

```

Output

How Running Total Works

35000
35000 + 45000 = 80000
80000 + 50000 = 130000

LAG Function

LAG() accesses the previous row value.

Useful for:

• Comparing current row with previous row
• Growth calculations
• Trend analysis

Example

```

sql

SELECT name,
salary,
LAG(salary) OVER(
ORDER BY salary
) AS previous_salary
FROM employee;

```

Output

LEAD Function

LEAD() accesses the next row value.

Example

```

sql

SELECT name,
salary,
LEAD(salary) OVER(
ORDER BY salary
) AS next_salary
FROM employee;

```

Output

FIRST_VALUE()

Returns the first value in the window.

Example

```

sql

SELECT name,
department,
salary,
FIRST_VALUE(salary) OVER(
PARTITION BY department
ORDER BY salary
) AS lowest_salary
FROM employee;

```

Output

LAST_VALUE()

Returns the last value in the window.

Example

```

sql

SELECT name,
department,
salary,
LAST_VALUE(salary) OVER(
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS highest_salary
FROM employee;

```

Output

Commonly Used Window Functions

Real-World Use Cases

Window functions are used in:

Advantages of Window Functions

• Powerful analytical capabilities
• Avoid complex subqueries
• Keep original rows intact
• Simplify reporting queries
• Improve readability

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges