Optimizing Data Analysis with PostgreSQL Window Functions: Hands-on RANK, ROW_NUMBER, and LAG/LEAD

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Nested Subqueries, Self-Joins, or Window Functions?

Early last year, our revenue dashboard started lagging as our order volume hit the 5 million record mark. The breaking point arrived every Monday morning when the marketing team pulled reports on the top 3 highest-value orders alongside comparisons with adjacent purchases. CPU usage on our RDS PostgreSQL instance spiked to 98%, and queries took over 30 seconds to return results.

Facing this bottleneck, backend engineers typically consider three approaches:

  • Correlated Subqueries: Stuffing subqueries into SELECT or WHERE clauses. This is usually the most intuitive approach to conceptualize initially.
  • Self-Joins: Joining a table with itself using timestamps or IDs.
  • Window Functions (The OVER + PARTITION BY Clause): Computing metrics directly across data partitions without collapsing individual rows.

Production Realities: A Comparison After 6 Months of Refactoring

After benchmarking on an orders table holding around 4.2 GB of data, the performance gap became glaringly evident:

1. Subqueries and Self-Joins: Quick to Write, Painful to Run

Junior developers often reach for subqueries because they feel convenient. However, the database pays a steep price:

  • I/O Overload: Correlated subqueries force the engine to execute millions of repeated full table scans ($O(N^2)$), rapidly exhausting the buffer cache.
  • Maintenance Nightmare: Fetching preceding and succeeding orders required three self-joins. The SQL script spanned an entire page, making it nearly impossible for teammates to parse and maintain.

2. Window Functions: Single-Pass Scan, Plummeting Latency

Once the team migrated the queries entirely to Window Functions, query latency plummeted from 28 seconds down to just 340ms.

  • Major Advantage: PostgreSQL scans the table only once (Single-Pass Scan). The working data is gathered into memory (work_mem) and evaluated immediately. The query footprint was cut in half, yielding much cleaner logic.
  • Important Caveat: Window Functions are executed after the WHERE, GROUP BY, and HAVING stages. As a result, you cannot directly place a window function inside a WHERE clause to filter records. Instead, you need to wrap the computation in a CTE (WITH ... AS) or an outer subquery.

When Should You Use Window Functions?

Here is a quick rule of thumb:

  • Need to collapse rows (requiring a single aggregated summary per customer group)? Go with GROUP BY.
  • Need to retain individual row details while appending calculated columns (ranking orders, retrieving the previous transaction, computing running totals)? Choose Window Functions.

Hands-on Practice with PostgreSQL

Let’s set up a test table simulating an order scenario:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10, 2),
    order_date DATE
);

INSERT INTO orders (customer_id, amount, order_date) VALUES
(101, 150.00, '2026-01-05'),
(101, 300.00, '2026-01-12'),
(101, 300.00, '2026-02-01'),
(102, 50.00,  '2026-01-10'),
(102, 200.00, '2026-01-18'),
(103, 500.00, '2026-02-15');

If you have an existing CSV dump from a legacy database and need a quick test, you can check out toolcraft.app/en/tools/data/csv-to-json to convert it to JSON or inspect its structure without worrying about data leaking to external servers.

1. Distinguishing ROW_NUMBER, RANK, and DENSE_RANK

These three functions are frequently confused when tackling ranking problems:

  • ROW_NUMBER(): Assigns sequential numbers (1, 2, 3…) strictly incrementing, regardless of tied values.
  • RANK(): Rows with identical values share the same rank, but subsequent ranks skip numbers (1, 2, 2, 4).
  • DENSE_RANK(): Rows with identical values share the same rank without skipping subsequent numbers (1, 2, 2, 3).

Let’s rank order amounts per customer:

SELECT 
    order_id,
    customer_id,
    amount,
    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
    RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;

Here, PARTITION BY customer_id segments the orders per customer, while ORDER BY amount DESC sorts orders from highest to lowest within each partition.

2. Retrieving Top N Records per Group

To extract each customer’s most expensive order, we pair a CTE with ROW_NUMBER():

WITH ranked_orders AS (
    SELECT 
        order_id,
        customer_id,
        amount,
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn = 1;

3. Calculating Delta Against Preceding/Succeeding Rows with LAG and LEAD

When computing Month-over-Month (MoM) growth or time gaps between purchases, LAG and LEAD provide an exceptionally clean solution:

  • LAG(column, offset, default): Retrieves values from a preceding row.
  • LEAD(column, offset, default): Retrieves values from a succeeding row.

Calculate revenue variance compared to a customer’s immediately preceding order:

SELECT 
    customer_id,
    order_date,
    amount AS current_amount,
    LAG(amount, 1, 0) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
    amount - LAG(amount, 1, amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS diff_amount
FROM orders;

4. Computing Running Totals

Instead of writing complex recursive queries, simply combine SUM() with an ORDER BY clause:

SELECT 
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER(
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Each row automatically accumulates totals from the initial transaction up to the current entry. The syntax is concise and efficiently leverages composite indexes on (customer_id, order_date) without triggering database I/O bottlenecks.

Share: