Q&A for professionals and students of engineering
-- Time series of the number of active experts per week -- Where "active expert" is someone who has posted more than one upvoted answer. -- Per Deer Hunter's definition in http://meta.stackexchange.com/a/261608/ -- -- X-axis is in years, so the point at 2015.0 is the first week of 2015. DECLARE @tag nvarchar(25) SET @tag = LOWER('##tagName##') select YR_WK, count(*) as NumActiveExperts from ( select (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0) as YR_WK , u.id as UID , count (*) as NumAnswers from users u, posts p, posts q, posttags pt, tags t where p.owneruserid = u.id and p.posttypeid = 2 and p.score > 0 and p.ParentId = q.id and q.posttypeid = 1 and pt.postid=q.id and t.id = pt.tagid and t.tagname=@tag group by (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0) , u.id ) AnsPerUPerWk where NumAnswers >= ##ExpertNumAnswersPerWeekThreshold:int?2## group by YR_WK order by YR_WK asc