Lesson 21.3

Rolling Calendar Windows in SQL

ROWS BETWEEN 6 PRECEDING counts seven rows, not seven days. On data with quiet days, RANGE with an interval is the window that means what the report says.

The rolling sessions mission averaged each day with the two rows before it. That is a three-row window. It is a three-day window only if every day has a row, and real data has quiet days. A "last 7 days" column needs a window measured on the calendar:

Pattern

with daily as (
  select session_date, count(*) as sessions
  from sessions
  group by session_date
)
select session_date,
       sessions,
       sum(sessions) over (
         order by session_date
         range between interval 29 days preceding and current row
       )::integer as last_30_days
from daily
order by session_date

Rows or days

rows between 2 preceding and current row takes the current row and the two rows above it, however far back they are. range between interval 2 days preceding and current row takes every row whose date is no more than two days before the current one, however many rows that is, including none:

Three rows against three days

with daily as (
  select session_date, count(*) as sessions
  from sessions
  where session_date >= date '2024-06-01'
  group by session_date
)
select session_date,
       sessions,
       sum(sessions) over (order by session_date
                           rows between 2 preceding and current row)::integer as last_3_rows,
       sum(sessions) over (order by session_date
                           range between interval 2 days preceding and current row)::integer as last_3_days
from daily
order by session_date

On June 8 the two windows disagree: the three-row window reaches back to June 1 and counts 3, while the three-day window covers June 6 to 8 and counts 1. On June 2 they agree, because June 1 is the day before.

Two ways to the same answer

The other fix is to give every day a row first, with the date spine from Lesson 21.2, and keep the ROWS frame. That is the version to use when the report should also list the empty days. RANGE with an interval is shorter when it should not. PostgreSQL 11 and later runs the same RANGE syntax, written interval '6 days'; MySQL 8 writes interval 6 day.

Seven days is six preceding

The current day counts as one of the seven, so a seven-day window reaches back six days. interval 7 days preceding is eight days, and the total quietly runs high.

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

The starter's last_7_days sums the current row and the six rows before it. Make it the number of sessions in the seven calendar days ending on each row's date (that day and the six before it). Keep the columns session_date, sessions, and last_7_days as an integer, and sort by session_date.

Website Analytics · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: session_date, sessions, last_7_days.
Rows: the 11 days with sessions from May 1 on, the same days as the starter.
June 1's last_7_days is 1 and June 19's is 3: June 16, 18, and 19 all fall inside its seven days.

← Previous · 21.2 Date spines

End of Chapter 21

That's the last lesson in “Time Series in SQL.”

The next chapter builds on what this one covered.

Start Chapter 22: Lists, Structs, and JSON → Begins with 22.1 Lists

Reference

SQL Date Functions

Date parts, math, and truncation.

Cheatsheet

SQL Date and String Functions Cheatsheet

Everyday date and text functions.

Interview