Q&A for linguists, teachers and learners wanting to discuss the finer points of the Portuguese language
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 (2016) ), c as ( Select DATEPART(YYYY, CreationDate) TimeYr, count(Id) nyear from Posts where PostTypeId = 1 group by DATEPART(YYYY, CreationDate) ), d as ( Select round(1.00*delay/30,0) mdelay, TimeYr, SUM(VoteS) sumscore from a group by PostTypeId, round(1.00*delay/30, 0), TimeYr ) Select d.mdelay, CAST(d.TimeYr as Char) year, d.sumscore, c.nyear, (d.sumscore/c.nyear) averagescore from d join c on d.TimeYr = c.TimeYr where d.mdelay> 0 and d.mdelay <= 36 order by d.TimeYr, d.mdelay