How to Practice SQL Window Functions at Home

Are you looking for a way to maintain your knowledge of SQL window functions? What’s the best way to practice them at home? Here are some suggestions.

Learning something new feels great, but it’s also arguably the easiest part of gaining knowledge. You’ve probably experienced that yourself. You struggle with learning an idea, and then you finally get it! There’s the feeling you’ve learned something. And then, very soon, you practically have to start all over again with the very same concepts.

What happened? You didn’t practice – didn’t apply what you’ve learned. So you forgot everything (or almost everything). Thus, we have the second and most important stage in gaining knowledge: maintaining what you’ve learned through practice.

Practicing SQL window functions is especially important – and it can be pretty hard to do it on your own. In this article, I’ll give you suggestions on learning and practicing SQL window functions.

What Are Window Functions?

Window functions are a type of SQL function that perform operations on a set of rows. This set of rows is called a window, hence the name window functions.

Even basic SQL functions can be very helpful in analyzing data. However, SQL window functions give you new possibilities for analyzing data and creating reports with SQL. They are used for almost every operation required for reporting and business intelligence, i.e. creating ranks, performing basic mathematical operations within each data category, calculating running totals and moving averages, etc.

That’s why learning SQL window functions is essential for SQL developers, data analysts, and data scientists.

Learning the Syntax

The starting point for learning anything in SQL is learning its syntax. Window functions are the same. Probably the best way to start is to read articles, which will explain the syntax and the purpose of every part of it.

The essential part of SQL window functions’ syntax is the OVER() clause. From my own experience, I know that you really begin understanding syntax when you see how it’s used in the examples. That means you have to find practical examples and read the underlying code. Then you can dissect it and get to that aha! moment: I get it now: this is what this part of the code does!

Since I like to practice what I preach, I wrote about what SQL window functions are and the syntax of window functions. Even when I’m writing about window functions, I’m reading about them, too. And you’ll probably read more articles every now and then. There are plenty available all over the internet.

Now, syntax is pretty easy to forget, especially if you don’t use window functions very often or you’re learning them for the first time. One of the best ways to refresh your memory is to have a cheat sheet by your side.

Practice Writing SQL Window Function Queries

This is probably the most challenging part. It requires a level of discipline and willpower. And you’ll have to create a certain kind of routine. It’s quite easy to read something, but it’s also easy to forget what you read – unless you put it into practice.

By writing your own queries, you get to do a vital part of the learning process: making mistakes. I’d say this is the quickest way to learn to write good queries. You’ll regularly write code that (for some reason) won’t return the expected result or doesn't work at all. When you try to solve your own mistakes (and succeed !), knowledge starts to form. You’re building something I call a “dictionary of mistakes”, which allows you to make fewer mistakes in the future. It also allows you to find errors more quickly and introduces you to error messages returned by the database. This will help you use error messages to locate and fix problems.

You’ll also discover how to properly structure your queries. This is more than just sticking to the SQL window function syntax. There are usually different ways to achieve the same result, so you have to find what works for you and what you like. Everybody writes their queries differently, and practicing helps you find your way. This will even affect how your code looks. Only with practice will you get to know how you like your code. You’ll learn how to write queries in a way that you can easily read, which makes finding potential mistakes easier.

You Can Practice Window Functions On Your Own

There are some challenges to practising window functions on your own. One is getting a good data set. How can you do this?

Random data generators

If you Google “random data generators” you’ll see there are plenty available. They can be quite useful, since they allow you to define column names, types of data, the number of rows, etc. Using them is not difficult and is a way to very quickly generate large amounts of data that you can use to practice SQL.

Create your own datasets

You can also create your own data. Here you have the absolute freedom to decide how much data you want, how to name columns, whether you want duplicates or not, and what the row values will be. It’s also fun because you can put silly names in your tables. But this freedom means it can take quite a lot of time to create a sufficient amount of data. You’ll have to handle everything by yourself: naming the tables, defining the columns and data types, and creating data for every row and every column. And then, you have to plan for JOINs. If you really want to create your own data, you might want to refresh your knowledge of SQL JOINs.

