Q&A for those who study, teach, research and apply economics and econometrics
DECLARE @tag NVARCHAR(25) = ##Tag:string?-##, @filterByTag BIT; CREATE TABLE #QualifiedPosts ( Id int ); SELECT @filterByTag=CASE @tag WHEN '-' THEN 0 ELSE 1 END; PRINT(@filterByTag); INSERT INTO #QualifiedPosts SELECT p.Id FROM Posts p JOIN ( SELECT pt.PostId FROM PostTags pt JOIN Tags t ON t.Id = pt.TagId AND (@filterByTag = 0 OR t.TagName = @tag) GROUP BY pt.PostId ) AS FilteredTags ON FilteredTags.PostId = p.Id WHERE PostTypeId = 1; INSERT INTO #QualifiedPosts SELECT p.Id FROM Posts p JOIN ( SELECT pt.PostId FROM PostTags pt JOIN Tags t ON t.Id = pt.TagId AND (@filterByTag = 0 OR t.TagName = @tag) GROUP BY pt.PostId ) AS FilteredTags ON FilteredTags.PostId = p.ParentId WHERE PostTypeId = 2; -- Used only as x-axis datapoints WITH postcount AS ( SELECT dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime, COUNT(*) AS total FROM Posts p WHERE PostTypeId IN (1,2) GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0) ), qcount AS ( SELECT dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime, COUNT(*) AS total FROM Posts p JOIN #QualifiedPosts qp ON qp.Id = p.Id WHERE PostTypeId = 1 GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0) ), acount AS ( SELECT dateadd(day, datediff(day, 0, p.CreationDate), 0) AS datetime, COUNT(*) AS total FROM Posts p JOIN #QualifiedPosts qp ON qp.Id = p.Id WHERE PostTypeId = 2 GROUP BY dateadd(day, datediff(day, 0, p.CreationDate), 0) ), usercount AS ( SELECT dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime, COUNT(*) AS total FROM Users GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0) ), votecount AS ( SELECT dateadd(day, datediff(day, 0, v.CreationDate), 0) AS datetime, COUNT(*) AS total FROM Votes v JOIN #QualifiedPosts qp ON qp.Id = v.PostId WHERE v.VoteTypeId IN (1,2,3) GROUP BY dateadd(day, datediff(day, 0, v.CreationDate), 0) ), stats AS (SELECT p.datetime AS 'date', ISNULL(v.total, 0) AS 'votes per day', ISNULL(q.total, 0) AS 'questions per day', ISNULL(a.total, 0) AS 'answers per day', ISNULL(p.total, 0) AS 'posts per day' FROM postcount p FULL OUTER JOIN usercount u on u.datetime = p.datetime FULL OUTER JOIN votecount v on v.datetime = p.datetime FULL OUTER JOIN qcount q on q.datetime = p.datetime FULL OUTER JOIN acount a on a.datetime = p.datetime ) SELECT date, [votes per day], [questions per day], [answers per day] FROM stats WHERE stats.date >= ##StartDate:string?2014-12-16## ORDER BY stats.date