Course of the Month: Window Functions

Spring has come. It is nicer outside. It's a great time to learn something new. Which course should you choose? In April, it's worth it to bet on SQL window functions. What are they, what are they used for, and why are they worth knowing?

I asked Agnieszka Kozubek-Krycuń, Chief Content Officer at Vertabelo, these questions. She has a PhD in mathematics and over 10 years’ experience teaching mathematics and computer science at the University of Warsaw. She knows SQL better than just about anyone and is probably the best person to talk about window functions.

Let's start from the beginning. How would you describe window functions to someone with no IT experience?

For someone with no IT experience, I’d start by explaining SQL and databases. A database is a computer program that can store a large amount of data and can process data efficiently. SQL is the language you use to talk to databases. SQL allows you to retrieve data from a database and do a lot of different computations on that data. Some people say that SQL is “Excel on steroids.”

There is what I call a “core SQL”: clauses such as SELECT, WHERE, GROUP BY, HAVING, and ORDER BY. This is more or less SQL as it was defined by the SQL-92 standard. Every database course and every SQL tutorial teaches core SQL or some part of it.

Window functions are a very powerful extension of core SQL. You don’t need to know window functions to know SQL, but you should definitely know what window functions are if you claim to be a SQL expert.

Let’s talk more about window functions. What are they? When are they useful?

Window functions allow you to compute quite sophisticated statistics in one simple query. The most common use cases for window functions are creating rankings, performing time series analysis, and computing running totals, averages, etc.

Rankings are great for comparing data across various factors. For example, you can use rankings to find the top 100 salespeople in your company, your 10 best-selling products, or the 30 employees with the highest salary.

When it comes to time series analysis, we see their use during the COVID-19 pandemic. Each day, people are looking at the number of people diagnosed and the number who’ve recovered or died. We compare these figures with the numbers from the previous day. Is the number of diagnoses increasing? Has it started to decrease? Is the average over the last three days higher in one country or another? You can answer all these questions with window functions. In more peaceful times, you’ll be using window functions to analyze trends in daily website visits, daily or monthly sales, or currency exchange rates.

Window functions are useful also for computing running totals and running averages. For example, you may be interested in cumulative daily sales. Let’s say that between March 1 and March 25 we sold 1,345 items, while between February 1 and February 25, we sold 1,200 items. We can see the increase in March’s sales. Running totals and running averages are mostly used in the financial world, but they are used in sales or marketing as well.

What types of window functions are there?

Window functions can be divided into multiple groups. First there are the aggregate functions. These functions compute statistics, such as the sum (total), count, average, maximum, and minimum values in the group. If we use window functions, we can apply these functions to a more specific group of rows than we can with a regular GROUP BY.

Next we have ranking functions, which number rows according to a given criteria. There are also positional functions, such as LEAD, LAG, FIRST_VALUE, and LAST_VALUE. They find values for rows before or after the current row.

Do window functions resemble any other SQL functions?

We can say that window functions are like the GROUP BY clause, but unlike GROUP BY they do not collapse rows.

Like GROUP BY, window functions can compute various aggregates (statistics): sums, averages, counts. Unlike GROUP BY, the details of individual rows are still available when you use window functions. For example, you can compute the average in a group of rows (like the average sales in each city) and compare it to details of individual rows (sales in a particular city).

Also, window functions operate on a “window frame”, a set of rows defined with respect to the current row (e.g. the average over the three days preceding the current row). Window functions allow for more fine-grained and sophisticated statistics than GROUP BY.

Since these functions are so important and useful, they must be in every SQL course.

Window functions are usually not part of the regular SQL course program. However, more students are looking for good places to learn them. Of course, there are tutorials on window functions. We can even find them on YouTube – but these are either lectures, which are difficult for the ordinary user to process, or simply boring. As far as I know, our window functions course is the only interactive and complete course on window functions.

Do popular database engines support window functions?

Yes. All popular database engines, such as Oracle, Microsoft SQL Server, and PostgreSQL, support window functions. MySQL supports window functions on version 8.0 or later. Many Big Data technologies, such as Presto and Hive, also support window functions.

Window functions are a rather advanced SQL technique. Can I take the LearnSQL.com Window Functions course without passing, say, the SQL Basics course first?

I’d recommend the “Window Functions” course for someone who has a good grasp of SQL. First, I’d say learn fundamental SQL, maybe with the help of our SQL Basics course. Then get some experience working with SQL – at work, as a hobby project, or with the help of our SQL Practice Set. Then learn window functions.

Do I have to install something on my computer to start the course?

All you need is a web browser and an internet connection. It's the same with all our other SQL courses. You write queries in our online console and our platform verifies the code and tells you if you’re correct. This greatly simplifies learning and solving the exercises.

Who should take this course?

I’d say this course is geared primarily toward those dealing with data analysis. Every analyst should know window functions. Do you work with time series data, financial data, sales data, marketing data, or stock trading data? This is a course for you. Learn how to work faster, easier, and more efficiently.

Of course, you can analyze your data in SQL without window functions. But you'll have to write queries that are much more complicated, much less readable, and usually slower. Getting to know these functions simply pays off.

Can you give a specific example where window functions worked in favor of the company?

When the LearnSQL.com team prepared custom SQL courses for Uber, we learned that people who work in various operational roles benefitted by knowing window functions. They often need to make decisions based on trends over the last days, weeks, or months and window functions are an essential tool to analyse trends in SQL.

Does the course contain enough exercises to help me practice new skills?

The course contains over 200 exercises, most of them interactive. You get a problem and solve it by writing a proper SQL query. We try very hard to make all exercises practical and make them resemble real problems. There is some reading when we explain the theory, but the core of the course is practical, hands-on coding.

Is the window function course part of a larger track or can it be done as a separate course?

Window Functions course can be done as a separate course. It is also part of two tracks, SQL From A to Z (our complete SQL track for total beginners who want to learn all of modern SQL) and Advanced SQL (for people who want to get into the advanced features of SQL). Of course, the Advanced SQL track presupposes you have some SQL knowledge already.

At the end of previous LearnSQL.com courses, I received a certificate that I could post on my LinkedIn profile. Will I get one for this course?

Yes, you’ll receive a certificate after you complete all the course exercises. We encourage our users to attach certificates to their resumes and LinkedIn profiles. It is also worth showing off your new skill in social media. Who knows – maybe a future boss will see your post and offer you a better job!

You are Chief Content Officer at Vertabelo. This means that you either co-authored or supervised the creation of all courses on the LearnSQL.com platform. Do you remember your work on this course?

Yes! Window Functions course is one of my favourite courses at LearnSQL.com. Preparing it was a lot of fun. Working on this course forced me to learn window functions much better than I knew them before. In fact, teaching anything means diving much deeper into the subject than you’d normally do. I call it “learning by teaching”. You have to grasp the totality of the subject, and then structure your material so that it is suitable for students. With the Window Functions course, it was even more challenging: this course is not for beginners, but for people who are already knowledgeable about SQL.

So you already know what window functions are and why they are worth learning. Everyone wants to work better and more efficiently. Don't wait until your competition overtakes you. Let April become the month you learned window functions.