Site Activity and VoteGraph

53

(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 user experience researchers and experts

``````-- 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
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,
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,
count(*) as Action
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 (
[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``````

Enter Parameters

Options:
-
Switch to main site
Hold tight while we fetch your results
:records returned in :time ms:cached