Skip to content

Instantly share code, notes, and snippets.

@guilespi
Created October 31, 2012 01:43
Show Gist options
  • Save guilespi/3984329 to your computer and use it in GitHub Desktop.
Save guilespi/3984329 to your computer and use it in GitHub Desktop.
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