3

Please login or register to vote for this query.

(click on this box to dismiss)

Plot the user activity for a site, aggregated per 28 days.

Q&A for ebook publishers and readers

declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?240## - 1, CURRENT_TIMESTAMP)
;

with UserAct as (

        select distinct
               DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate) / 28 * 28, 0) as YearMon,
               UserId as UserId
        from PostHistory
        where CreationDate > @epoch
    UNION
        select distinct
               DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate) / 28 * 28, 0) as YearMon,
               UserId
        from Comments
        where CreationDate > @epoch

) , Activity as (


    select YearMon,
           'Active Users in Week' as Series,
           count(UserId) as Action
    from UserAct u
    group by YearMon
)
select *
from Activity
pivot (
    sum(Action)
    for Series in (
        [Active Users in Week]))
    as Pivoted
where YearMon >= DATEADD(WEEK, - ##Weeks:int?240## , CURRENT_TIMESTAMP) -- previous 60 weeks.
and YearMon < DateAdd(dd, -1, CURRENT_TIMESTAMP) -- ignore most recent week.
order by YearMon

fork query

edited jun 8 15 Jack Douglas

created jan 21 14 rolfl