Questions per day (n day moving average)

3

Please login or register to vote for this query.

(click on this box to dismiss)

Economics

Q&A for those who study, teach, research and apply economics and econometrics

-- cf. https://stackoverflow.com/a/1478978/4751173
SELECT TOP 50000 IDENTITY(INT, 1, 1) AS Number INTO #numbers
  FROM [StackOverflow].dbo.Posts;
ALTER TABLE #numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number);

CREATE TABLE #temp (Date DATETIME2, Total FLOAT);
INSERT INTO #temp
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0), COUNT(*)
  FROM PostsWithDeleted
  WHERE PostTypeId = 1 -- Questions
    AND CreationDate >=
    DATEADD(DAY, -##numberOfDaysPreceding:int?6##, ##fromDate:string?2016-01-01##)
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0)
-- numberOfDaysPreceding: 'n-1', so use 6 for a weekly average. I didn't feel like implementing https://stackoverflow.com/a/29695951/4751173

DECLARE @StartDate DATETIME2, @EndDate DATETIME2;
SELECT @StartDate = MIN(Date) FROM #temp;
SELECT @EndDate = MAX(Date) FROM #temp;

WITH Temp AS (
  SELECT DATEADD(DAY, Number - 1, @StartDate) AS [Date],
    COALESCE(#temp.Total, 0) AS [Total]
    FROM #numbers
    LEFT OUTER JOIN #temp ON DATEADD(DAY, Number - 1, @StartDate) = #temp.Date
    WHERE Number <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
)  
SELECT Date,
  AVG(Total) OVER (ORDER BY Date ASC ROWS ##numberOfDaysPreceding## PRECEDING)
  FROM Temp
  ORDER BY Date
  OFFSET ##numberOfDaysPreceding## ROWS;

DROP TABLE #temp;
DROP TABLE #numbers;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results