MySQL “Window Functions” is one of the important parts that many candidates skip and don’t prepare while appearing for interviews. It is a relatively simple topic if you understand it with simple explanations and examples.
In this article, we will see some important and commonly asked interview questions on the window functions topic. So, let’s get started!
MySQL Window Functions Interview Questions to Know
This guide will start with the basic questions and then some intermediate followed by the advanced questions on the window function topic.
1. What is Window Function? Give Examples of it.
Window Functions are the functions that work on the set of records which is called a “window frame” or just a “window”.
Window functions are very similar to aggregate functions. However, instead of calculating the scaler value for the set of rows, the window functions give the result for each row which means the window functions do not collapse rows and keep the details of each row.
All aggregate functions are window functions. Apart from aggregate functions, the following are some widely used window functions- RANK(), ROW_NUMBER(), DENSE_RANK(), LEAD(), LAG() etc. You can see the list of all window functions on the official MySQL dev blog.
2. What is the OVER clause?
The OVER clause is used to define the window frame and must be present with the window functions such as RANK(), DENSE_RANK() etc. The OVER clause may contain the following sub-clauses- PARTITION BY, ORDER BY and the range.
For example-
Consider the table of the following data-
Here, we will write a query to display the department-wise ranking of students. Therefore, we will need to create the partition by the class column (the window) and sort the rankings by the marks.
SELECT id, name, class, marks,
RANK() OVER(PARTITION BY CLASS ORDER BY MARKS DESC) AS studs_rank
FROM marks;Code language: SQL (Structured Query Language) (sql)
3. What is the difference between Window Functions and Aggregate Functions?
Aggregate functions group together the rows and results into a single row. This means, multiple rows are collapsed to form a single row and the result is not for each individual row.
On the other hand, window functions keep the information of each row along with the result. This means, rows are not collapsed and the produced result is for each individual row. You will see the data from the original table along with the new column in the result, unlike the aggregate functions.
4. Explain the RANK() Function.
The rank function assigns the rank to the current row within its partition, with gaps. If two rows in the same partition have the same values, then the same ranks are assigned to both rows. However, the next rows in the same partition will not get the consecutive rank.
For example, if there are four rows present in the window. The first two rows have the same values. Then both rows will get the rank 1. Whereas, the third and fourth rows will be assigned with the 3 and 4.
Let’s see it by taking an example. Consider the table from the second example.
SELECT id, name, class, marks,
RANK() OVER(ORDER BY marks) as 'rank'
FROM marks;Code language: SQL (Structured Query Language) (sql)Here, we have not used the partition by clause. Therefore, the whole table will act as a single partition. However, we have used the ORDER BY clause to sort the rows. Therefore, if two rows will have the same value, both will get the same ranking.
As you can see, the result will not consist of the consecutive ranking. You can read a complete tutorial on the RANK() function here.
5. Explain the DENSE_RANK() function.
The DENSE_RANK() function is somewhat similar to the RANK() function, except the one difference. The DENSE_RANK() function assigns the consecutive rankings to each row, even if there are multiple rows present having the same values in the same partition.
Let’s see it by the example. Consider the table from the second example
SELECT id, name, class, marks,
DENSE_RANK() OVER(ORDER BY marks) as 'rank'
FROM marks;Code language: SQL (Structured Query Language) (sql)
As you can see, the dense_rank function assigns the consecutive rankings. You can read a complete tutorial on the RANK() function here.
6. What is the difference between ROW_NUMBER and RANK functions?
The RANK() function assigns the same ranks to the rows having the same value within the partition. However, it will not assign a consecutive ranking. For example, if there are four rows present in the window. The first two rows have the same values. Then both rows will get the rank 1. Whereas, the third and fourth rows will be assigned with the 3 and 4.
On the other hand, no matter how many rows have similar values, the ROW_NUMBER function will not assign the same ranks to more than one row. It simply returns the number of current rows within the partition.
Let’s see it by the example where we will use the RANK(), DENSE_RANK() and ROW_NUMBER() functions together in one query.
SELECT id, name, class, marks,
RANK() OVER(ORDER BY marks) as 'rank',
DENSE_RANK() OVER(ORDER BY marks) as 'denseRank',
ROW_NUMBER() OVER(ORDER BY marks) as 'rowNumber'
FROM marks;Code language: SQL (Structured Query Language) (sql)
Conclusion
These are some of the common and most asked questions on the “window functions” topic. Make sure you read and get your hands dirty by querying all functions of the window functions including aggregate functions.