SQL Playground Mission: Rolling Sessions Report

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 is not a calendar window with missing dates filled in.

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