SELECT cdate , sum(vc) over(ORDER BY cdate ROWS BETWEEN 1...

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace

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

SELECT 
  cdate
,     
  sum(vc) over(ORDER BY cdate ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) /
  sum(nq) over(ORDER BY cdate ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) 
  as [Number of views per question created at date, 14 day rolling avg]

FROM (SELECT
    CAST(CreationDate AS DATE) AS cdate,
    0.0+COUNT(*) AS nq,
    0.0+SUM(ViewCount) AS vc
    FROM Posts
  WHERE PostTypeId = 1 AND CreationDate > '2017-08-01' AND ClosedDate IS NULL
  GROUP BY CAST(CreationDate AS DATE)
  ) foo
ORDER BY cdate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results