Votes per post by month since August 16, 2011

0

Please login or register to vote for this query.

(click on this box to dismiss)

Signal Processing

Q&A for practitioners of the art and science of signal, image and video processing

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 >= '8-16-2011'
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,2,3) -- Accepted answer, upvote, downvote.
group by DATEDIFF(month, getdate(), v.CreationDate)
)

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

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