Window Functions

CS 374, Fall 2026

Today

  • Aggregate functions vs. window functions
  • Ranking: rank, dense_rank, row_number
  • Comparing rows: lag, lead, running totals
  • Lab: Window Functions on TPC-H (30 min)
  • Then time for HW5 and GP3 reviews

GROUP BY Collapses Rows

SELECT r_name, count(*) AS nations
FROM nation
  JOIN region ON r_regionkey = n_regionkey
GROUP BY r_name;
  • 5 rows: one per region, and n_name is gone
  • SELECT r_name, n_name, count(*) ... GROUP BY r_name is an error
  • What if you want every nation and the count for its region?

OVER Keeps Every Row

SELECT r_name, n_name, count(*) OVER (PARTITION BY r_name) AS nations
FROM nation
  JOIN region ON r_regionkey = n_regionkey
ORDER BY r_name, n_name;
r_name n_name nations
AFRICA ALGERIA 5
AFRICA ETHIOPIA 5
... ... ...
  • 25 rows: count is computed over a window of rows, attached to each one
  • PARTITION BY is like GROUP BY, except nothing is collapsed

The Shape of a Window Function

function(...) OVER (PARTITION BY ... ORDER BY ...)
  • PARTITION BY: which rows are in the same window (default: all of them)
  • ORDER BY: the order of rows within each window (needed for ranks, lag, running totals)
  • The function: any aggregate (count, sum, avg, ...), or a window-only function (rank, lag, ...)

Order of evaluation:
FROM → WHERE → GROUP BY → HAVING → windows → SELECT → ORDER BY

Percent of the Total

SELECT r_name, count(*) AS customers,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM customer
  JOIN nation ON n_nationkey = c_nationkey
  JOIN region ON r_regionkey = n_regionkey
GROUP BY r_name;
r_name customers pct
AFRICA 29764 19.8
AMERICA 29952 20.0
  • OVER () is one window holding every row of the result
  • sum(count(*)) works because windows run after GROUP BY
  • 100.0, not 100, to avoid integer division

Ranking

SELECT c_custkey, count(o_orderkey) AS orders,
  rank()       OVER (ORDER BY count(o_orderkey) DESC),
  dense_rank() OVER (ORDER BY count(o_orderkey) DESC),
  row_number() OVER (ORDER BY count(o_orderkey) DESC)
FROM customer LEFT JOIN orders ON o_custkey = c_custkey
WHERE c_custkey <= 12
GROUP BY c_custkey;
c_custkey orders rank dense_rank row_number
4 20 1 1 1
10 20 1 1 2
7 16 3 2 3
  • Ties: rank skips (1, 1, 3), dense_rank doesn't (1, 1, 2), row_number picks one

Top N per Group

SELECT n_name FROM nation WHERE rank() OVER (ORDER BY n_name) <= 3;
-- ERROR:  window functions are not allowed in WHERE

-- The three largest orders of each year
SELECT year, o_orderkey, o_totalprice, rank
FROM (
  SELECT extract(year FROM o_orderdate) AS year, o_orderkey, o_totalprice,
    rank() OVER (PARTITION BY extract(year FROM o_orderdate)
                 ORDER BY o_totalprice DESC)
  FROM orders
) AS ranked
WHERE rank <= 3
ORDER BY year, rank;
  • WHERE runs before the windows:
    rank in a subquery, then filter outside (21 rows)

Comparing with the Previous Row

SELECT year, orders,
  orders - lag(orders) OVER (ORDER BY year) AS change
FROM (
  SELECT extract(year FROM o_orderdate) AS year, count(*) AS orders
  FROM orders GROUP BY year
) AS yearly
ORDER BY year;
year orders change
1992 227089 NULL
1993 226645 -444
  • lag(x): x from the previous row; lead(x): from the next one
  • The first row has no previous row, so lag is NULL (or use lag(x, 1, 0))
  • Without windows: a self join of yearly on y2.year = y1.year + 1

Running Totals

SELECT year, orders,
  sum(orders) OVER (ORDER BY year) AS running_total
FROM ( ... yearly, as before ... ) AS yearly;
year orders running_total
1992 227089 227089
1993 226645 453734
1998 133623 1500000
  • With ORDER BY, the window is "every row up to this one": a running sum
  • Without ORDER BY, it is every row: the grand total on each row
  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a 7-row moving window

Dates and Naming Windows

extract(year FROM o_orderdate)     -- 1997
extract(month FROM o_orderdate)    -- 1 through 12
date_trunc('month', o_orderdate)   -- 1997-03-01 00:00:00-05 (a timestamp)

When several functions share a window, name it once:

SELECT year, orders,
  orders - lag(orders) OVER w AS change,
  sum(orders) OVER w AS running_total
FROM ( ... ) AS yearly
WINDOW w AS (ORDER BY year);

Aggregate vs. Window

GROUP BY OVER
Rows in the result one per group one per input row
Other columns only grouped ones any column
Runs before HAVING after HAVING, before ORDER BY
Can filter on it in HAVING an outer query (OVER in subquery)
Typical uses totals, counts ranks, shares, changes, running totals

Where This Is Going

  • GP3: answer your reviews and push follow-up commits before the weekend
  • HW5: Analytical Queries -- due Monday, Nov 02
    • You can now write all 8 queries
  • Nov 03: Views and Indexes, and a new database: air
  • GP4: Queries and Views -- starts next week

Lab: Window Functions on TPC-H

30 minutes, then HW5 and team time