Votes per post by month


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with posts_by_month as (
select cast (dateadd(month, DATEDIFF(month, getdate(), p.CreationDate), getdate()) as DATE) Month, 
       count(*) post_count
from Posts p 
where p.CreationDate >= '1-1-2009'
AND PostTypeId = 1
group by DATEDIFF(month, getdate(), p.CreationDate)

votes_by_month as(
select cast (dateadd(month, DATEDIFF(month, getdate(), v.CreationDate), getdate()) as DATE) Month, 
       count(*) vote_count
from Votes v 
where VoteTypeId in (1) -- Accepted answer, upvote, downvote.
group by DATEDIFF(month, getdate(), v.CreationDate), VotetypeId

select v.Month, cast(vote_count as float)/post_count--, post_count, vote_count
from posts_by_month p
     join votes_by_month v on p.Month = v.Month
order by v.Month

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results