53

Please login or register to vote for this query.

(click on this box to dismiss)

Plot the site activity by voting, questions, answers and comments, aggregated per week.

Q&A for professionals and students of engineering

-- 2014-12-09 - exclude closed questions from charts.
declare @epoch as datetime = DATEADD(WEEK, - ##Weeks:int?104## - 1, CURRENT_TIMESTAMP)
;

with VoteNames as (
   select pt.Id as PostTypeId,
          vt.Id as VoteTypeId,
          case when pt.Id in (1, 2) and vt.Id = 1 then 'Answer Accepts in week'
               when pt.Id = 1 and vt.Id != 1 then 'Question Votes in week'
               when pt.Id = 2 and vt.Id != 1 then 'Answer Votes in week'
               else 'Unknown'
       end as VName
   from VoteTypes vt,
        PostTypes pt
   where vt.Id in (1, 2, 3)
     and pt.Id in (1, 2)
     
) , UserAct as (

        select distinct
               DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,CreationDate)), CONVERT(DATE,CreationDate)) as YearMon,
               UserId as UserId
        from PostHistory
        where CreationDate > @epoch
    UNION
        select distinct
               DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,CreationDate)), CONVERT(DATE,CreationDate)) 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

  UNION

    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon,
           'New Users in week' as Series,
           count(*) as Action
    from Users u
    where u.CreationDate > @epoch
    group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate))

  UNION
  
    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon,
       VName as Series,
       count(*) as Action
    from Posts p,
         Votes v,
         VoteNames as vn
    where p.Id = v.PostId
      and v.VoteTypeId = vn.VoteTypeId
      and p.PostTypeId = vn.PostTypeId
      and v.VoteTypeId in (1, 2, 3)
      and p.PostTypeId in (1, 2)
      and v.CreationDate > @epoch
    group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate),
          VName
  
  UNION
  
    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon,
       case when pt.Id = 1 then 'Questions Asked in week'
               when pt.Id = 2 then 'Answers Given in week'
               else 'Unknown'
       end as Series,
       count(*) as Action
    from Posts p, PostTypes pt
    where p.PostTypeId = pt.Id
      and p.PostTypeId in (1, 2)
      and p.CreationDate > @epoch
      and p.ClosedDate is null
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)),
          pt.Id
  
  UNION
    
    SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon,
        'Comments in week' as Series,
        count(*) as Action
    from Comments c, Posts p
    where c.CreationDate > @epoch
      and c.PostId = p.Id
      and p.ClosedDate is null
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate))
)
select *
from Activity
pivot (
    sum(Action)
    for Series in (
        [Questions Asked in week],
        [Answer Accepts in week],
        [Answers Given in week],
        [Comments in week],
        [Question Votes in week],
        [Answer Votes in week],
        [New Users in week],
        [Active Users in Week]))
    as Pivoted
where YearMon >= DATEADD(WEEK, - ##Weeks:int?60## , CURRENT_TIMESTAMP) -- previous 60 weeks.
and YearMon < DateAdd(dd, -1, CURRENT_TIMESTAMP) -- ignore most recent week.
order by YearMon

fork query

edited jun 2 15

created jan 21 14 rolfl