Eric Campbell 5 min read PostgreSQL

How to Use Window Functions in PostgreSQL

Window functions are one of PostgreSQL's most powerful features, yet many developers avoid them due to perceived complexity. At their core, window functions perform calculations across a set of rows...

Key Insights

  • Window functions compute values across related rows without collapsing the result set like GROUP BY does, enabling calculations like running totals and rankings while preserving individual row detail
  • The PARTITION BY clause divides data into logical groups while ORDER BY controls row sequence within partitions, and frame specifications (ROWS/RANGE) define exactly which rows participate in each calculation
  • Window functions eliminate complex self-joins and correlated subqueries, often providing 10x performance improvements while making SQL more readable and maintainable

Introduction to Window Functions

Window functions are one of PostgreSQL’s most powerful features, yet many developers avoid them due to perceived complexity. At their core, window functions perform calculations across a set of rows related to the current row—similar to aggregate functions—but with one critical difference: they don’t collapse your result set.

Consider a sales table where you want to show each transaction alongside the department’s total sales. With GROUP BY, you lose individual transaction details. With a window function, you keep everything:

-- Traditional GROUP BY: loses transaction detail
SELECT department, SUM(amount) as dept_total
FROM sales
GROUP BY department;

-- Window function: preserves all rows
SELECT 
    transaction_id,
    department,
    amount,
    SUM(amount) OVER (PARTITION BY department) as dept_total
FROM sales;

The second query returns every transaction while adding the department total to each row. This fundamental difference makes window functions invaluable for analytics, reporting, and complex data transformations.

Basic Window Function Syntax

Every window function follows the same pattern: function_name() OVER (window_specification). The OVER clause is what distinguishes a window function from a regular function. Even an empty OVER() clause creates a window encompassing the entire result set:

-- Assign sequential numbers to all rows
SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER () as row_num
FROM products;

PostgreSQL provides several ranking functions that work without any additional configuration:

-- Compare ranking functions
SELECT 
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
    RANK() OVER (ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

The differences matter: ROW_NUMBER() always assigns unique sequential numbers, RANK() creates gaps after ties, and DENSE_RANK() maintains consecutive ranks. If three employees tie for first place, RANK() makes the next employee fourth, while DENSE_RANK() makes them second.

PARTITION BY and ORDER BY Clauses

The real power emerges when you partition your data. PARTITION BY divides rows into groups, and calculations reset for each partition. Think of it as an implicit GROUP BY that doesn’t collapse rows:

-- Rank employees within their departments
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) as dept_rank
FROM employees;

This query ranks employees separately within each department. The highest-paid person in Engineering gets rank 1, and so does the highest-paid person in Sales—they’re in different partitions.

ORDER BY within the OVER clause controls how rows are sorted within each partition, which is crucial for calculations that depend on sequence:

-- Running total of sales by month
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) as running_total
FROM sales
ORDER BY sale_date;

Combining both clauses gives you fine-grained control:

-- Running total per department
SELECT 
    department,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date
    ) as dept_running_total
FROM sales;

Frame Specifications (ROWS and RANGE)

Frame specifications define exactly which rows within a partition participate in the calculation. By default, when you use ORDER BY, PostgreSQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—everything from the partition start to the current row.

You can be explicit about frames using ROWS or RANGE:

-- 3-month moving average
SELECT 
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3mo
FROM monthly_revenue;

This calculates the average of the current row plus the two preceding rows. For the first row, it averages only that row; for the second, it averages two rows; from the third onward, it averages three rows.

Frame boundaries are flexible:

-- Different frame specifications
SELECT 
    order_date,
    amount,
    -- From partition start to current row
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sum,
    -- Current row plus next 2 rows
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) as forward_avg,
    -- Centered window: 1 before, current, 1 after
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as centered_avg
FROM orders;

ROWS and RANGE differ in how they handle ties. ROWS counts physical rows, while RANGE includes all rows with the same ORDER BY value:

-- ROWS vs RANGE with duplicate dates
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as rows_sum,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as range_sum
FROM orders;

If multiple orders share the same date, RANGE includes all of them in the calculation, while ROWS processes them individually.

Common Window Functions and Use Cases

Beyond ranking, PostgreSQL offers window functions for comparing rows and accessing specific values within partitions.

LAG() and LEAD() access previous or subsequent rows, perfect for period-over-period analysis:

-- Month-over-month revenue change
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) as mom_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / 
        LAG(revenue) OVER (ORDER BY month), 
        2
    ) as mom_pct_change
FROM monthly_revenue;

FIRST_VALUE() and LAST_VALUE() extract boundary values from partitions:

-- Compare each sale to department's first and best
SELECT 
    department,
    employee_name,
    sale_amount,
    FIRST_VALUE(sale_amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date
    ) as first_sale,
    MAX(sale_amount) OVER (
        PARTITION BY department
    ) as best_sale
FROM sales;

For statistical analysis, window functions calculate percentiles and distributions:

-- Salary percentile within department
SELECT 
    department,
    employee_name,
    salary,
    PERCENT_RANK() OVER (
        PARTITION BY department 
        ORDER BY salary
    ) as percentile,
    NTILE(4) OVER (
        PARTITION BY department 
        ORDER BY salary
    ) as quartile
FROM employees;

PERCENT_RANK() returns a value between 0 and 1 representing relative position, while NTILE(n) divides rows into n equal groups.

Advanced Patterns and Performance Considerations

When using the same window specification multiple times, the WINDOW clause eliminates repetition and improves readability:

-- Named window for reuse
SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER dept_window as dept_avg,
    MAX(salary) OVER dept_window as dept_max,
    RANK() OVER (dept_window ORDER BY salary DESC) as dept_rank
FROM employees
WINDOW dept_window AS (PARTITION BY department);

For performance, window functions benefit from proper indexing. An index matching your PARTITION BY and ORDER BY columns can dramatically improve query speed:

-- Index supporting window function
CREATE INDEX idx_sales_dept_date 
ON sales(department, sale_date);

-- Efficiently uses the index
SELECT 
    department,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date
    ) as running_total
FROM sales;

Check your query plans with EXPLAIN ANALYZE to verify index usage. Window functions typically perform better than equivalent self-joins or correlated subqueries:

-- Self-join approach (slower)
SELECT s1.*, 
    (SELECT SUM(s2.amount) 
     FROM sales s2 
     WHERE s2.department = s1.department 
     AND s2.sale_date <= s1.sale_date) as running_total
FROM sales s1;

-- Window function (faster)
SELECT *,
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date
    ) as running_total
FROM sales;

The window function version scans the table once, while the self-join version requires a nested loop that scans repeatedly.

Conclusion

Window functions transform how you write analytical SQL. They eliminate the choice between aggregation and detail, letting you have both. Instead of wrestling with self-joins or correlated subqueries, you express complex calculations clearly and efficiently.

Master the core concepts—PARTITION BY for grouping, ORDER BY for sequencing, and frame specifications for precision—and you’ll handle everything from simple rankings to sophisticated time-series analysis. Start with basic ranking functions, progress to running calculations, then explore LAG/LEAD for comparisons. Your queries will become more readable, your performance will improve, and you’ll wonder how you ever lived without them.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.