Percentage and Rolling Window

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
  GOAL
    1)what percentage of 'students' posted between 06-30-20 and 07-01-21?
    2)three‐month rolling average of posts per user, all inclusive?
  
  DEFINITION
    Defining three month as 90 days.


 

1)find out percentage of 'students' that posted between given date range
select 
  --count(OwnerUserId)
  FORMAT((Cast(count(OwnerUserId) as decimal(18,2))/
    (select count(id) from users where AboutMe like '%student%')
  ),'P') as result  
from Posts
where CreationDate between '06-30-20' and '07-01-21'
and OwnerUserId in(
    select id from users
    where AboutMe like '%student%'
  )
--result is 61.7
 
2)three‐month rolling average of posts per user, all inclusive?

select avg(metric) OVER(
    PARTITION by OwnerUserId
    order by CreationDate
    ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
    as periodical
from(
  
  ) as agg
*/
  Select OwnerUserId, CreationDate, count(Id) as metric
    from Posts
    group by OwnerUserId, CreationDate

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results