Average views per question by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Super User

Q&A for computer enthusiasts and power users

SELECT
  CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME) AS [Date],
  Count(*) AS [Count],
  Avg(p.ViewCount) AS [Avg views],
  Stdev(p.ViewCount) AS [Std dev],
  Avg(IIF(p.Score > 0, p.ViewCount, NULL)) AS [Score >0] --,
  --Avg(CASE p.ClosedDate WHEN NULL THEN NULL ELSE p.ViewCount END) AS [Open]
FROM Posts p
WHERE p.PostTypeId = 1
GROUP BY CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME)
ORDER BY CAST(CAST(DATEPART(yyyy, p.CreationDate) AS NVARCHAR(4)) + '-' + CAST(DATEPART(mm, p.CreationDate) AS NVARCHAR(2)) + '-01' AS DATETIME) DESC

Enter Parameters

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