High answers per month grouped by rating

0

Please login or register to vote for this query.

(click on this box to dismiss)

High answers per month grouped by rating

Stack Overflow на русском

Вопросы и ответы для программистов

select datepart(year, uba.[Answer Creation Date]) as [Year],
 datepart(month, uba.[Answer Creation Date]) as [Month],
 count(case when uba.[Upvotes Count] = 1 then 1 else null end) as [One],
 count(case when uba.[Upvotes Count] > 1 and uba.[Upvotes Count] < 5 then 1 else null end) as [Two Plus],
 count(case when uba.[Upvotes Count] >= 5 and uba.[Upvotes Count] < 8 then 1 else null end) as [Five Plus],
 count(case when uba.[Upvotes Count] >= 8 and uba.[Upvotes Count] < 10 then 1 else null end) as [Eight Plus],
 count(case when uba.[Upvotes Count] >= 10 and uba.[Upvotes Count] < 15 then 1 else null end) as [Ten Plus],
 count(case when uba.[Upvotes Count] >= 15 then 1 else null end) as [Fifteen Plus],
 count(*) as [Total]
from (select min(a.CreationDate) as [Answer Creation Date], 
    count(*) as [Upvotes Count]
    from Votes v
    inner join Posts a on v.PostId = a.Id
    inner join Posts q on a.ParentId = q.Id 
    where v.VoteTypeId = 2
    and a.PostTypeId = 2
    and datediff(day, a.CreationDate, v.CreationDate) <= 30
    and (select count(*)
       from PostTags pt
       inner join Tags t on pt.TagId = t.Id
       where pt.PostId = q.Id
         and t.TagName = '##tagName##') > 0
    group by v.PostId) uba
group by datepart(year, uba.[Answer Creation Date]),
  datepart(month, uba.[Answer Creation Date])
order by [Year], [Month]

Enter Parameters

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