Questions per day with 7 day moving average

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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?2018-01-01##
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0)
  ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0);
  
SELECT Date, Total,
  AVG(Total) OVER (ORDER BY Date ASC ROWS 7 PRECEDING)
  FROM #temp;

DROP TABLE #temp;

Enter Parameters

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