Votes and posts per year

0

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 votes_agg as (
select YEAR(CreationDate) y, count(*) c
from Votes
where VoteTypeId in (2, 3)
group by YEAR(CreationDate)
), posts_agg as (
select YEAR(CreationDate) y, count(*) c
from Posts
where PostTypeId in (1, 2)
group by YEAR(CreationDate)
)
select 
  votes_agg.y "Year"
, votes_agg.c "Votes"
, posts_agg.c "Posts"
, CONVERT(DECIMAL(4,2), 1.0*votes_agg.c/posts_agg.c) "Ratio"
from votes_agg
join posts_agg on posts_agg.y = votes_agg.y
order by votes_agg.y

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results