Window Functions in SQL: See the Whole Picture Without Losing the Details

Window Functions

• A window function in SQL performs a calculation across a set of rows related to the current row, without changing the number of rows in the result set.

•   We can get data without losing the level of detail of the row.

• Aggregate functions are if you are giving a N number of rows as input it is able to give you One Output.

• Window functions are if you are giving N number of rows as input it is able to give you N number of outputs.

• Whenever using window functions for sure you need to use a clause called OVER.

            The purpose of OVER is to apply for every particular row.

Window Functions	Aggregate Functions
Perform calculations over a specified range of rows, but don't collapse rows into one result.	Perform calculations and return a single result by grouping rows.
Results are shown for each row in the specified range.	Results are shown as a single row per group.
Useful for calculations that need a row by row result within groups(eg: Running total, Ranking)	Useful for summarizing data(eg: Total sales per category)
Examples: Row_Number(),Rank(),Sum(),Over()	Examples: Sum(),Avg(),Count()

Syntax

SELECT 

  column_name,

  window_function(column_name) OVER (

    PARTITION BY column_name

    ORDER BY column_name

  ) AS result_column

FROM table_name;

• function_name: The aggregate or ranking function (e.g., SUM, AVG, ROW_NUMBER, etc.).

• PARTITION BY: Divides the result set into partitions to apply the function separately to each partition.

• ORDER BY: Specifies the order of rows within each partition.

• frame_clause: Defines a subset of rows for the calculation (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

  
  

Aggregate Functions

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:

• SUM(): Sums values within a window.

• AVG(): Calculates the average value within a window.

• COUNT(): Counts the rows within a window.

• MAX(): Returns the maximum value in the window.

• MIN(): Returns the minimum value in the window.

  
  

Example: Sales Data Analysis

Imagine a table sales with the following structure

sale_id	region	salesperson	amount
1	East	Alice	500
2	East	Bob	300
3	West	Charlie	700
4	West	Alice	200
5	East	Bob	400

Query: Calculate Total and Average Sales per Region

SELECT 

    region,

    salesperson,

    amount,

    SUM(amount) OVER (PARTITION BY region) AS total_sales_per_region,

    AVG(amount) OVER (PARTITION BY region) AS avg_sales_per_region

FROM sales;

Result

region	salesperson	amount	total_sales_per_region	avg_sales_per_region
East	Alice	500	1200	400.00
East	Bob	300	1200	400.00
East	Bob	400	1200	400.00
West	Charlie	700	900	450.00
West	Alice	200	900	450.00

Explanation

• SUM(amount) OVER (PARTITION BY region)

  • Calculates the total sales for each region (East and West) without grouping the rows.

• AVG(amount) OVER (PARTITION BY region):

  • Calculates the average sales for each region.

    
    

1. ROW_NUMBER()

Assigns a unique sequential number to each row, starting from 1, without skipping numbers, even if there are ties.

Example:

SELECT Name, Score, ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNumber

FROM Students;

Input

Name	Score
Alice	95
Bob	90
Carol	90
Dave	85

Output

Name	Score	RowNumber
Alice	95	1
Bob	90	2
Carol	90	3
Dave	85	4

2.RANK()

Assigns a rank to rows, but skips numbers if there are ties.

Example:

SELECT Name, Score, RANK() OVER (ORDER BY Score DESC) AS Rank

FROM Students;

Output

Name	Score	Rank
Alice	95	1
Bob	90	2
Carol	90	2
Dave	85	4

3. DENSE_RANK()

Similar to RANK(), but does not skip numbers when there are ties.

Example:

SELECT Name, Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank

FROM Students;

Output

Name	Score	DenseRank
Alice	95	1
Bob	90	2
Carol	90	2
Dave	85	3

 4.PERCENT_RANK()

1.Calculates the relative rank of a row as a percentage of the total rows, ranging from 0 to 

Example

SELECT Name, Score, PERCENT_RANK() OVER (ORDER BY Score DESC) AS PercentRank

FROM Students;

Output

Name	Score	PercentRank
Alice	95	0.000
Bob	90	0.333
Carol	90	0.333
Dave	85	1.000

5.NTILE(n)

Divides rows into n equal groups and assigns a group number to each row.

Example:

SELECT Name, Score, NTILE(2) OVER (ORDER BY Score DESC) AS NTile

FROM Students;

Output

Name	Score	NTile
Alice	95	1
Bob	90	1
Carol	90	2
Dave	85	2

1. LAG()

The LAG() function retrieves the value of a column from a previous row within the same result set.

Example

SELECT 

    employee_id,

    salary,

    LAG(salary) OVER (ORDER BY employee_id) AS previous_salary

FROM employees;

Output

employee_id	salary	previous_salary
1	50000	NULL
2	55000	50000
3	60000	55000

2. LEAD()

The LEAD() function retrieves the value of a column from the next row within the same result set.

Example:

SELECT 

    employee_id,

    salary,

    LEAD(salary) OVER (ORDER BY employee_id) AS next_salary

FROM employees;

Output

employee_id	salary	next_salary
1	50000	55000
2	55000	60000
3	60000	NULL

3. FIRST_VALUE()

The FIRST_VALUE() function retrieves the first value in a window or partition.

Example

SELECT 

    department_id,

    employee_id,

    salary,

    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS lowest_salary

FROM employees;

Output

department_id	employee_id	salary	lowest_salary
1	101	40000	40000
1	102	45000	40000
2	201	50000	50000

4. LAST_VALUE()

The LAST_VALUE() function retrieves the last value in a window or partition.

Example:

SELECT 

    department_id,

    employee_id,

    salary,

    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_salary

FROM employees;

Output

department_id	employee_id	salary	highest_salary
1	101	40000	45000
1	102	45000	45000
2	201	50000	50000

Conclusion

SQL window functions are commonly used for tasks such as aggregations, rankings, and running totals, enabling data analysts to perform complex analytical tasks efficiently.