Q&A about the site for science fiction and fantasy enthusiasts
With a as ( Select p.Id, cast(DATEPART(YYYY, p.CreationDate) as char) TimeYr, p.PostTypeId, p.Score, VoteTypeId, round(1.00*DATEDIFF(day, p.CreationDate, v.CreationDate)/30,0) delay from Votes v join Posts p on p.Id = v.PostId where p.PostTypeId in (1,2) and VoteTypeId in (2,3) ), b as ( select Id, TimeYr, PostTypeId, Score, VoteTypeId, delay, SUM(case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end) SumScore from a group by Id, Timeyr, PostTypeId, Score, VoteTypeId, delay ), c as ( Select TimeYr, count(distinct Id) nyear from b where PostTypeId = 1 group by TimeYr ) Select delay, b.TimeYr, log(SUM(1.00*SumScore/(nyear*1.00)),10) avgscore from b join c on b.TimeYr = c.TimeYr where PostTypeId = 1 and delay <= 36 and Cast(b.TimeYr as int) in (2010,2011,2012,2015) group by delay,b.TimeYr order by TimeYr, delay