Evolution of questions and answer wait time for experienced users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

;with first_cte as (
  SELECT q.Id, q.CreationDate AS QuestionCreationDate,
    MIN(a.CreationDate) AS MinAnswerCreationDate
  FROM Posts AS q
    JOIN Posts AS a on a.ParentId = q.Id and a.Score > 1 
	INNER JOIN Users qu ON qu.Id = q.OwnerUserId
  where q.CreationDate >= '20100101' and a.CreationDate >= '20100101'
    and q.PostTypeId = 1
	and qu.Reputation >= 200
  GROUP BY q.Id, q.CreationDate
),
ym_cte as (
  SELECT YEAR(q.QuestionCreationDate) * 100 + MONTH(q.QuestionCreationDate) AS YM,
    q.Id, q.QuestionCreationDate,
    CAST(DATEDIFF(minute, q.QuestionCreationDate, q.MinAnswerCreationDate) AS BIGINT) AS AnswerTime
  FROM first_cte AS q)
SELECT
  YM,
  SUM(AnswerTime) / COUNT(1) AS avgTime,
  COUNT(1) AS Cnt
FROM ym_cte as q
GROUP BY YM
ORDER BY YM

Enter Parameters

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