Skip to content

Instantly share code, notes, and snippets.

@guilespi
Created October 31, 2012 01:42
Show Gist options
  • Save guilespi/3984320 to your computer and use it in GitHub Desktop.
Save guilespi/3984320 to your computer and use it in GitHub Desktop.
How many language questions are in each difficulty category
DECLARE @tagname varchar(20) = ##language:string##
select DifficultyGroup, Count(1) Total,
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,
CASE
WHEN DateDiff(hour, Questions.CreationDate, Answers.CreationDate) < 5 THEN 'Easy'
WHEN DateDiff(hour, Questions.CreationDate, Answers.CreationDate) < 24 THEN 'Medium'
WHEN DateDiff(hour, Questions.CreationDate, Answers.CreationDate) < 144 THEN 'Hard'
ELSE 'Hell'
END as DifficultyGroup
FROM Posts Questions
join Posts Answers on Answers.id = Questions.AcceptedAnswerId
join PostTags ON PostTags.PostId = Questions.Id
join Tags on Tags.Id = PostTags.TagId
WHERE Questions.CreationDate > convert(datetime, '01/01/2011', 101)
and TagName = @tagname
) as Responses
group by DifficultyGroup
order by DifficultyGroup
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment