Understanding Window Functions in SQL with Running Totals

Introduction

Window functions in SQL are incredibly powerful, allowing you to perform calculations across a set of table rows related to the current row. They enable tasks like calculating running totals, generating rankings, and more, all while maintaining individual row-level detail.

In this post, we’ll dive into window functions, focusing on how to calculate a running total that resets for each customer. This is a common requirement in financial reporting, where we need to track cumulative totals within specific groups, such as by customer or by product.

What Are Window Functions?

Window functions allow you to perform calculations across rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a group of rows, window functions can return a value for each row in the result set.

Here’s a quick breakdown:

Syntax: Window functions use the OVER clause, which defines the set of rows (the “window”) for the calculation.
PARTITION BY: Divides the result set into partitions, applying the window function separately to each partition.
ORDER BY: Defines the order of rows within each partition.

The Problem: Running Totals with Reset

Imagine you have a table of payments, and you want to calculate a running total of the payment amounts. However, this running total should reset to 0 for each new customer.

Let’s start with the query:

SELECT
  customer_id,
  amount,
  payment_date::timestamp::date AS payment_date,
  SUM(amount) OVER (ORDER BY customer_id, payment_date) AS running_total
FROM
  payment
ORDER BY
  customer_id, payment_date;

This query calculates a running total across all rows, but it doesn’t reset for each customer_id. To fix this, we’ll use the PARTITION BY clause.

Solution: Using PARTITION BY

To ensure the running total resets for each customer, modify the query as follows:

SELECT
  customer_id,
  amount,
  payment_date::timestamp::date AS payment_date,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date) AS running_total
FROM
  payment
ORDER BY
  customer_id, payment_date;

Explanation:

PARTITION BY customer_id: This clause divides the rows into groups based on customer_id, so the running total calculation is performed separately within each group.
ORDER BY payment_date: Orders the rows by payment date within each customer_id group to calculate the running total in sequence.

With this query, the running total starts at 0 for each new customer, exactly as required.

Conclusion

Window functions, especially with the use of PARTITION BY, provide a flexible and powerful way to perform complex calculations in SQL. Whether you’re calculating running totals, rankings, or moving averages, window functions can simplify your SQL queries and make them more readable and efficient.