Lesson 19.4

Window Frames in SQL

A frame is the set of rows a window function sees for each row. The default one treats ties as a single step, which surprises nearly everyone once.

Every window function works on a frame: for each row, the set of rows it's allowed to see. A running total sees everything up to the current row; a 3-row moving average sees the current row and the two before it. You can write the frame out, and when you don't, SQL picks one for you.

Pattern

select reading_date,
       reading,
       avg(reading) over (
         order by reading_date
         rows between 2 preceding and current row
       ) as moving_avg_3
from daily_readings

Writing a frame

rows between <start> and <end> counts positions in the ordered partition. Each end is one of unbounded preceding (the first row), n preceding, current row, n following, or unbounded following (the last row). rows between 2 preceding and current row is the three-row window in the pattern above.

The default frame

With an ORDER BY in the window and no frame, the standard default is range between unbounded preceding and current row. RANGE works on values, not positions: rows that tie on the ORDER BY value are peers, and the frame always includes all of them. So a running count over dates takes every session on a date in one step:

Default frame vs ROWS on tied dates

select session_id,
       session_date,
       count(*) over (order by session_date) as default_frame,
       count(*) over (
         order by session_date, session_id
         rows between unbounded preceding and current row
       ) as rows_frame
from sessions
where session_date < date '2024-02-10'
order by session_date, session_id

Two sessions on 2024-01-18 both read 4 under the default frame, and three on 2024-02-08 all read 10. The ROWS frame, with session_id to settle the order, counts them one at a time. Without an ORDER BY at all, the frame is the whole partition, which is why sum(x) over () gives a grand total. The same trap in a running total is written up in why a window running total jumps on ties.

Moving averages

A frame with a fixed size gives a moving average or moving sum: rows between 6 preceding and current row is a 7-row window. Rows are not days, though. If some dates have no rows, 7 rows can span more than a week. Lesson 17.8 · Rolling sessions report builds a 3-row average over active days and says so in its result.

The LAST_VALUE trap

Because the default frame ends at the current row, last_value(x) over (order by y) returns the current row's own value, not the last row in the partition. Write the frame out to the end, rows between unbounded preceding and unbounded following, or flip the sort and use first_value(). See why LAST_VALUE returns the current row.

Schema · Website Analytics Table · sessions 7 columns · 30 rows

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id int user_id int session_date date channel text campaign_id int device text duration_seconds int

Syntax reference

Need syntax details? See the SQL window function reference

Your task

Number the sessions in the order they happened. Return session_id, session_date, and running_sessions: a running count that goes up by exactly 1 on every row, even when several sessions share a date. Break ties by session_id, and sort by session_date, then session_id.