Q&A for people looking to improve their interpersonal communication skills
SELECT cdate, avg(apq) over(ORDER BY cdate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as [Average number of answers for questions created at date, 30 day rolling avg], avg(apoq) over(ORDER BY cdate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as [Average number of answers for questions created at date if question still open, 30 day rolling avg] FROM (SELECT CAST(CreationDate AS DATE) AS cdate, AVG(CAST(AnswerCount AS FLOAT)) AS apq, CAST(SUM(AnswerCount) AS FLOAT)/NULLIF(SUM(CASE WHEN ClosedDate IS NULL THEN 1 ELSE 0 END),0) AS apoq FROM Posts WHERE ParentId IS NULL AND CreationDate > '2017-08-01' GROUP BY CAST(CreationDate AS DATE) ) foo ORDER BY cdate