Write Window Functions for Analytics

You’ve been pulling reports with GROUP BY and subqueries, but every time you need a running total, a rank, or a moving average, you end up writing convoluted self-joins or hitting the limits of your SQL. The pain is real: your queries become slow, hard to read, and even harder to maintain. But PostgreSQL has a built‑in feature that makes all of this elegant and fast: window functions. In this lesson, you’ll learn how to write window functions for analytics, turning repetitive, painful SQL into clean, expressive statements that run circles around your old approach.

The problem this lesson solves

When you need to compute metrics that depend on other rows in your result set — like a running total of sales, a ranking of employees by salary, or a moving average of daily traffic — GROUP BY actually breaks down. GROUP BY collapses rows into a single output row per group, so you lose the detail you need for comparisons within groups. The typical workaround is to use a self‑join or a correlated subquery, which is not only verbose but can become painfully slow on large tables.

Consider a simple request: "Show each sale, along with a running total of sales per customer." With GROUP BY, you’d need to join the table to an aggregated version of itself. That’s messy, and it gets worse when you want multiple running metrics at once. Window functions solve exactly this problem: they let you perform calculations across a set of rows related to the current row, without collapsing them into a single output row. Each row keeps its identity, and you get extra columns that carry the result of the windowed calculation.

Without window functions, your SQL gets tangled in self‑referencing joins and subqueries that are hard to debug and slow to execute. With them, you write clear, declarative code that expresses your intent directly.

Core concept / mental model

Think of a window function as a telescope over your result set. Imagine you’ve already selected the rows you want (the query’s final result set). Now, for each row, you can look ahead, look behind, or zoom in on a group of related rows. The window defines that frame — the set of rows that the function can see for each current row.

• OVER() — the clause that defines the window. Without any arguments, the window is the entire result set.
• PARTITION BY — divides the result set into partitions (like GROUP BY for windows). The function is applied independently to each partition.
• ORDER BY inside OVER() — defines the logical order within each partition. Many window functions (ranking, running totals) rely on this order.
• Frame specification — (ROWS BETWEEN ... AND ...) controls the exact rows in the window relative to the current row, for moving windows like a 7‑day average.

A key mental model: window functions are evaluated after the WHERE, GROUP BY, and HAVING clauses, but before the final ORDER BY of the query. This means they see the rows that have already been filtered and grouped, and they can add new columns with the results.

Another helpful analogy: you have a spreadsheet with each row as a sale. You add a new column that, for each row, shows the sum of everything above it in that column. That’s a running total — a window function.

How it works step by step

Let’s break down how to write a window function in practice.

1. Start with your base query — the SELECT, FROM, WHERE, etc., that produces the rows you care about.

2. Add a window function in the SELECT list. The syntax is:

sql function_name (expression) OVER ( [PARTITION BY column1, column2, ...] [ORDER BY column3, column4, ...] [ROWS BETWEEN ... AND ...] ) AS column_alias

1. Choose the right function — ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), LAG(), LEAD(), etc. Remember that aggregate functions like SUM() and AVG() can be used as window functions when followed by OVER().

2. Define your OVER() clause — decide whether you need partitioning (PARTITION BY) and/or ordering (ORDER BY). If you want a running total across the whole result, just use ORDER BY. If you want a running total per customer, add PARTITION BY customer_id.

3. Adjust the frame if you need a moving window — for example, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7‑day moving average.

4. Alias the result with AS so you can reference it in ORDER BY or in a subquery.

Example: Ranking with ROW_NUMBER() and RANK()

Here’s a simple ranking example. Suppose you have a table of employees with salaries:

SELECT first_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;

• ROW_NUMBER() assigns a unique sequential number, even for ties.
• RANK() leaves gaps after ties (1,2,2,4).
• DENSE_RANK() does not leave gaps (1,2,2,3).

Hands-on walkthrough

Let’s put it all together with a realistic example. We’ll use a sales table: id, sale_date, customer_id, and amount.

1. Running total of sales per customer

SELECT sale_date, customer_id, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY sale_date
       ) AS running_total
FROM sales
ORDER BY sale_date, customer_id;

Output (first few rows):

sale_date   | customer_id | amount | running_total
------------+-------------+--------+--------------
2023-01-01  | 1           | 100    | 100
2023-01-03  | 1           | 150    | 250
2023-01-02  | 2           | 200    | 200
2023-01-05  | 2           | 50     | 250

The SUM(...) OVER (...) AS running_total computes the cumulative sum of amount for each customer, ordered by date.

2. Ranking customers by total revenue

SELECT customer_id,
       SUM(amount) AS total_revenue,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY customer_id;

Output:

