Q&A for scientists using computers to solve scientific problems
-- 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 = 3 -- 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;