5

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers, students and Spanish language enthusiasts in general wanting to discuss the finer points of the language

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

fork query

edited apr 3 17

created apr 3 17 anonymous