Answer speed by question posting hour (absolute)

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

WITH Dates AS (
SELECT q.CreationDate AS CreationDate,
  DATEDIFF(SECOND, q.CreationDate, a1.CreationDate) AS SecondsBeforeFirstAnswer
  FROM Posts AS q
  LEFT OUTER JOIN Posts AS a1
    ON a1.ParentId = q.Id
  LEFT OUTER JOIN Posts AS a2
    ON a2.ParentId = q.Id
   AND a2.CreationDate < a1.CreationDate
  WHERE q.PostTypeId = 1 -- Questions
    AND a2.Id IS NULL
)
SELECT DATEPART(HOUR, CreationDate),
  COUNT(*) AS 'All questions',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 900 THEN 1 ELSE 0 END) AS 'Answer <15m',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 1800 THEN 1 ELSE 0 END) AS 'Answer <30m',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 3600 THEN 1 ELSE 0 END) AS 'Answer <1h',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 7200 THEN 1 ELSE 0 END) AS 'Answer <2h',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 10800 THEN 1 ELSE 0 END) AS 'Answer <3h',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 21600 THEN 1 ELSE 0 END) AS 'Answer <6h',
  SUM(CASE WHEN SecondsBeforeFirstAnswer < 86400 THEN 1 ELSE 0 END) AS 'Answer <24h'
  FROM Dates
  GROUP BY DATEPART(HOUR, CreationDate)
  ORDER BY DATEPART(HOUR, CreationDate)

Enter Parameters

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