Q&A about the site for serious players and enthusiasts of chess
-- Displays a sliding average of questions, answers, and up/down votes per day -- on a site. -- -- Parameters: -- -- Days Number of days to look back (relative to data dump date). -- VScale Divide vote counts by this number (for graph scaling). -- WindowSize Number of days (in addition to current) to include in -- sliding average window (6 is a week, because 6+current = 7). -- Set to 0 to see actual values instead of averages. -- -- http://stackexchange.com/users/305991/jason-c DECLARE @LastDate datetime SELECT @LastDate = MAX(CreationDate) FROM PostHistory; -- Hack to generate list of dates so we can insert dates with 0 items -- into query below so that they're reflected accurately on the graph and -- in sliding averages. Note we can't use recursion as max limit is 100 -- on sede. WITH DatesOfInterest AS ( SELECT CONVERT(DATE, DATEADD(DAY, 1 - x.N, @LastDate)) D, y.W FROM (SELECT TOP ##Days?365## ROW_NUMBER() OVER (ORDER BY Id) N FROM PostHistory) x CROSS JOIN (SELECT * FROM (VALUES ('Questions'), ('Answers'), ('Votes / ' + STR(##VScale##))) z(W)) y ) -- This query computes the sliding stats. SELECT D [Date], W [What], AVG(N) OVER (PARTITION BY W ORDER BY D ROWS ##WindowSize?3## PRECEDING) AvgPerDay FROM ( -- This query adds 0's back in to daily events. SELECT COALESCE(daily.D, doi.D) D, COALESCE(daily.W, doi.W) W, COALESCE(daily.N, 0) N FROM DatesOfInterest doi LEFT OUTER JOIN ( -- This query counts daily events. SELECT CONVERT(DATE, CreationDate) D, 'Questions' W, 1.0 * COUNT(*) N FROM Posts WHERE PostTypeId = 1 AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days## GROUP BY CONVERT(DATE, CreationDate) UNION SELECT CONVERT(DATE, CreationDate) D, 'Answers' W, 1.0 * COUNT(*) N FROM Posts WHERE PostTypeId = 2 AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days## GROUP BY CONVERT(DATE, CreationDate) UNION SELECT CONVERT(DATE, CreationDate) D, 'Votes / ' + STR(##VScale##) W, 1.0 * COUNT(*) / ##VScale?7## N FROM Votes WHERE VoteTypeId IN (2, 3) AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days## GROUP BY CONVERT(DATE, CreationDate) ) daily ON doi.D = daily.D AND doi.W = daily.W ) stats ORDER BY [Date], -- Custom sort order for the graph based on what I think looks good CASE W WHEN 'Answers' THEN 1 WHEN 'Questions' THEN 2 ELSE 0 END