Best Online Courses for SQL Window Functions

In this article, I will summarize the top online tutorials and courses available on SQL window functions for enthusiasts of data analysis, data science, and data engineering. The information is here to help you embrace the topic with ease and feel comfortable using window functions in SQL databases.

SQL window functions can help you solve some of the most common analysis challenges in business:

• What are the N most popular products for each month of the year?
• What are the trends in repeat purchase behavior per consumer?
• What is the sales growth % month over month?
• Can these metrics even be trusted, or are there undetected duplicates buried in the data set?

Questions like these can all be answered with a proper use of the SQL window functions.

What Are SQL Window Functions?

SQL window functions, also called OVER functions or analytic functions, are extremely powerful tools for data analysis. These functions make building complex data segmentations and aggregations much simpler and more streamlined.

Window functions allow calculations or statistical analysis to be performed over a set or a range of rows and return a value based on the query. The "window" is basically the set of rows the calculation operates on, and the window function uses the values in the window to determine what to return.

SQL window functions can only be included within SELECT or ORDER BY clauses.

Some common SQL window functions include the following:

• Aggregate: COUNT, SUM, MIN, MAX, AVG
• Offset: FIRST_VALUE, LAST_VALUE, LEAD, LAG
• Statistical: PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST
• Ranking functions: ROW_NUMBER, RANK, DENSE_RANK

Window functions can also work on a set of rows related to the current row. This set is called a frame and can be defined as a certain number of rows before and/or after the current row (using the keyword ROWS) or rows within a certain range from the current rows (using the keyword RANGE).

To better understand practical uses of SQL window functions, check out our earlier article, “When Do I use SQL Window Functions?”

Top 6 Online Courses on SQL Window Functions

Below are the top online courses and tutorials you can take to understand how SQL window functions work and how to use them. Before we start going through the list, I stress on the fact that practice is key in order to truly master and be comfortable with its use, just like any other SQL topic you have learned so far.

LearnSQL.com: Window Functions

LearnSQL.com’s Window Functions course is designed to not only teach you the syntax and the semantics of window functions, but also to provide you with an effective platform for retaining the knowledge through practical examples and hands-on exercises. It is the only interactive course in our list here where you can practice the materials taught in the web-integrated learning environment.

The course covers the essentials of window functions, starting with a basic overview module. This is followed by modules that cover individual pieces of syntax such as OVER, OVER(PARTITION BY), PARTITION BY, ORDER BY, ranking functions, and analytic functions, along with more advanced concepts such as evaluation order and window frames. The course wraps up with a comprehensive practice module and a final quiz that tests your knowledge.

Through the modules, you will see window functions in multiple practical use cases such as finding the best and the worst performers, building rankings, investigating trends over time, and computing running totals and averages. While this is largely a beginner-friendly course, some prior basic knowledge in SQL is recommended, including JOINs and GROUP BY clauses. Junior data analysts and developers working on relational databases will hugely benefit from this course by gaining a deeper understanding of SQL aggregate functions.

Moreover, two very useful variants of this course are available, covering the same materials but with implementation on different databases: Window Functions in MS SQL Server and Window Functions in PostgreSQL.

Pluralsight: T-SQL Window Functions

The T-SQL Window Functions course offered on Pluralsight utilizes Microsoft’s Transact SQL extension to demonstrate the syntax and performance optimization of window functions in Microsoft SQL Server.

In this course, students will learn how to use the T-SQL window functions to solve common queries. Major topics covered include an introduction to window functions, tools to measure query performance, framing, and in-depth explanations of functions such as FIRST_VALUE, LAST_VALUE, ranking functions, window aggregate functions, and statistical functions.

However, the course description and summary does not specify any modules for practice exercises or quizzes, which are key to a comprehensive learning experience as I have emphasized earlier. The total instructional content of the course is only about 2 hours compared to almost ten times that in LearnSQL.com’s Window Functions course. Additionally, this is an intermediate level course, so learners are recommended to have a prior fundamental understanding of SQL and T-SQL.

Lynda: Advanced SQL – Window Functions

Lynda, operated by LinkedIn, offers an Advanced SQL – Window Functions course. It covers the foundations and nuances of processing window functions, with the aim of implementing them in every business challenge.

The course covers different constructions and advanced solution techniques, as well as how to use the composable and declarative nature of SQL. Topics covered include framing, exclusions, and shortcuts, in addition to the typical functions such as aggregate, rank, distribution, and offset window functions.

Similar to Pluralsight’s course, it is approximately 2 hours in length. The prerequisite skill level is outlined as “Advanced.” Looking at the course content and comparing with other courses on this list, however, a foundational understanding of SQL would seem to suffice.

DataCamp: PostgreSQL Summary Stats and Window Functions

Through DataCamp’s PostgreSQL Summary Stats and Window Functions course, you can learn how to create queries for business analytics and data engineering using SQL window functions. The course covers an overview of window functions and concepts, including fetching values from different sections of a table, ranking rows, binning rows into tables, and defining window frames, as well as the typical window functions syntax with PARTITION BY and ORDER BY. The chapters use a “flights” data set to solve some practical business problems such as generating rankings, calculating moving averages, and performing time intelligence.

Like the three courses already mentioned above, a basic knowledge of SQL including SELECT, FILTER, ORDER BY, and JOINs is recommended before starting. The instructional duration is about 4 hours, slightly edging out the courses from Pluralsight and Lynda.

Udemy: Introduction to SQL Window Functions

Udemy’s Introduction to SQL Window Functions provides a comprehensive coverage of window functions, including a primer on setting up your database environment. The course covers some of the common topics in window functions such as assigning rankings, and how to use the FIRST_VALUE or LAST_VALUE functions with ORDER BY and PARTITION BY to set up a frame for window functions. You will also have the opportunity to learn DENSE_RANK as well as the differences between ROWS and RANGE.

The instructional duration is a little over 3 hours and covers almost all the concepts as that of its LearnSQL.com counterpart. However, in comparison, it lacks the interactive practice and quiz modules, which, to reiterate, is an inimitable technique for learning and concept retention.

Learn Window Functions With an Online Course

Window functions are an absolutely indispensable tool for data science and business analytics – ask any data professional! Above are some of the most comprehensive online SQL courses focused on window functions, and they all cover appreciable depths of content.

Having said that, what makes LearnSQL.com’s Window Functions course unique and attractive is definitely its interactive learning and practice environment, unmatched by any of the other platforms. As I have emphasized throughout the article, this very aspect of the learning experience is what sets it apart.