Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
StackOverflow response times by language, grouped by hour
DECLARE @tagname varchar(20) = ##language:string##
select ResponseHour,
avg(cast(ResponseTime as bigint)) as Average,
stdev(cast(ResponseTime as bigint)) as StandardDev
from
(SELECT
Questions.CreationDate,
Questions.Title,
Tags.TagName,
Answers.CreationDate as ResponseDate,
datediff(minute, Questions.CreationDate,
Answers.CreationDate) as ResponseTime,
cast(DATEPART(hour, Questions.CreationDate) as int) as ResponseHour
FROM Posts Questions
join Posts Answers on Answers.id = Questions.AcceptedAnswerId
and datediff(hour, Questions.CreationDate, Answers.CreationDate) < 5
join PostTags ON PostTags.PostId = Questions.Id
join Tags on Tags.Id = PostTags.TagId
WHERE Questions.CreationDate > convert(datetime, '01/01/2012', 101)
and TagName = @tagname
) as Responses
group by ResponseHour
order by ResponseHour
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment