Module 03b // Analytical SQL

Window Functions

> Aggregations and ranking that don't collapse rows. Compute running totals, moving averages, gaps, and per-group ranks inline with each row.

The Mental Model

A regular GROUP BY aggregate collapses rows. A window function looks at a sliding "window" of rows around the current row and returns a value without collapsing. Every row stays — you just get an extra column.

The syntax is function() OVER (PARTITION BY ... ORDER BY ... frame). The three parts are independent and each is optional.

Ranking Functions

Three flavors that differ on how ties are handled:

  • ROW_NUMBER() — unique sequential number, ties broken arbitrarily.
  • RANK() — same rank for ties, leaves gaps (1, 2, 2, 4).
  • DENSE_RANK() — same rank for ties, no gaps (1, 2, 2, 3).
  • NTILE(n) — buckets rows into n approximately equal groups.
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk,
  NTILE(4)     OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

Top-N per Group

The classic use case — pick the top 3 earners in each department:

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

LAG and LEAD — Looking at Neighbors

LAG(col, n) returns the value n rows before the current row; LEAD(col, n) looks ahead. Perfect for period-over-period deltas.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS mom_pct
FROM monthly_revenue;

Running Totals and Moving Averages

Add a frame clause to define the window of rows the aggregate sees. The default frame for ordered windows is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running total.

SELECT
  order_date,
  amount,
  -- Running total since the beginning
  SUM(amount) OVER (ORDER BY order_date) AS running_total,

  -- 7-day moving average (current row + 6 preceding)
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7d,

  -- Centered 3-day average
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_avg
FROM daily_sales;

ROWS vs RANGE vs GROUPS

  • ROWS — physical row offsets. Predictable, ignores ties.
  • RANGE — value-based; includes all peers with the same ORDER BY value.
  • GROUPS — peer-group offsets (Postgres 11+).

Use ROWS for moving averages over time, RANGE when you want all same-day rows to share a window.

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Pull a specific row from the frame. Watch out: LAST_VALUE with the default frame ends at the current row, not the partition end.

SELECT
  customer_id,
  order_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

Named Windows

Reuse the same window spec across multiple functions with a WINDOW clause:

SELECT
  name,
  salary,
  AVG(salary)  OVER w AS avg_salary,
  RANK()       OVER w AS rnk,
  COUNT(*)     OVER w AS dept_size
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Sessionization (Gaps & Islands)

Group consecutive events into sessions using LAG + a cumulative SUM trick:

WITH marked AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
           > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
),
sessioned AS (
  SELECT *,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM marked
)
SELECT user_id, session_id, MIN(event_time) AS started, MAX(event_time) AS ended
FROM sessioned
GROUP BY user_id, session_id;

Performance Notes

  • Window functions run after WHERE/JOIN/GROUP BY — filter first.
  • You can't reference a window result in WHERE; wrap in a CTE/subquery.
  • An index on (partition_cols, order_cols) can eliminate the sort.
  • Multiple functions sharing the same OVER spec are computed in one pass.