Posted 5 or more times in a month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Server Fault

Q&A for system and network administrators

declare @startdate datetime = (
select top 1 dateadd(day, datediff(day, getdate(), CreationDate), getdate())
from Posts
group by datediff(day, getdate(), CreationDate)
having count(*) >= ##threshold?10##
order by datediff(day, getdate(), CreationDate)
);

declare @enddate datetime = (
select top 1 dateadd(day, datediff(day, getdate(), CreationDate), getdate())
from Posts
group by datediff(day, getdate(), CreationDate)
having count(*) >= ##threshold##
order by datediff(day, getdate(), CreationDate) desc
);

with    Posted
          as ( select   OwnerUserId
                      , dateadd(month, datediff(month, @startdate, CreationDate), @startdate) Month
                      , PostedCount = count(*)
               from     Posts
               where    CreationDate between @startdate and @enddate
                        and PostTypeId in ( 1, 2 )
                        and ClosedDate is null                   
               group by OwnerUserId, datediff(month, @startdate, CreationDate)
)

select Month, count(*)
from Posted
where PostedCount >= 5
group by Month

Enter Parameters

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