Advanced SQL Concepts: Window Functions - OVER()

When we talk about advanced SQL, most people get confused due to the lots of nuances. Advanced SQL can mean stored procedures, indexes, and temporary tables for a SQL Developer but on the other hand, a Data Analyst can use Advanced SQL by dealing with User Defined Functions (UDF), Common Table Expressions (CTEs), and so on.

In this post, I am going to introduce some general advanced SQL concepts such as window functions which allow us to use complex aggregations.

What is a window function?

The window function is a function that performs calculations across a set of table rows. The rows are somehow related to the current row.

For example, you can find some values by looking at the current row to calculate the previous or next row.

Window functions and Common Table Expressions(CTEs) are so versatile to use SQL at best.

Some Types of window functions

• OVER()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE()
• NTH_VALUE()
• LAG() and LEAD()

A demonstration of the common functions in SQL.

Window functions make complex aggregations much simpler.