Questions per day (n day moving average)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

CREATE TABLE #temp (Date DATETIME2, Total FLOAT);
  
INSERT INTO #temp
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0), COUNT(*)
  FROM Posts
  WHERE PostTypeId = 1 -- Questions
    AND CreationDate >= ##fromDate:string?2016-01-01##
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0)
  ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0);
  
SELECT Date,
  AVG(Total) OVER (ORDER BY Date ASC ROWS ##numberOfDays:int?30## PRECEDING)
  FROM #temp;

DROP TABLE #temp;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results