Window Functions

A window function computes a value across a set of related rows while keeping every row in the result. The examples use monthly revenue for two regions.

-- setup.sql
CREATE TABLE revenue (
    region TEXT NOT NULL,
    month  DATE NOT NULL,
    amount INT  NOT NULL
);
INSERT INTO revenue VALUES
    ('North', '2024-01-01', 100), ('North', '2024-02-01', 120),
    ('North', '2024-03-01',  90), ('North', '2024-04-01', 150),
    ('South', '2024-01-01',  80), ('South', '2024-02-01',  80),
    ('South', '2024-03-01', 130), ('South', '2024-04-01', 110);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 8

OVER and PARTITION BY

OVER () makes an aggregate a window function; PARTITION BY splits the window into groups.

-- partition.sql
SELECT region, month, amount,
       sum(amount) OVER ()                    AS grand_total,
       sum(amount) OVER (PARTITION BY region) AS region_total,
       round(100.0 * amount / sum(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM revenue
ORDER BY region, month;

$ psql -d sqldemo -f partition.sql
 region |   month    | amount | grand_total | region_total | pct_of_region 
--------+------------+--------+-------------+--------------+---------------
 North  | 2024-01-01 |    100 |         860 |          460 |          21.7
 North  | 2024-02-01 |    120 |         860 |          460 |          26.1
 North  | 2024-03-01 |     90 |         860 |          460 |          19.6
 North  | 2024-04-01 |    150 |         860 |          460 |          32.6
 South  | 2024-01-01 |     80 |         860 |          400 |          20.0
 South  | 2024-02-01 |     80 |         860 |          400 |          20.0
 South  | 2024-03-01 |    130 |         860 |          400 |          32.5
 South  | 2024-04-01 |    110 |         860 |          400 |          27.5
(8 rows)

All eight rows survive; GROUP BY region would have collapsed them to two. Each row sees the total of its own partition, which is what makes “percent of region” a one-line calculation.

ROW_NUMBER, RANK, and DENSE_RANK

The three ranking functions differ only in how they treat ties. South has two months at 80.

-- ranking.sql
SELECT month, amount,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM revenue
WHERE region = 'South'
WINDOW w AS (ORDER BY amount DESC)
ORDER BY amount DESC, month;

$ psql -d sqldemo -f ranking.sql
   month    | amount | row_number | rank | dense_rank 
------------+--------+------------+------+------------
 2024-03-01 |    130 |          1 |    1 |          1
 2024-04-01 |    110 |          2 |    2 |          2
 2024-01-01 |     80 |          3 |    3 |          3
 2024-02-01 |     80 |          4 |    3 |          3
(4 rows)

The two 80s are tied. row_number still gives them 3 and 4 (which one gets 3 is arbitrary unless you add a tie-breaker to ORDER BY). rank gives both 3 and would skip to 5; dense_rank gives both 3 with no gap. WINDOW w AS (...) names the window once instead of repeating it three times.

Filtering on a Window Function

“Best month per region” sounds like a WHERE on row_number().

-- window_where_error.sql
SELECT region, month, amount
FROM revenue
WHERE row_number() OVER (PARTITION BY region ORDER BY amount DESC) = 1;

$ psql -d sqldemo -f window_where_error.sql
psql:window_where_error.sql:4: ERROR:  window functions are not allowed in WHERE
LINE 3: WHERE row_number() OVER (PARTITION BY region ORDER BY amount...
              ^

Window functions run after WHERE, so they cannot be used there. Compute the number in a subquery, then filter the outer query.

-- top_per_region.sql
SELECT region, month, amount
FROM (
    SELECT region, month, amount,
           row_number() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM revenue
) ranked
WHERE rn = 1
ORDER BY region;

$ psql -d sqldemo -f top_per_region.sql
 region |   month    | amount 
--------+------------+--------
 North  | 2024-04-01 |    150
 South  | 2024-03-01 |    130
(2 rows)

This is the standard top-N-per-group pattern. Change rn = 1 to rn <= 2 for the two best months of each region.

LAG and LEAD

lag reads a value from an earlier row in the window; lead reads a later one.

-- lag.sql
SELECT region, month, amount,
       lag(amount) OVER (PARTITION BY region ORDER BY month) AS prev,
       amount - lag(amount) OVER (PARTITION BY region ORDER BY month) AS change
FROM revenue
ORDER BY region, month;

$ psql -d sqldemo -f lag.sql
 region |   month    | amount | prev | change 
--------+------------+--------+------+--------
 North  | 2024-01-01 |    100 |      |       
 North  | 2024-02-01 |    120 |  100 |     20
 North  | 2024-03-01 |     90 |  120 |    -30
 North  | 2024-04-01 |    150 |   90 |     60
 South  | 2024-01-01 |     80 |      |       
 South  | 2024-02-01 |     80 |   80 |      0
 South  | 2024-03-01 |    130 |   80 |     50
 South  | 2024-04-01 |    110 |  130 |    -20
(8 rows)

January has no previous month inside its partition, so prev and change are NULL there. The partition stops lag from reaching across regions: South’s January does not compare itself with North’s April.

Running Totals and Moving Averages

With ORDER BY inside OVER, sum becomes a running total. A frame clause turns avg into a moving average.

-- running.sql
SELECT month, amount,
       sum(amount) OVER (ORDER BY month) AS running_total,
       round(avg(amount) OVER (ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM revenue
WHERE region = 'North'
ORDER BY month;

$ psql -d sqldemo -f running.sql
   month    | amount | running_total | moving_avg_3 
------------+--------+---------------+--------------
 2024-01-01 |    100 |           100 |        100.0
 2024-02-01 |    120 |           220 |        110.0
 2024-03-01 |     90 |           310 |        103.3
 2024-04-01 |    150 |           460 |        120.0
(4 rows)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages at most three rows. In January and February there are fewer rows behind, so those averages cover one and two months.

Running Totals With Ties

The default frame for an ordered window is RANGE, which treats rows with the same sort value as one step. Watch the South running total over amounts that tie.

-- running_ties.sql
SELECT month, amount,
       sum(amount) OVER (ORDER BY amount)                   AS range_total,
       sum(amount) OVER (ORDER BY amount, month ROWS UNBOUNDED PRECEDING) AS rows_total
FROM revenue
WHERE region = 'South'
ORDER BY amount, month;

$ psql -d sqldemo -f running_ties.sql
   month    | amount | range_total | rows_total 
------------+--------+-------------+------------
 2024-01-01 |     80 |         160 |         80
 2024-02-01 |     80 |         160 |        160
 2024-04-01 |    110 |         270 |        270
 2024-03-01 |    130 |         400 |        400
(4 rows)

Both 80 rows show 160 in range_total: RANGE adds all peers with the same amount at once. ROWS adds one physical row at a time, so its first row is 80. The month tie-breaker in the ROWS window matters: without it, which 80 row comes first is up to the plan, and the two running totals could swap between runs. Neither frame is wrong, but if you expected a row-by-row running total, the default gave you something else.

The LAST_VALUE Trap

first_value works as expected, and last_value looks broken.

-- last_value.sql
SELECT month, amount,
       first_value(amount) OVER w AS first,
       last_value(amount)  OVER w AS last_default,
       last_value(amount)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                   AND UNBOUNDED FOLLOWING) AS last_full
FROM revenue
WHERE region = 'North'
WINDOW w AS (ORDER BY month)
ORDER BY month;

$ psql -d sqldemo -f last_value.sql
   month    | amount | first | last_default | last_full 
------------+--------+-------+--------------+-----------
 2024-01-01 |    100 |   100 |          100 |       150
 2024-02-01 |    120 |   100 |          120 |       150
 2024-03-01 |     90 |   100 |           90 |       150
 2024-04-01 |    150 |   100 |          150 |       150
(4 rows)

last_default just repeats each row’s own amount. The default frame ends at the current row, so “the last value so far” is the current value. Extending the frame to UNBOUNDED FOLLOWING gives April’s 150 on every row.

Practice

1. For each region, show each month’s amount and the region’s best month amount on every row.

-- practice_region_max.sql
SELECT region, month, amount,
       max(amount) OVER (PARTITION BY region) AS best
FROM revenue ORDER BY region, month;

$ psql -d sqldemo -f practice_region_max.sql
 region |   month    | amount | best 
--------+------------+--------+------
 North  | 2024-01-01 |    100 |  150
 North  | 2024-02-01 |    120 |  150
 North  | 2024-03-01 |     90 |  150
 North  | 2024-04-01 |    150 |  150
 South  | 2024-01-01 |     80 |  130
 South  | 2024-02-01 |     80 |  130
 South  | 2024-03-01 |    130 |  130
 South  | 2024-04-01 |    110 |  130
(8 rows)

2. Find the month with the lowest revenue in each region, keeping ties.

-- practice_lowest.sql
SELECT region, month, amount
FROM (SELECT *, rank() OVER (PARTITION BY region ORDER BY amount) AS r
      FROM revenue) t
WHERE r = 1
ORDER BY region, month;

$ psql -d sqldemo -f practice_lowest.sql
 region |   month    | amount 
--------+------------+--------
 North  | 2024-03-01 |     90
 South  | 2024-01-01 |     80
 South  | 2024-02-01 |     80
(3 rows)

3. Show each North month with next month’s amount using lead.

-- practice_lead.sql
SELECT month, amount, lead(amount) OVER (ORDER BY month) AS next_month
FROM revenue WHERE region = 'North' ORDER BY month;

$ psql -d sqldemo -f practice_lead.sql
   month    | amount | next_month 
------------+--------+------------
 2024-01-01 |    100 |        120
 2024-02-01 |    120 |         90
 2024-03-01 |     90 |        150
 2024-04-01 |    150 |           
(4 rows)

4. Split all eight rows into two buckets by amount with ntile.

-- practice_ntile.sql
SELECT region, month, amount, ntile(2) OVER (ORDER BY amount DESC) AS half
FROM revenue ORDER BY amount DESC, region, month;

$ psql -d sqldemo -f practice_ntile.sql
 region |   month    | amount | half 
--------+------------+--------+------
 North  | 2024-04-01 |    150 |    1
 South  | 2024-03-01 |    130 |    1
 North  | 2024-02-01 |    120 |    1
 South  | 2024-04-01 |    110 |    1
 North  | 2024-01-01 |    100 |    2
 North  | 2024-03-01 |     90 |    2
 South  | 2024-01-01 |     80 |    2
 South  | 2024-02-01 |     80 |    2
(8 rows)