8 Best SQL Window Function Articles

Do you need a starting point for learning SQL window functions? Not sure which articles provide a solid foundation? We dove into the internet rabbit hole and found the eight best articles dealing with window and analytic functions.

There are plenty of articles dealing with SQL window functions. Type those words into Google and you’ll find a huge number of articles returned.

Reading articles is a good start if you want to learn something about window or analytic functions in SQL – provided, of course, that the articles you choose are trustworthy and up to date. We sometimes forget that just because something is on the internet doesn’t mean it’s true. So it’s always possible that you’ll find window function articles that’ll give you wrong information.

Once you’ve read a few good articles, it’ll be easier for you to recognize the not-so-good ones. That’s why I’ve prepared this review of eight articles on SQL window functions. These articles will give you a detailed explanation of window functions; they are well written and provide in-depth information.

The articles in this post can be your starting point for learning the window functions – and the starting point for finding other good quality articles. There are many more out there if you know what to look for!

Almost every good article about SQL window functions starts off by defining what window functions are. The same goes for this article!

What Are SQL Window Functions?

SQL window functions perform calculations across a set of rows in a table. The name comes from the fact that the set of rows is called a ‘window’ or ‘window frame’.

Window functions are used for creating complex aggregations, reports, and analyses. You can use them to rank rows, calculate running totals and moving averages, and perform other data analysis operations.

This is just a teaser for everything you’ll find in the articles listed below. More detailed explanations follow in those articles; there’s no point in doing all that here. What’s the point in recommending other articles if I write everything in this one?

1. SQL for the 21st Century: Analytic / Window Functions

Author: Pavel Kolesnikov

Link: https://www.gooddata.com/blog/sql-21st-century-analytic-window-functions

Description:

I found this article on the GoodData website. They’re a computer software company in the business intelligence (BI) and Big Data analytics space. In this blog post, the author talks about how people still don’t use the SQL window functions that often. Instead, they get tangled in writing unnecessarily complex SQL queries. The author first introduces you to three types of functions: single-row functions, aggregate functions, and window functions. They then proceed to briefly explain the first two and compare them with SQL window functions.

Next, the author focuses on the value (or analytic) window functions, such as LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE(). They also explain ranking window functions like ROW_NUMBER(), RANK(), and RANK_DENSE(). Finally, the author shows you how to calculate running totals and year-to-date totals and how to use window functions with the GROUP BY clause.

Why it’s useful: 

This article gradually leads you through an overview of general window functions concepts. It provides graphical explanations of the data set and what certain window functions and SQL queries return as a result. Along with that, it gives you the context of the window functions within SQL functions as a whole.

2. Learn Basic Data Analysis with SQL Window Functions

Author: Haroon Ashraf

Link: https://codingsight.com/learn-basic-data-analysis-with-sql-window-functions/

Description:

The article is published on CodingSight, a community platform for sharing knowledge mainly about SQL Server. This article focuses on explaining the window and the OVER() clause in the window functions as well as the syntax in general. It’s written in very simple language and is intended for beginners. It also shows you some basic data analysis using the aggregate window functions SUM(), COUNT(), MIN(), MAX(), and AVG().

Why it’s useful: 

This article is helpful because of its simplicity. Uncomplicated explanations and definitions are paired with detailed descriptions. Examples are straightforward. It’s ideal for beginners, especially if they are Microsoft SQL Server users.

3. SQL Window Functions Cheat Sheet

Author: LearnSQL.com Team

Link: https://learnsql.com/blog/sql-window-functions-cheat-sheet/

Description:

This cheat sheet is LearnSQL.com’s own helpful tool for anyone learning or working with SQL window functions. It explains what the window function does and how its syntax looks. It systematically goes through different parts of the syntax, such as the PARTITION BY and ORDER BY clauses. The cheat sheet also demystifies the window frame and shows you how it can be defined. It further contains the logical order of operations in SQL and a list of virtually every window function by category. Finally, it demonstrates and explains in detail what every function does.

Why it’s useful: 

The cheat sheet contains more graphical than verbal explanations. It’s very detailed and covers every aspect of window function usage. It’s called a cheat sheet for a reason: it can be downloaded and printed out so you can always have it on your wall, corkboard, or desk. Unlike the first two articles, it also covers distribution window functions (PERCENT_RANK() and CUME_DIST()). I don’t include it just because it’s ours, but because it’s really good!

4. 15 Types of SQL Window Functions (With Examples)

Author: Tim Enalls

Link: https://analyticsexplained.com/15-types-of-sql-window-functions-with-examples/

Description:

