Learn SQL Window Functions: PARTITION BY & Ranking

The Problem: Repeated Subqueries

You have a sales table with columns salesperson, region, amount, and sale_date. When you want to add a column showing the average amount per region, a naive approach uses a subquery:

SELECT salesperson, region, amount,
    (SELECT AVG(amount) FROM sales s2 WHERE s2.region = s1.region) AS region_average
FROM sales s1;

This recalculates the average for every row. With three regions (north, south, east), the north average is computed four times, the south average four times, and the east average three times – 11 calculations instead of three.

Window Functions: Partitioning Without Collapsing

A window function splits the table into groups (windows) of related rows and applies a function once per window, then fills the result into every row in that window. It works like GROUP BY but does not collapse rows.

Average per Region with PARTITION BY

SELECT salesperson, region, amount,
    AVG(amount) OVER (PARTITION BY region) AS region_average
FROM sales;

PARTITION BY region groups rows by region, just like GROUP BY, but keeps all rows. The average is calculated once per region and repeated for each row in that region. The result is identical to the subquery version, but far more efficient.

Ranking Within Partitions

To rank salespeople by their total sales within each region:

SELECT salesperson, region,
    SUM(amount) AS total_sales,
    RANK() OVER (
        PARTITION BY region
        ORDER BY SUM(amount) DESC
    ) AS region_rank
FROM sales
GROUP BY salesperson, region
ORDER BY region, region_rank;

RANK() assigns a rank within each region partition, ordered by the sum of amounts descending. DENSE_RANK() would assign the same rank to ties without gaps, but RANK() skips numbers after ties. The GROUP BY is needed because you are aggregating SUM(amount) per salesperson and region. The result shows each region having its own ranking starting at 1.

Running Totals

A running total accumulates a sum over ordered rows without grouping.

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

The ORDER BY sale_date inside the OVER clause defines the order for the running sum, not the final presentation order. The outer ORDER BY sale_date sorts the output. Each row shows the cumulative sum of all amounts up to and including that date.