Advanced SQL for analysts: window functions are not your enemies, P.2

The first part can be found here.

So now we learned how to use window functions: aggregate_func(expression) over(partition by group_column)

But window functions can be used not only with aggregate functions but also with some new Ranking Window Functions and Value Window Functions.

Ranking Window Functions

RANK(), DENSE_RANK(), ROW_NUMBER()

For this kind of functions ordering is required: if we want to have the row number we also need to know, how to order the rows from first to last. Same thing with rank and dense rank.

Let’s take the students table from the previous lesson:

students table

Let’s give every student rank per subject and overall rank:

SELECT name, faculty, score,
    RANK() OVER(PARTITION BY faculty ORDER BY score DESC) 
        AS faculty_rank,
    RANK() OVER(ORDER BY score DESC) 
        AS overall_rank
FROM students s

Resulting table

Note, that:

1. (red) We have overall rank, where James and Isabella acquired same rank as they got same scores of 87. The next student, Iker, has a rank equals 7. If you don’t want to have such gaps, use dense_rank() instead (in which case Iker would have acquried rank of 6).
2. (yellow) In order to get the rank per faculties, we specified the grouping column in our query — partition by faculty.

Value Window Functions

LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Imagine that we want to know the difference between the scores of neighbour students. We can use LAG() and LEAD() in this case, which should show us the previous (LAG) and following student’s score:

SELECT name,  score,
    LAG(score) OVER(ORDER BY score DESC) AS previous_score,
    score - LAG(score) OVER(ORDER BY score DESC) AS difference
FROM students s
ORDER BY score DESC

Resulting table

As seen, with the window functions we are able to make much more deep analysis without using special tools like Python Pandas. Of course, in this article we just scracthed the surface of the WF. But I hope you, the reader, will start using WF today and will get confident enough using them on a daily basis.