Distinct Contributors per week

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.

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results