Tag Answer Times

1

Please login or register to vote for this query.

(click on this box to dismiss)

Code Review

Q&A for peer programmer code reviews

declare @TagAnswers table (TagId int, PostCount int);

insert into @TagAnswers (TagId, PostCount)
  select TagId, count(*)
  from (
      select pt.TagId
      from Posts a
        join Posts q on a.ParentId = q.Id
        join PostTags pt on q.Id = pt.PostId
      where a.PostTypeId = 2 -- Answers
        and datediff(d, a.CreationDate, getdate()) < 60
    ) tagAnswers
  group by TagId;

select t.TagName, 
       case when times.AnswerTime > 23 then 0 
            else times.AnswerTime 
       end [UTC Hour],
       count(*) PostCount,
       ta.PostCount TagPosts
from (
    select pt.TagId, 
           datepart(hour, a.CreationDate) +
           case when datepart(minute, a.CreationDate) > 30 then 1
                else 0 
           end AnswerTime
    from Posts a
      join Posts q on a.ParentId = q.Id
      join PostTags pt on q.Id = pt.PostId      
    where a.PostTypeId = 2 -- Answers
    and datediff(d, a.CreationDate, getdate()) < 60
  ) times
  join Tags t on times.TagId = t.Id
  join @TagAnswers ta on t.Id = ta.TagId
group by ta.PostCount, t.TagName, AnswerTime
order by ta.PostCount desc, t.TagName, times.AnswerTime

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results