The Talent500 Blog

Window Functions in SQL


SQL, or Structured Query Language, is used for storing and manipulating information in relational databases. SQL provides us with a number of commands to perform various operations on a database. Window functions are one such command that allows advanced queries to return more streamlined results. In this tutorial, you will get a brief overview of some of the most common SQL window functions that make your work easier as a developer.

What are Window Functions in SQL?

To understand window functions better, we will first learn about databases. 

A SQL database is a relational database, which means the data points are linked across one or more tables. A relational database typically contains multiple tables, each with its own unique set of rows and columns holding information in different formats, such as string, integer, date, and time values.

We can retrieve such data using simple SQL queries. 

Window Functions

Window functions are used to perform complex calculations across a set of rows in a defined window. This window is created using an OVER() clause in SQL, which returns a single value for each row in the query.

You might assume they function similarly to aggregate functions in SQL, but window functions preserve the individuality (identity) of rows, unlike aggregate functions.

Window functions can use aggregate functions using the OVER() clause to compute a running calculation for each row.

Based on the type of calculations, window functions can be categorized into three main types:

  1. Aggregate window functions
  2. Ranking window functions
  3. Value window functions


Window Functions in SQL 1

Why do we use window functions in SQL? 

Although window functions require a sheer amount of practice to get used to, they are quite useful for dealing with huge databases in SQL by executing queries within a specified window frame. This reduces the loading time and makes SQL queries efficient. 

With window functions, you can perform tasks like finding the running averages of a filtered query, which would otherwise require complex operations like self-joins and subqueries.

Another advantage of using window functions over traditional SQL functions is that it formats longer queries and enhances readability.  

Types of SQL Window Functions

Before moving forward, let us begin by creating a basic table to understand window functions better. Open a DBMS (database management) tool of your choice and run this simple query to create a Students table. For this tutorial, I’ll be using MySQL Workbench:

  name VARCHAR(100) NOT NULL— Combine first and last name
  city VARCHAR(50),
  country VARCHAR(50),
  marks DECIMAL(5,2)

INSERT INTO Students (name, city, country, marks)
VALUES (‘Alice Miller, ‘New York‘, ‘USA‘, 95.0),
      (‘Charlie Brown‘, ‘Chicago‘, ‘USA‘, 98.0),
      (‘Bart Simpson‘, ‘El Paso‘, ‘USA‘, 89.0),

       (‘Mulan’, ‘Charlotte’, ‘USA‘, 94.0),
      (‘Jack Wilson‘, ‘Columbus‘, ‘USA‘, 80.0),

       — add more of your choice


Return the Students table:

SELECT * FROM Students


Window Functions in SQL 2

Consider adding some additional data for a comprehensive analysis of your queries.

Aggregate Functions

Aggregate functions are most frequently used to perform basic calculations, such as computing averages, sums, minimum and maximum values.


In standard SQL, aggregate functions are generally used with GROUP BY statements. 

When we use window functions, we can replace the GROUP BY statement with the OVER clause. For example:


— using GROUP BY statement
SELECT city, COUNT(*) AS total_students
FROM Students
GROUP BY city;

— using OVER clause
SELECT city, COUNT(*) OVER (PARTITION BY city) AS total_students
FROM Students;


Both queries return the total number of students in each city. However, the GROUP BY statement first groups the data by city and then computes the required query within each group. 

On the other hand, the OVER clause creates a partition by city within the specified window, and calculations are then performed across this partition.


— with GROUP BY statement
SELECT, s1.marks, AVG(s2.marks) AS moving_average
FROM Students s1
INNER JOIN Students s2 ON >=
GROUP BY, s1.marks

— with OVER clause
SELECT name, marks, AVG(marks) OVER (ORDER BY name) AS moving_average
FROM Students;

Both queries calculate the moving average of marks for each student in the Students table. But as you can see, the OVER clause improves the readability of the code. In contrast, self-joins become less efficient to use, particularly with larger databases.


Similarly, the SUM() function calculates the total of a specified column in a database.

SELECT name, marks, SUM(marks) OVER (ORDER BY name) AS running_total
FROM Students;

MIN() & MAX()

The MIN() and MAX() functions are used to calculate the minimum and maximum values of a column, respectively:

— with GROUP BY()
SELECT city, MIN(marks) AS lowest_marks, MAX(marks) AS highest_marks
FROM Students
GROUP BY city;

This query returns the lowest and highest marks scored by students in each city.

— with OVER()
SELECT name, marks, MIN(marks) OVER (ORDER BY name ROWS BETWEEN PRECEDING 2 FOLLOWING 0) AS previous_min,
      MAX(marks) OVER (ORDER BY name ROWS BETWEEN PRECEDING 2 FOLLOWING 0) AS previous_max
FROM Students;

