Introduction to Window Functions

Window functions let you compute calculations across a set of rows that are related to the current row, without collapsing those rows into a single output like GROUP BY does. This means you keep the original row-level detail while still getting aggregates, rankings, and comparisons side by side. Common use cases include ranking the top selling products per region, computing running totals for financial reports, and calculating moving averages over time. The key syntax combines an aggregate function with an OVER clause that defines how the "window" of rows is sliced.

Basic Syntax and Core Functions

A window function call looks like an ordinary aggregate but with an OVER clause appended. Inside OVER you define PARTITION BY (how to group rows into windows), ORDER BY (how to sort within each window), and optionally a frame clause like ROWS BETWEEN. The most frequently used window functions are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG, and COUNT — each works as either an aggregate or a navigation function depending on the context.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER assigns a unique sequential number within each partition, restarting at 1 when the partition changes. RANK and DENSE_RANK behave similarly but assign the same number to ties and differ in how they handle gaps after tied values.

Ranking and Top-N Queries

Ranking is one of the most practical applications. Suppose you want the top 3 highest paid employees per department. You compute a rank column and then filter on it, either with a subquery or a CTE for clarity. This pattern is far more efficient than correlated subqueries and reads naturally in application code.

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

Notice how PARTITION BY department restarts the ranking for every department, so you always get exactly 3 winners per group even if some salaries are tied.

Running Totals and Moving Frames

Running totals and rolling averages require a frame clause inside OVER. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives you a cumulative sum that grows from the start of the partition to the current row. Switching to a fixed window like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW produces a 7-row moving average, which is common in time series analytics.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

The first column grows monotonically, while the moving average smooths out daily spikes to reveal the underlying trend. Frame definitions can use ROWS (physical count) or RANGE (logical, value-based), but ROWS is faster and more predictable for most reporting needs.

Common Pitfalls and Performance Tips

A frequent mistake is forgetting that window functions run after WHERE, HAVING, and GROUP BY — so they cannot be filtered directly in the same query without a CTE or subquery. Another pitfall is mixing window functions with SELECT DISTINCT or aggregating the result set in ways that confuse the planner. For performance, always index the columns used in PARTITION BY and ORDER BY, and prefer ROWS over RANGE when you do not need value-based framing. Avoid putting window functions in WHERE clauses by wrapping the query in a CTE first.

Summary

Window functions in PostgreSQL are the right tool whenever you need row-level detail alongside grouped calculations. The OVER clause with PARTITION BY and ORDER BY gives you full control over how the window is defined, and frame clauses extend that control to rolling computations. Mastering ranking, running totals, and moving averages covers roughly 80 percent of real-world analytical queries you will write as a backend developer.