Q&A for aircraft pilots, mechanics, and enthusiasts
With a as ( Select p.Id, DATEPART(YYYY, p.CreationDate) TimeYr, p.PostTypeId, case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end VoteS, DATEDIFF(day, p.CreationDate, v.CreationDate) delay from Votes v join Posts p on p.Id = v.PostId where p.PostTypeId in (1) and v.VoteTypeId in (2,3) and DATEPART(YYYY, p.CreationDate) in (2015,2016) ), c as ( Select DATEPART(YYYY, CreationDate) TimeYr, count(Id) nyear from Posts where PostTypeId = 1 group by DATEPART(YYYY, CreationDate) ), d as ( Select floor(1.00*delay/30) mdelay, TimeYr, SUM(VoteS) sumscore from a group by floor(1.00*delay/30), TimeYr ) select Sum(VoteS) from a