## Daily site activity graph

5

(click on this box to dismiss)

Q&A for those involved in the field of teaching Computer Science

``````-- 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 *
)

-- 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``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached