## Answers per day (n day moving average)

0

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

-- cf. https://stackoverflow.com/a/1478978/4751173
SELECT TOP 50000 IDENTITY(INT, 1, 1) AS Number INTO #numbers
FROM sys.objects s1 CROSS JOIN sys.objects s2;
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
AND CreationDate >=
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 main site
Hold tight while we fetch your results
:records returned in :time ms:cached