This query produces the same output, but it creates an output window including the current student and the two previous students. This can help us find the previous minimum and maximum marks as well. 

As you can see, window functions can help you analyze student performances by comparing marks with those of their immediate competitors.

Ranking Functions


When a window is defined within the OVER() clause, ROW_NUMBER() assigns a distinct sequential number to each row:

  SELECT name, marks, ROW_NUMBER() OVER (ORDER BY name) AS row_num
  FROM Students
) AS ranked_students
WHERE row_num <= 5;

This query will assign a unique number to each student, ordered by their names in ascending order. 

Window Functions in SQL 3


SELECT name, marks, RANK() OVER (ORDER BY marks DESC) AS ‘rank’
  SELECT name, marks
  FROM Students
) AS ranked_students;

Window Functions in SQL 4

This query will return the top 5 students who scored the highest marks from the Students table. 

Rows with the same value will be assigned the same rank, and subsequent rows will get the next rank with gaps in the ordering.


SELECT name, marks, DENSE_RANK() OVER (ORDER BY marks DESC) AS ‘dense_rank’
FROM Students
WHERE ‘dense_rank’ <= 5;

Window Functions in SQL 5

Similar to the previous query, this query will also return the top 5 students scoring the highest marks, but using the DENSE_RANK() function. 

DENSE_RANK() will assign the same rank to identical values, but keep in mind that unlike RANK(), it does not include gaps in between. This means that the next row will be assigned the consecutive ranking in order.


PERCENT_RANK() returns 0 to 1 percentile ranks for a row within a specified window.

SELECT name, marks, PERCENT_RANK() OVER (ORDER BY marks DESC) AS percentile_rank
FROM Students;

Window Functions in SQL 6

The above result indicates that Mulan scored higher than 50% of the class. It can also be interpreted that Mulan scored lower than 51% of the class. Similarly, Bart scored higher than 75% but lower than 76% of the students.


The NTILE() function allows you to divide the records of an output table into approximately equal tiles or groups. NTILE(num) takes a num integer as input, where we specify the desired number of groups.  

This output table is the result of the ordered partitioning of a specific table in a database.

For example, if our output table contains 20 records and we use NTILE(5), it will divide the records into 5 groups, with each group containing 4 records.

SELECT name, marks, NTILE(3) OVER (ORDER BY marks DESC) AS student_performance
FROM Students;

Window Functions in SQL 7

This query divides the students into 3 groups based on their performance, where a student_performance of 1 corresponds to the top performers with the highest marks.

Value Functions


The LAG() function is used to retrieve a value from a previous row within a partition, i.e., the succeeding value that comes before the current row in the ordered set of rows.

    LAG(name) OVER (PARTITION BY city ORDER BY marks) AS prev_name,
    LAG(marks) OVER (PARTITION BY city ORDER BY marks) AS prev_marks

This SQL query returns the student who has the second highest marks and scores lower than the current student within the same city.


The LEAD() function is used to retrieve a value from a subsequent row within a partition, i.e., the preceding value or next row in the ordered set of rows.

    LEAD(name) OVER (PARTITION BY city ORDER BY marks) AS next_name,
    LEAD(marks) OVER (PARTITION BY city ORDER BY marks) AS next_marks

 This query finds the student with the next highest marks who scores higher than the current student within the same city.

Window Functions in SQL 8


The FIRST_VALUE() function retrieves the first row within an ordered window frame, while the LAST_VALUE() function retrieves the last row within an ordered window frame.

SELECT name, city, marks,
  FIRST_VALUE(marks) OVER (ORDER BY marks DESC) AS highest_marks,
  LAST_VALUE(marks) OVER (ORDER BY marks ASC) AS lowest_marks
FROM Students;

This query will return highest and lowest marks for each row. But if you think about it, we can achieve the same result using the MIN() and MAX() functions:

SELECT name, city, marks,
  MIN(marks) OVER () AS lowest_marks,
  MAX(marks) OVER () AS highest_marks
FROM Students;

This is efficient and faster compared to the above query. Depending on your use case and the size of the dataset, it is important to understand whether using SQL window functions would be beneficial or not.


SQL window functions offer an efficient way of dealing with huge databases to make the queries more readable, faster and promote clean code. 

While window functions are a great choice when writing complex SQL queries, we should always consider factors like database size, working environment, and specific use cases when choosing the optimal approach to writing queries.

For instance, we can use simple queries like MIN() and MAX() to perform simple calculations in SQL queries.

It takes an ample amount of practice to master window functions in SQL, but once you get your command on it, it can greatly improve your productivity and save time when working with databases.


Shreya Purohit

Shreya Purohit

As a data wizard and technical writer, I demystify complex concepts of data science and data analytics into bite-sized nuggets that are easy for anyone to understand.

Add comment