Votes per post aging

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 agg_votes as
(
select
  YEAR(v.CreationDate) VoteDate
, YEAR(p.CreationDate) PostDate
, count(*) votes
from Posts p
join Votes v on v.PostId = p.Id
where p.PostTypeId in (1, 2)
and   v.VoteTypeId in (2, 3)
-- and   p.OwnerUserId = 635608
group by YEAR(v.CreationDate), YEAR(p.CreationDate)
), cnt_posts as 
(
select YEAR(CreationDate) y, count(*) c
from Posts
where PostTypeId in (1, 2)
-- and OwnerUserId = 635608
group by YEAR(CreationDate)
), foobar as (
select VoteDate, PostDate
, sum(votes) over (partition by PostDate order by VoteDate, PostDate range between unbounded preceding and current row) as AccumulatedVotes
, (select c from cnt_posts where y = PostDate) as PostsThatYear
from agg_votes
)
select PostDate, VoteDate, AccumulatedVotes, PostsThatYear
, CONVERT(DECIMAL(5,2), 1.0*AccumulatedVotes/PostsThatYear) as VotesPerPost
from foobar
order by PostDate, VoteDate

Enter Parameters

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