SQL Window Functions Made Easy

Understand SQL Window Function Basics And Springboard Into Advanced Patterns With PostgreSQL

SQL Window functions are powerful and fun to write, but they have a steeper learning curve than aggregates and joins. In this article, we walk through window functions step-by-step.

If you’re familiar with SQL queries and aggregates, then you’re ready to dive into this article.

What’s A Window Function?

A window function adds a column of data to the result of a SQL query. This column can contain the output of aggregations like SUM and COUNT as well as special window functions like LAG and LEAD.

However, unlike normal aggregates, a window function doesn’t require the returned table to be grouped by a specific field (or fields). Instead, the window function performs a calculation and then attaches the result to each row of the returned table.

Let’s look at an example. Say we have a table of sales data that looks like this:

Table of Sales Data

Each row includes sale data, with month, date, item, and price fields. If we wanted…