Get weekly Average for last 90 days in SQL-CodePudding

i have a table that has session id and profile_type, and dt (2021-01-05 = format) as column headers. i want to run a query to get the average amount of sessions per week for the last 90 days for each profile type. i am currently counting session ids to get total session count. so far i have something like this and i am getting errors when i run:

Select DATE_TRUNC('week'   , dt) AS week ,
profile_type, 
avg(e.session_count)
from    
(Select profile_type, 
 count(session_id) over 
 (partition by profile_type) as session_count
 from XXX ) e
where dt > current_date - 90

CodePudding user response：

Try

with week_count as (
    Select 
    profile_type,
    DATE_TRUNC('week'   , dt) AS week_no, 
    count(profile_type) as cnt
    from xxx
    group by 1,2
)
select
profile_type,
week_no
avg(cnt)
from week_count
group by 1,2