I was inspired to write a query that produced a graph mapping the perceived quality of your Stack Exchange answers over time.... in other words, "Are your answers getting better, or worse?"
Because answer scores are very 'spiky', it seems natural to calculate a sliding average with a configurable window, to show trends.
The result I have is:
And this is the SEDE query.
with Rolling as ( Select Rank() over (order by CreationDate Asc) as Answer, CreationDate, Convert(float, Score) as Score from Posts where PostTypeId = 2 and OwnerUserId = ##UserId:int## ) select Master.CreationDate, Master.Score as [Score], Avg(Slave.Score) as [RollingAvg] from Rolling as Master, Rolling as Slave where Slave.Answer > Master.Answer - ##RollingAvg:int?10## and Slave.Answer <= Master.Answer group by Master.CreationDate, Master.Score order by Master.CreationDate
I am particularly interested to know whether the rolling average can be computed in a more SQL Server friendly way. There does not appear to be a performance problem, but the CTE and self join seem.... ugly.
Any other observations?