Number of views per question, based on creation date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Biology Meta

Q&A about the site for biology researchers, academics, and students

DECLARE @StartDate datetime = '2023-01-01';
DECLARE @EndDate datetime = '2024-12-31';

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 main site
loading Hold tight while we fetch your results