0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jan 3 19

created jan 3 19 anonymous