Top users with answers grouped by rating

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

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

select datepart(year, uba.[Answer Creation Date]) as [Year],
 datepart(month, uba.[Answer Creation Date]) as [Month],
 uba.[Owner],
 count(case when uba.[Upvotes Count] < 8 then 1 else null end) as [Below Eight],
 count(case when uba.[Upvotes Count] >= 8 and uba.[Upvotes Count] < 10 then 1 else null end) as [Below Ten],
 count(case when uba.[Upvotes Count] >= 10 and uba.[Upvotes Count] < 15 then 1 else null end) as [Below Fifteen],
 count(case when uba.[Upvotes Count] >= 15 then 1 else null end) as [Over Fifteen]
from (select min(a.CreationDate) as [Answer Creation Date], 
    count(*) as [Upvotes Count],
    min(a.OwnerUserId) as [Owner]
    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 v.CreationDate >= '2015-01-01'
    and a.CreationDate < dateadd(day, -30, getdate())
    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
where uba.[Upvotes Count] >= 5
group by datepart(year, uba.[Answer Creation Date]),
  datepart(month, uba.[Answer Creation Date]),
  uba.[Owner]
order by [Year], [Month]

Enter Parameters

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