I’ve used both approaches when I was creating suitable examples for my previous articles. However, the main disadvantage of both approaches is that you have to have a certain level of knowledge about window functions to create suitable data. If you don’t have it, it’s easy to create data that is not big enough or simply not suited for analytic functions. If you’ve just started to learn about window functions, it’s not realistic to expect that you’ll immediately start creating your own datasets.

Whether you’re using data generators or you’re creating your own data, you also have to come up with your own examples that will cover all important aspects of window functions. This is quite difficult if you don’t know window functions well. All those difficulties can easily draw you away from practicing SQL. They may even make you give up before you really get started.

Structure Your Learning Path

You can take online courses to learn SQL window functions, and some of them are very good. There are some awesome instructors that will give you a lot of knowledge for free (or for not much money). However, one of the drawbacks is you usually copy what somebody else is doing on the screen. Only occasionally do you get the chance to solve some kind of test or to practice on your own. You also need to install the software the instructor is using. If you have an older computer, this can be a real pain. It might be impossible.

Interactive courses are a good way to avoid all that. LearnSQL.com’s Window Functions interactive course makes it a lot easier to build your coding skills. It provides you with a structured way to learn, giving you enough theory to understand the examples that will follow. There’s no need to install anything; if you have an internet connection, you can practice from anywhere. There are also data sets ready, designed specifically to show you how window functions work. You run example queries on a real database. Of course, you’re also required to write your own queries after every example. It allows you to practice what you’ve learned, build your ‘muscle memory’, make mistakes, and learn from them. You’re also free to use that database to write SQL queries and practice something other than window functions.

In the April Course of the Month interview, the course creator talks in detail about what you’ll learn and why it could be useful to know SQL window functions.

Additional SQL Learning Tips

I’ve always learned best on my own. It’s not to say that I don’t need other people’s guidance and expertise, or a formal and theoretical education. No, I need those a lot. Colleagues, authors, and their expert guidance can always help. However, try not to depend only on other people. Learning from other people can only lead to having more information – but information doesn’t necessarily mean knowledge.

Information becomes knowledge when you’re on your own with it and you’re trying to solve a certain problem. That’s what I mean by learning on your own. So if you can, try to apply what you’ve learned from articles and courses on the job. Find a way to practice analytic functions in real life – again, ideally as part of your job. You’ll practice on imperfect data, sometimes under deadlines, and you’ll be trying to find a way around some real problems. There’s no faster way to gain knowledge than that.

This type of practice will put your creativity to the test. It’ll force you to make something work in practice and not just in theory. Every mistake you make (and you’ll make them) will stick in your brain. Whenever you mess up something, you’ll make sure you don’t make that same mistake again. You’ll make a different slipup, but that’s how you build your “dictionary of mistakes”. This will allow you to avoid many errors and quickly discover the ones you do make.

Another tip – I mention it only half-jokingly – is to write articles about SQL window functions. From personal experience, I can tell you that this is a great way to improve your knowledge. It should not be underestimated. Yes, be like me!

On a more serious note, this idea can be expressed in another way. If you have the chance, try to explain to someone what window functions are and how to use them. If a colleague asks you something, try to share your knowledge. When you teach other people, you learn at the same time. And you’ll find gaps in your own knowledge or maybe discover that you still haven’t completely grasped certain concepts. Which is completely fine! Only when you become aware that your knowledge is imperfect can you get closer to perfecting it.

Yes, You Can Learn SQL Window Functions!

We’ve discussed the basic steps in learning SQL window functions. You need both theoretical and practical knowledge to do so, and you need to understand how they interact. Hands-on practice is extremely important, so I’ve shown you some obstacles that await you and how to avoid them. Practicing window functions online is one of the more workable solutions.

If you have any questions or comments, let me know in the comments section!