3

Please login or register to vote for this query.

(click on this box to dismiss)

Identify how many people ask, answer, or comment each week.

Q&A about the site for Drupal developers and administrators

declare @activedate as date = DateAdd (week, - ##Weeks:int?60##, Convert(date, CURRENT_TIMESTAMP));

set @activedate = DateAdd (day, 7 - DatePart(day, @activedate), @activedate);

with WeekActionBase as (
select DateAdd(day, 7 - DatePart(weekday, CreationDate), Convert(Date, CreationDate)) as Week,
       'NewUser' as Series,
       Id as UserId
from Users
where CreationDate >= @activedate

UNION ALL

select distinct
       DateAdd(day, 7 - DatePart(weekday, CreationDate), Convert(Date, CreationDate)) as Week,
       case when PostTypeId = 1 then 'Askers' else 'Answerers' end as Series,
       OwnerUserId as UserId
from Posts
where PostTypeId in (1, 2)
  and CreationDate >= @activedate

UNION ALL

select distinct
       DateAdd(day, 7 - DatePart(weekday, CreationDate), Convert(Date, CreationDate)) as Week,
       'Commenters' as Series,
       UserId
from Comments
where CreationDate >= @activedate

), WeekAction as (

select Week, Series, UserId
from WeekActionBase
UNION ALL
select distinct Week, 'Combined', UserId
from WeekActionBase

)



select Week, Series, Count(*)
from WeekAction
where Week < CURRENT_TIMESTAMP
group by Week, Series
order by Week

fork query

edited jun 12 14

created feb 28 14 rolfl