Mastering Postgres Window Functions for Data Analysis in Supabase

Postgres window functions are a powerful SQL feature that lets you perform calculations across rows related to the current row without collapsing results. Unlike aggregate functions that reduce multiple rows into a single value, window functions retain each row while allowing computations such as running totals, moving averages, and rankings. They work well for real-time analytics, user scoring, or time-series comparisons, which are commonly needed in modern data-driven apps.

Window functions allow you to express powerful analytics logic in a single SQL statement that’s easier to write and debug and optimized by Postgres under the hood. This makes your queries faster and easier to manage.

In this article, I’ll explain what window functions are, how they differ from traditional aggregate functions, and how to use them effectively in Postgres. I’ll walk you through real-world examples of their usage, such as ranking users, calculating moving averages, tracking historical changes, and computing running totals, all while using clean, performant SQL that runs directly in your database.

All examples in this blog are tested on Supabase, an open source backend-as-a-service (BaaS) platform that simplifies backend development for web and mobile applications. Supabase provides full support for Postgres databases.

Understanding Postgres Window Functions

The term window function in SQL comes from the analogy of a sliding window that moves across data, allowing for calculations on a specific subset (“window” or “view”) of rows at a time. A window is a set of rows that are related to the current row and over which a window function is executed.

Window functions retain each row while still allowing calculations across neighboring rows. This makes them incredibly useful for analytical tasks like running totals, rankings, percent changes, or comparing a value to a group average. Window functions are used to write clean, expressive, and high-performance SQL. Here’s the basic structure of a window function:

<function_name>(<column>) OVER (
  [PARTITION BY column_list]
  [ORDER BY column_list]
  [ROWS or RANGE frame_clause]
)

The previous snippet’s components function as follows:

• PARTITION BY divides the data set into separate groups (like departments or users). Each group is treated as an independent mini-table.
• ORDER BY specifies the order for the rows within each partition, affecting how cumulative or ranking operations are performed.
• ROWS / RANGE defines the frame—how many rows to include relative to the current row.

Window Functions vs. Aggregate Functions

The easiest way to understand the difference between the window functions and aggregate functions is with an example. Imagine you want to analyze sales data for the following table:

If you’re analyzing sales by region, an aggregate query using SUM(amount) with GROUP BY region will give you the total per region but discard individual transactions:

SELECT region, SUM(amount) FROM sales GROUP BY region;

So the above statement will give you the following result:

A window function instead adds a column with the total sales per region alongside each original sale record. This means you get both the group-level insight and the detailed row-level data in a single result—ideal for use cases like percentiles or ranking users without losing context. Take the following window function as an example:

SELECT 
  id, region, amount,
  SUM(amount) OVER (PARTITION BY region) AS total_sales_per_region
FROM sales;

This will output the following:

Window Frames

While the “window” is a set of rows that a window function can operate on that is defined using the PARTITION BY (and optionally, ORDER BY) clause, a “window frame” is a subset of the “window”, used for row-by-row calculation. It’s defined using ROWS or RANGE clauses (like ROWS BETWEEN 1 PRECEDING AND CURRENT ROW). The “window frame” controls how many rows around the current row are used in the calculation. The window frame answers the question “Which nearby rows should be considered for this calculation?”

Window frames can be defined in two ways:

• ROWS: Selects a fixed number of rows before and after the current row, no matter what the values are.

  • Example: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING includes the row before, the current row, and the row after.

• RANGE: Selects rows that have similar values based on the ORDER BY column.

  • Example: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows with values less than or equal to the current one and treats tied values as a group.

The following are some common window-frame clause keywords:

Complete frame clauses combine these keywords to define a range. For example, this creates a three-row window—the previous row, the current row, and the next row:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

This includes all rows from the partition’s start up to the current row:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Together, these give you precise control over which rows are used in each window function’s calculation.

Common Types of Window Functions

The common types of window functions are divided into three major categories:

• Ranking functions: These functions assign a rank or position to each row within a partition based on a specified ordering—for example, ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(n).
• Aggregate functions: These functions perform a calculation on a set of rows and return a single value. When used as window functions, they operate within a defined window (partition) instead of collapsing rows like in a GROUP BY clause. The aggregate functions are SUM(), AVG(), COUNT(), MAX(), and MIN().
• Value functions: These functions retrieve values from another row in the window frame relative to the current row. The functions are LAG(n), LEAD(n), FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE(n).

The following table summarizes each of these function types:

Let’s now explore each of these function types with real-world examples.

Ranking Functions in Action

Postgres provides a powerful set of ranking functions that allow you to assign positions to rows based on a given order. As an example, this article uses a leaderboard-building scenario, where you can use the functions to perform actions such as identifying top performers, dividing users into cohorts, or analyzing performance relative to peers.

Ranking functions are always used with the OVER(ORDER BY ...) clause, and they do not collapse rows like GROUP BY; they preserve row-level detail while adding new insights. The example below uses the following student_results table:

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a partition based on the order specified. Tied values are given different row numbers arbitrarily. The following example ranks students within each subject based on their score:

SELECT 
  subject,
  student_name,
  score,
  ROW_NUMBER() OVER (
    PARTITION BY subject 
    ORDER BY score DESC
  ) AS row_num
FROM student_results;

The output assigns a unique row number starting from 1 for the top scorer within each subject:

RANK()

RANK() assigns a rank to each row. Tied values receive the same rank, but the next rank is skipped to preserve the total count. Here’s an example that assigns a rank to each student within their subject based on descending scores, giving equal ranks for tied scores and skipping the next rank(s) accordingly:

SELECT 
  subject,
  student_name,
  score,
  RANK() OVER (
    PARTITION BY subject 
    ORDER BY score DESC
  ) AS rank
FROM student_results;

In the output, Abigail and Sarah share rank 1 in the subject Math. Adam is ranked at 3 because it’s the next available in Maths:

DENSE_RANK()

DENSE_RANK() works like RANK(), but there are no gaps (ie no numbers are skipped) in the ranking sequence. Ties get the same rank, and the next rank increases by one. The following query assigns a dense rank to each student within their subject based on descending scores:

SELECT 
  subject,
  student_name,
  score,
  DENSE_RANK() OVER (
    PARTITION BY subject 
    ORDER BY score DESC
  ) AS dense_rank
FROM student_results;

As it gives equal ranks for tied scores without skipping the next rank (ranks remain consecutive), Adam gets rank 2 in Math this time:

NTILE(n)

NTILE(n) function distributes rows into n equal groups (or as evenly as possible). Each group is assigned a bucket number starting from 1. This query divides students within each subject into two equally sized groups (tiles) based on descending scores:

SELECT 
  subject,
  student_name,
  score,
  NTILE(2) OVER (
    PARTITION BY subject 
    ORDER BY score DESC
  ) AS performance_bucket
FROM student_results;

It also labels them as performance buckets (eg 1 = higher performers; 2 = lower performers):

Aggregate Window Functions in Action

Aggregate window functions allow you to perform aggregate calculations like SUM(), AVG(), and COUNT() without collapsing the result set. These functions are useful in calculating things like running totals, moving averages, or group-level statistics while still seeing individual records. Let’s walk through these key aggregate window functions and how they’re used in real-world scenarios using the following sales_commissions table:

SUM()

SUM() calculates a running total (cumulative sum) of values as you move through rows or the total sum for an entire group without collapsing the original data. When calculating the running total, it adds values row by row in order (eg daily sales accumulating over time). In calculating group total, it sums all values in a category (eg total sales per product) while keeping individual rows intact. This query calculates each employee’s commission and shows the total and running (cumulative) commission per product over time:

SELECT 
  employee_name,
  product_name,
  sale_date,
  units_sold * unit_price * commission_rate AS total_commission,

  -- Sum of total commission per product
  SUM(units_sold * unit_price * commission_rate) OVER (
    PARTITION BY product_name
  ) AS total_product_commission,

  -- Running total commission per product (ordered by date)
  SUM(units_sold * unit_price * commission_rate) OVER (
    PARTITION BY product_name
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_commission

FROM sales_commissions;

Here’s the output:

AVG()

The AVG() window function calculates the average (mean) of a set of values over a specific group of rows called a “window.” Instead of computing the average for the whole table, it gives you an average that changes row by row based on nearby rows in the window. This is useful for things like moving averages or spotting trends over time. This query computes each employee’s commission and shows the overall average and moving (cumulative) average commission per product over time based on the sale date:

SELECT 
  employee_name,
  product_name,
  sale_date,
  units_sold * unit_price * commission_rate AS total_commission,

  -- Overall average commission for the product
  AVG(units_sold * unit_price * commission_rate) OVER (
    PARTITION BY product_name
  ) AS average_commission,

  -- Moving average commission per product, ordered by sale date
  AVG(units_sold * unit_price * commission_rate) OVER (
    PARTITION BY product_name
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS moving_average_commission

FROM sales_commissions;

This is the output:

COUNT()

COUNT() counts how many rows fall within a defined window frame. It can be used for cumulative and group-wise counting. This query shows each employee’s sale and calculates the total number of sales for each product and a running (cumulative) count of sales per product over time based on sale_date:

SELECT 
  employee_name,
  product_name,
  sale_date,

  -- Group-wise count: total sales records for this product
  COUNT(*) OVER (
    PARTITION BY product_name
  ) AS total_sales_per_product,

  -- Cumulative count: running count of sales records per product by sale_date
  COUNT(*) OVER (
    PARTITION BY product_name
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_sales_count

FROM sales_commissions;

The output from this query is as follows:

Value Window Functions in Action

Value window functions are focused on fetching data from neighboring rows within a window frame and can compare the current row to past or future data. These functions are particularly useful in time-series analysis, trend detection, and change tracking. Let’s explore different value window functions through the following package_tracking table example:

LAG()

LAG() returns the value of a column from a previous row based on the ORDER BY clause. You can specify how many rows back to look, and the default is 1. This SQL query analyzes how much time passed between each checkpoint update for every package in the package_tracking table—that is, it calculates the delay or duration between checkpoints:

SELECT
  package_id,
  checkpoint,
  city,
  status_time,
  LAG(status_time) OVER (PARTITION BY package_id ORDER BY status_time) AS previous_time,
  EXTRACT(EPOCH FROM (status_time - LAG(status_time) OVER (
      PARTITION BY package_id ORDER BY status_time))) / 3600 AS hours_since_last
FROM package_tracking;

The first row returns “null” because there’s no previous row to look back:

LEAD()

LEAD() returns the value of a column from the next row. Like LAG(), you can define how far ahead to look. You can expose this info in your app or dashboard and show the next expected checkpoint, which is useful for live tracking systems or ETAs.

This query shows the most recent checkpoint and what’s expected next:

SELECT
  package_id,
  city,
  checkpoint AS current_checkpoint,
  status_time,
  LEAD(checkpoint) OVER (PARTITION BY package_id ORDER BY status_time) AS next_checkpoint
FROM package_tracking
WHERE package_id = 101
ORDER BY status_time DESC
LIMIT 1;

Here’s the output:

FIRST_VALUE()

The FIRST_VALUE() function returns the value of the first row in the window frame. This query retrieves the dispatch time for each package and the earliest recorded status_time per package_id:

SELECT
  package_id,
  checkpoint,
  city,
  status_time,

  -- Show origin dispatch time for the package
  FIRST_VALUE(status_time) OVER (
    PARTITION BY package_id 
    ORDER BY status_time
  ) AS dispatched_on

FROM package_tracking;

This is the output, which allows you to compare all other checkpoints against when the package was first sent:

LAST_VALUE()

LAST_VALUE() gives you the value from the last row in your window frame. By default, the window goes up only to the current row (not beyond it). So unless you extend the frame (using something like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), it might return the current row’s value, not the true last row of the whole group. This query checks whether a package has reached its final destination city (not the delivery status) by comparing the current checkpoint’s city with the last city in the delivery path:

SELECT
  package_id,
  checkpoint,
  city,
  status_time,

  -- Get the final destination city (same for all rows in that package)
  LAST_VALUE(city) OVER (
    PARTITION BY package_id
    ORDER BY status_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS final_destination_city,

  -- Compare current city to final destination
  CASE 
    WHEN city = LAST_VALUE(city) OVER (
      PARTITION BY package_id
      ORDER BY status_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) THEN 'Yes'
    ELSE 'No'
  END AS is_final_destination_reached

FROM package_tracking;

This is the output:

Remember, the clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is essential when using LAST_VALUE() in window functions because without it, LAST_VALUE() returns the current row’s value and not the true last value in the partition due to the default frame ending at the current row.

Common Pitfalls and Troubleshooting with Window Functions

Window functions are powerful, but small mistakes can lead to confusing results or slow performance. Here are ways to avoid common issues:

• Unstable ordering: If ORDER BY values are tied, the row order becomes unpredictable. Fix this by adding a secondary column: ORDER BY score DESC, student_id.
  • Unexpected ranks: If two rows have the same value, RANK() gives them the same rank but skips the next number (creating a gap). DENSE_RANK() also gives the same rank but doesn’t skip any numbers. So tied values affect how ranks are counted.
  • Performance issues: Using complex window ranges or grouping by columns with too many unique values (like IDs) can make queries slow. Instead, use simpler window frames and group by useful categories like departments or regions.
  • Null behavior: By default, Postgres treats null values as the smallest when sorting. You can use NULLS LAST if you want them at the end. LAG() and LEAD() also return “null” when there’s no previous or next row. Use a default value like LAG(sales, 1, 0) to avoid this.

Tip: Debug by starting with a minimal query and adding complexity step by step. It helps reveal how window frames and partitions are applied.

Conclusion

Postgres window functions let you perform advanced analytics—like running totals, rankings, and comparisons—without losing row-level detail. They are more flexible and readable than complex subqueries or joins. We saw real-world examples like sales analysis, student rankings, and time-based trends using different window functions.