customer_id | total_revenue | rank
------------+---------------+-----
3           | 500           | 1
1           | 300           | 2
2           | 250           | 3

Notice that we’re using an aggregate (SUM) in the window function — that’s allowed because the GROUP BY collapses rows first, and then the window function operates on the aggregated result.

3. 7‑day moving average of daily revenue

First, get daily revenue:

SELECT sale_date, SUM(amount) AS daily_revenue
FROM sales
GROUP BY sale_date;

Then add a moving average:

SELECT sale_date, daily_revenue,
       AVG(daily_revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM (
    SELECT sale_date, SUM(amount) AS daily_revenue
    FROM sales
    GROUP BY sale_date
) AS daily
ORDER BY sale_date;

Output (shows the first rows):

sale_date   | daily_revenue | moving_avg_7d
------------+---------------+------------------
2023-01-01  | 100           | 100.00
2023-01-02  | 150           | 125.00
2023-01-03  | 120           | 123.33
...

The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines the window to include the current row and the previous six, giving a 7‑day average.

4. Comparing each sale to the previous sale for the same customer

SELECT sale_date, customer_id, amount,
       LAG(amount, 1) OVER (
           PARTITION BY customer_id
           ORDER BY sale_date
       ) AS prev_amount,
       amount - LAG(amount, 1) OVER (
           PARTITION BY customer_id
           ORDER BY sale_date
       ) AS diff
FROM sales
ORDER BY customer_id, sale_date;

Output:

sale_date   | customer_id | amount | prev_amount | diff
------------+-------------+--------+-------------+------
2023-01-01  | 1           | 100    | NULL        | NULL
2023-01-03  | 1           | 150    | 100         | 50
2023-01-02  | 2           | 200    | NULL        | NULL
2023-01-05  | 2           | 50     | 200         | -150

LAG() looks back one row within the partition. The first row has no previous row, so it returns NULL.

Pro tip: Use LAG() and LEAD() to compute differences between consecutive rows without self‑joins. That’s a huge readability and performance win.

Compare options / when to choose what

Window functions vs. traditional approaches:

When to choose what:

• Use window functions whenever you need to compute a value for each row that depends on other rows in the same result set. That includes running totals, rankings, moving averages, and row‑to‑row comparisons.
• Use GROUP BY when you truly need one output row per group, and you don’t need the detail rows.
• Use LATERAL subqueries for correlated calculations that are complex, but window functions are simpler for the common cases.

Variation: Named windows — you can define a window once with the WINDOW clause and reuse it:

SELECT sale_date, amount,
       SUM(amount) OVER w AS running_total,
       AVG(amount) OVER w AS running_avg
FROM sales
WINDOW w AS (ORDER BY sale_date)
ORDER BY sale_date;

This cleans up repeated OVER() clauses and makes the query more maintainable.

Troubleshooting & edge cases

• NULL in the result (e.g., LAG() on the first row): That’s expected. Use COALESCE() if you want a default value, e.g., COALESCE(LAG(amount) OVER (...), 0).
• Unexpected order in running totals: If you forget the ORDER BY inside OVER(), SUM() will return the total for the whole partition on every row — not a running total. Always include ORDER BY when you need a cumulative calculation.
• Performance: RANGE vs ROWS frame: The default frame for SUM() with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which can be slower on large datasets. Use the ROWS frame explicitly if possible, especially for moving windows.
• Multiple window functions with different partitions: Each window function has its own PARTITION BY; they don’t have to match. That’s fine, but keep an eye on performance.
• GROUP BY + window function: Window functions are applied after aggregation. If you get weird results, double‑check whether you need to group first.
• DISTINCT with window functions: If you use SELECT DISTINCT and window functions, the window function is evaluated before the DISTINCT, which can produce surprising results. Wrap in a subquery.

Pro tip: Always test your window functions on a small dataset first. Explain the query with EXPLAIN ANALYZE to see if the frame specification is causing a performance hit.

What you learned & what's next

You’ve learned the core idea behind writing window functions for analytics: using OVER(), PARTITION BY, and ORDER BY to compute running totals, rankings, moving averages, and row‑to‑row comparisons without collapsing your result set. You practiced building a running total per customer, ranking customers by revenue, and computing a 7‑day moving average. You also saw common pitfalls and how to choose between window functions and traditional approaches.

Now that you can write window functions, you’re ready to tackle more advanced analytical SQL. The next lesson in this track is "Write CTEs for step-by-step analytics" — you’ll learn to break complex analytics into readable, modular steps using common table expressions. That’s a natural next step for building maintainable reporting queries.

Practice makes perfect: try writing your own window function on your own data — a running total of your expenses or a ranking of your products by sales. Go ahead and experiment!