0

Please login or register to vote for this query.

(click on this box to dismiss)

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 = 2 -- Answers
    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;

fork query

edited oct 2 23 anonymous

created jul 20 20 Glorfindel