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 people looking to bypass life's everyday problems with simple tricks

with Activity as (

    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon,
           'NewUser' as Series,
           count(*) as Action
    from Users u
    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,
       case when v.VoteTypeId = 1 then 'Accept'
            when p.PostTypeId = 1 then 'QuestionVote'
            else 'AnswerVote'
       end as Series,
       count(*) as Action
    from Posts p,
         Votes v
    where p.Id = v.PostId
      and v.VoteTypeId in (1, 2, 3)
    group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate),
          case when v.VoteTypeId = 1 then 'Accept'
               when p.PostTypeId = 1 then 'QuestionVote'
               else 'AnswerVote'
          end
  
  UNION
  
    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon,
       pt.Name as Series,
       count(*) as Action
    from Posts p, PostTypes pt
    where p.PostTypeId in (1, 2)
      and p.PostTypeId = pt.Id
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)),
          pt.Name
  
  UNION
    
    SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate)) as YearMon,
        'Comment' as Series,
        count(*) as Action
    from Comments c
    Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,c.CreationDate)), Convert(DATE, c.CreationDate))
)
select *
from Activity
pivot (
    sum(Action)
    for Series in ([NewUser],[Question],[Answer],[Comment],[QuestionVote],[AnswerVote],[Accept]))
    as Pivoted
where YearMon >= DATEADD(WEEK, -60, CURRENT_TIMESTAMP) -- previous 60 weeks.
  and YearMon < CURRENT_TIMESTAMP -- ignore most recent week.
order by YearMon

view current version

edited feb 2 14

created jan 21 14 rolfl