Number of views per question, based on creation date/time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @StartDate datetime = ##StartDate##
declare @EndDate datetime = ##EndDate##

CREATE TABLE #temp (bucket datetime, average float, std float)

INSERT INTO #temp 
SELECT 
  dateadd(hour, datediff(hour, 0, creationdate), 0),
  avg(viewcount),
  stdev(viewcount)
  FROM posts
  WHERE posttypeid = 1
    AND creationdate >= @StartDate
    AND creationdate < @EndDate
  GROUP BY dateadd(hour, datediff(hour, 0, creationdate), 0)
  ORDER BY dateadd(hour, datediff(hour, 0, creationdate), 0)
  
SELECT * FROM #temp
--  dateadd(hour, datediff(hour, 0, creationdate), 0) AS 'Bucket',
--  avg(viewcount) AS 'Average view count',
--  count(*) AS 'Number of questions'
--  FROM posts INNER JOIN #temp
--   ON dateadd(hour, datediff(hour, 0, creationdate), 0) = bucket
--  WHERE posttypeid = 1
--    AND creationdate BETWEEN @StartDate AND @EndDate
--  GROUP BY dateadd(hour, datediff(hour, 0, creationdate), 0)
--  ORDER BY dateadd(hour, datediff(hour, 0, creationdate), 0)

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results