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 to query this data for the number of sales per month, we would use a COUNT and GROUP BY like so:

select month, count(*) as “total monthly sales” from sales_data group by month;

Here’s the result of our query:

But this, of course, groups our data by month and removes many data points. We can calculate “total monthly sales” with a window function without grouping rows together. The result looks like this:

Now we have the convenience of viewing the detailed data and the aggregated data side by side.

How A Window Function Works