Create a gist now

Instantly share code, notes, and snippets.

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