Window functions in depth

01The problem

Harbourline's monthly bookings bounce around: 146 in April 2025, 106 in June, 142 in July. The operations director asks, "Is volume actually going up or down, or is it just noise?" A month-by-month chart is too jumpy to answer that. What she needs is a moving average, which smooths each month with the months before it.

In SQL for Data Analysis you met RANK, ROW_NUMBER, running totals and LAG. This lesson covers the part that trips up experienced analysts: which rows a window function actually looks at.

02The concept

The frame

Inside OVER (…), PARTITION BY picks the group and ORDER BY sorts it. The frame then picks which rows of the group the function uses for the current row:

AVG(shipments) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW   -- this month and the two before
)

Two default-frame traps

1. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step. A running total ordered by booking_date gives every shipment booked on the same day the same total. If you want one step per row, write ROWS and add a tie-breaker (ORDER BY booking_date, shipment_id).
2. LAST_VALUE(x) OVER (ORDER BY …) returns the current row's value, because the default frame stops at the current row. Give it the whole partition, or use FIRST_VALUE with the order reversed.

Shares of a total

SUM(x) OVER () is the grand total on every row, and SUM(x) OVER (PARTITION BY mode) is the mode's total. Divide by either to get a share without a second query.

LAG and LEAD

LAG(x, n, default) looks n rows back (1 by default) and LEAD looks forward. They're how you calculate month-on-month change, or the gap between one order and the next.

03Example

A 3-month moving average of bookings, showing it only once three months are available:

WITH monthly AS (
  SELECT strftime('%Y-%m', booking_date) AS month, COUNT(*) AS shipments
  FROM shipments
  GROUP BY month
)
SELECT
  month,
  shipments,
  CASE
    WHEN COUNT(*) OVER w = 3 THEN ROUND(AVG(shipments) OVER w, 1)
  END AS moving_avg_3,
  shipments - LAG(shipments) OVER (ORDER BY month) AS change_vs_last_month
FROM monthly
WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY month;

The moving average sits between about 126 and 140 all the way through. June 2025's 106 is a dip, not a trend, and volume is broadly flat. That's the answer to the director's question, and it's far more reliable than reading the raw months.

04Walkthrough

1. Run the example. WINDOW w AS (…) names a window once so several functions can share it.
2. Remove the CASE so the first two months show a "moving average" of one and two months. That's why it's worth hiding incomplete windows: January's "average" is just January.
3. Run the RANGE trap for yourself. Look at the rows where several shipments share a booking date:

SELECT
  shipment_id,
  booking_date,
  freight_charge,
  SUM(freight_charge) OVER (ORDER BY booking_date) AS range_total,
  SUM(freight_charge) OVER (ORDER BY booking_date, shipment_id ROWS UNBOUNDED PRECEDING) AS rows_total
FROM shipments
ORDER BY booking_date, shipment_id
LIMIT 12;

4. Notice that range_total jumps once per day, while rows_total climbs once per shipment. Both are "correct", but they answer different questions.

05Practice

06Challenge

07More practice

08Check your understanding