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 about the site for finance professionals and academics

with Activity as (
    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate) as YearMon,
       case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name as Series,
       count(*) as Action
    from Posts p,
         Votes v,
         VoteTypes vt
    where p.Id = v.PostId
      and v.VoteTypeId = vt.Id
    group --
       by DATEADD(DAY, 7 - DATEPART(WEEKDAY, v.CreationDate), v.CreationDate),
          case when p.PostTypeId = 1 then 'Q' else 'A' end + vt.Name
  UNION
    select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)) as YearMon,
       case when p.PostTypeId = 1 then 'Questions' else 'Answers' end as Series,
       count(*) as Action
    from Posts p
    where p.PostTypeId in (1, 2)
    Group -- fix
       by DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,p.CreationDate)), Convert(DATE, p.CreationDate)),
          case when p.PostTypeId = 1 then 'Questions' else 'Answers' end
)
select *
from Activity
pivot (
    sum(Action)
    for Series in ([Questions],[Answers],[QUpMod],[QDownMod],[QFavorite],[AUpMod],[ADownMod],[AAcceptedByOriginator]))
    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 jan 21 14

created jan 21 14 rolfl