Lesson 17.8

SQL Playground Mission: Rolling Sessions Report

Build a daily sessions table, then calculate a three-active-date rolling average with a window frame.

Rolling reports are two-layer queries. First, aggregate the raw events to the reporting grain. Then run the window function over that grouped result.

This mission uses active session dates only, so the rolling average covers the current active date row and the two previous active date rows in the result. It isn't a calendar window with missing dates filled in. The rows between 2 preceding and current row clause is a window frame; Lesson 19.4 · Window frames explains frames in full.

Pattern

with daily as (
  select metric_date, count(*) as daily_count
  from fact_table
  group by metric_date
)
select metric_date,
       daily_count,
       avg(daily_count) over (
         order by metric_date
         rows between 2 preceding and current row
       ) as rolling_average
from daily

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

Your task

Return one row per active session_date with sessions, avg_duration, and rolling_3_active_day_sessions, both averages rounded to one decimal. The rolling value is the average of sessions over the current row and two preceding rows, ordered by date.

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

Run your query to inspect the result.

Expected answer

Columns: session_date, sessions, avg_duration, rolling_3_active_day_sessions.
Rows: one row per active session date (26 rows).
The rolling metric averages the current active day and the two prior active days.

← Previous · 17.7 Campaign revenue report

End of Chapter 17

That's the last lesson in “Guided Playground Missions.”

The next chapter builds on what this one covered.

Start Chapter 18: Modeling and Data Quality → Begins with 18.1 Profile a table before you query

Reference

SQL Subqueries and EXISTS

Nested SELECTs, IN, EXISTS, and correlation.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview