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
,     
  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

fork query

edited jan 3 19

created jan 3 19 anonymous