I found this article on Analytics Explained, which provides tips, tools, and tutorials for data professionals. The article doesn’t dwell much on theory or on explaining in detail what window functions are. Instead, it shows you the code for 15 window functions and how they work on a real-life example table.

Showing you 15 functions means there’s one window function missing: NTH_VALUE(). You can find this one in the cheat sheet mentioned above.

Why it’s useful:

It provides a relatively cut-and-dried overview of window functions. There’s nothing fancy, no excessive discussion or anything similar. It’s structured quite simply: window function—example code—result. Sometimes all you need is to see a certain function’s description and how it works. One additional benefit of this article is that it gives you specific real-life situations where you might need window functions.

5. How Window Functions Work

Author: Blake Barnhill

Link: https://dataschool.com/how-to-teach-people-sql/how-window-functions-work/

Description:

Here’s another window function article that goes into detail about how they work. You can find it on The Data School website, a free online platform for sharing data-related educational material. This article is very nicely structured for when you need to learn how SQL’s window functions are different from its aggregate functions. It starts by covering how window functions and their syntax work. It then goes on to give you quite a detailed explanation of every part of the window function syntax, such as specifying the window dimension using the ROWS keyword or using PARTITION BY and ORDER BY.

Why it’s useful:

GIFs! Yes, this article uses GIFs to show you how the window functions work. It’s not just a gimmick – it’s a really nicely structured and focused article that systematically explains everything you need to know to start using the window functions yourself. And it does all that on a database of AC/DC songs. As a lifelong fan, I couldn’t but recommend this article.

6. SQL Window Functions: The Intuitive Guide

Author: Do Lee

Link: https://towardsdatascience.com/sql-window-functions-the-intuitive-guide-5b56d7f437cb

Description:

Once the previous article draws you into the logic of SQL window functions, this is the natural extension. This intuitive guide is published on the Towards Data Science blog. This is a well-known Medium group that shares data science knowledge. This article isn’t about long snippets of code. It’s somewhat more theoretical than practical. However, it’s very well written, it’s detailed, and it gives good explanations of the OVER() clause. It also explains ORDER BY and PARTITION BY separately as well as how they work together.

Why it’s useful:

Besides covering all the important window function concepts, it gives you a nice mnemonic technique to help you think of window functions more intuitively. It also gives you links to the relevant PostgreSQL documentation, which is always very useful to read when learning window functions.

7. SQL Window Functions Tutorial for Business Analysis

Author: Alex Yeskov

Link: https://blog.statsbot.co/sql-window-functions-tutorial-b5075b87d129

Description:

This article by Alex Yeskov is published on Cube Dev’s data analytics blog on Medium. Its purpose is to demonstrate how SQL window functions can help you solve common business problems. The author shows you how to determine revenue growth, calculate running totals over a period of time, deal with duplicate data, find the top N rows in every group, and analyze repeat purchase behavior.

Why it’s useful:

Here the forte is real-life use cases that can be often found in the business world. The author is also very good at explaining every detail of the code they write, so you’ll not be left bewildered. Not only that, but the author also makes sure to explain the logic behind the problem and its solution. Once you understand the logic, it’s always easier to implement it in your SQL code.

8. Top 10 Problems to Practice Almost All SQL Concepts

Author: Mrinal Gupta

Link: https://towardsdatascience.com/10-problems-to-practice-almost-all-sql-concepts-37545e7c5219

Description:

This is another Towards Data Science post. In it, the author gives you ten questions that require knowledge of plenty of SQL concepts, ranging from basic to advanced. Out of ten questions, eight include window functions. These questions will ask you to compare employees’ average salaries, list students according to their exam scores, rank visitors by number of transactions, and create a report on a system running daily tasks. You’ll also learn how to create reports on customer orders, customer payments, and so on.

The article goes straight to the questions without any introduction to window function concepts. Its main intention is to provide you with real-life examples you can use for practice.

Why it’s useful:

It’s excellent for practicing. The questions from the article cover a vast range of problems. It’ll make you think and also write plenty of code. Not only will you practice window functions, you’ll also use other concepts like JOIN, CASE WHEN, CTE, pivot tables, etc. You can use this article if you’re preparing for an SQL job interview, as these questions often appear in the technical assessment.

Ready to Use SQL Window Functions More Often?

By drawing your attention to those eight articles, I’ve tried to provide you with a starter pack for SQL window functions. Some articles give you a more general overview of the concepts. Others go into much more detail, some of them with superb graphics and others with thorough descriptions. And you get plenty of examples to practice on.

Of course, this is just a start. If you want to systematically soak in all SQL’s  window function concepts and practice them, taking our Window Functions Course is highly recommended.