Think In SQL – Window Function Demisify

SQL Window Function 101 with Examples and Diagram

Photo by Katerina Pavlyuchkova on Unsplash

Many SQL tutorials mark the window function as the advanced topic. Numerous job interviews on SQL often involve at least one question on window function. Understanding window function is critical for anyone that writes SQL daily (as well as SQL like languages like Spark Dataframe and Pandas). In this story, let's think in SQL and demystify the window function with examples and diagrams.

What is Window Function?

Many versions of window function definitions are described by the community or the official DB vendors (MySQL, Postgresql). To be clear, I describe the window function in the following way:

Window Function enables current row to oversee a range of rows, applies function on those rows, and returns result back to the current row.

However, reading the definition will only resonate on how to think and use in SQL. Let's put the definition aside. We'd need concrete examples and diagrams to explain how the window function works.

Window Function Example Setup