0

Please login or register to vote for this query.

(click on this box to dismiss)

Rewrite of http://data.stackexchange.com/codereview/query/211467/average-time-to-in-seconds-to-answer-a-question-by-language-tag in response to http://codereview.stackexchange.com/questions/58668/average-time-to-first-answer. See http://codereview.stackexchange.com/a/58679/9357 for an explanation.

Q&A for professional and enthusiast programmers

declare @AnswerTime table (Id int, CreateTime date, AnswerTime date, Elapse bigint , Type nvarchar(150) ) 

insert @AnswerTime
Select Q.Id, max(Q.CreationDate) AS CreateTime, min(A.CreationDate) AS AnswerTime, 
       DATEDIFF(second, max(Q.CreationDate), min(A.CreationDate)) AS Elapse,
       substring(Q.Tags, 2, CHARINDEX('>', Q.Tags)-2) as Type
From   Posts Q, Posts A 
Where  Q.PostTypeId = 1
  and  Q.Id = A.ParentId
Group by Q.Id, Q.Tags
Order by Elapse;

select Type, count(*), min(Elapse) AS Min, max(Elapse) as Max, avg(Elapse) as Average
from   @AnswerTime
Group by Type
Order by Average ASC;

-- select * from Posts where ID=3021;

view current version

edited jul 31 14

created jul 31 14 Loki Astari