Mash's Musings

When to use window functions

Published Jul. 24, 2021

Learning SQL is often a 6-step process:

  1. Learn the basic syntax to manipulate a single table.
  2. Learn how to join in additional tables.
  3. Learn the basics of window functions.
  4. Really learn the basic operations.
  5. Really learn how to use joins.
  6. Really learn how to use window functions.

One of the first barriers I ran into was in step 3, trying to wrap my head around window functions. Fortunately there are many great resources that provide in-depth explanations of how window functions work which certainly helped me in getting to step 3 and step 6. But I want to focus on perhaps a more important aspect of getting to step 3: when should I even use window functions? What types of problems will they help solve?

Fundamentally, when thinking through any SQL problem, one must think in terms of records. What information does each record in the table represent? Do I only care about certain records? Time to think of the WHERE. Do I need records from a different table? FROM clause. Do I care about records in aggregate? GROUP BY clause. Do I only care about specific groups? HAVING clause. The vast majority of the time, this basic syntax will get me what I need. By thinking very carefully about what I want to happen to 3 or 4 records, I can get a sense of the shape of my final query.

However, sometimes when breaking down a problem, I run into a roadblock: what if I need information from some other records to compute information about this record? For example, let's say I have daily records of the weather, what if I want to compare today's record against yesterday's record? Running through the list of basic SQL clauses, I realize that none of them quite solve this problem. Enter window functions. The biggest clue to use window functions is when you need to pull information from other records, into this record.

Okay... Well when is that? Since that statement is a little opaque, let's think of a couple examples and break down how they fit the criteria.

Hopefully these examples give you a better picture of when window functions are the right tool to use. They may not be something you use everyday but when you inevitably run into this inter-record problem space, they really make your life easier. As a fun aside, know that all window functions could be replaced by a single CTE and join, which is how these inter-record problems were solved up until recently. Once you start to feel a solid footing on step 3, try replacing your window functions with joins to start the climb to step 4.