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 = '2016-02-01';
DECLARE @EndDate datetime = '2016-02-15';

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 BETWEEN @StartDate AND @EndDate
  GROUP BY dateadd(hour, datediff(hour, 0, creationdate), 0)
  ORDER BY dateadd(hour, datediff(hour, 0, creationdate), 0)
  
SELECT
  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
    AND viewcount BETWEEN average - 2 * std AND average + 2 * std
  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