3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

DECLARE @BeginDate DATETIME = (SELECT min(CreationDate) FROM PostHistory);
DECLARE @EndDate DATETIME = (SELECT max(CreationDate) FROM PostHistory);
DECLARE @Period INT = ##AveragingPeriod:int?7##;

WITH Dates AS
(
  SELECT @BeginDate AS [DateValue]
  UNION ALL
  SELECT DateValue + @Period FROM Dates WHERE DateValue + @Period < @EndDate
)

SELECT D.DateValue AS [Date], 
  (SELECT count(*) FROM Posts P WHERE P.PostTypeId = 1 AND P.CreationDate 
    BETWEEN D.DateValue AND D.DateValue + @Period) / cast(@Period AS FLOAT) AS [Questions]
FROM Dates AS D
ORDER BY D.DateValue ASC
OPTION (MAXRECURSION 0)

fork query

edited aug 18 15

created aug 14 15 Rauy