28_day_running_percentile.sql

28_day_running_percentile.sql

with  base_data as (
    select received_at, date_trunc('day', received_at)::date as end_date ,
           date_add('day', -28, date_trunc('day', received_at))::date start_date, load_time
    from outschool_com.performance_largest_contentful_paint
    where
      received_at > date_add('day',-90, sysdate::date)
    and load_time > 0
    and load_time < 60000
    --order by md5('seed' || received_at)
    ),

date_array as (
    select distinct end_date as event_date from base_data
)

select event_date,
       (event_date - INTERVAL '28 day')::date AS window_start,

percentile_cont(0.75)  within group (order by load_time)
from date_array d join base_data bd
on bd.received_at between d.event_date- INTERVAL '28 day' and d.event_date
group by 1,2
order by 1,2;