Reports median and average answer score. The MedianAnswerScore2 is more precise version of median.
Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
declare @median int; -- clasic median SELECT @median = MAX(Score) FROM (SELECT TOP 50 percent Score FROM Posts as PU WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and PU.OwnerUserId = ##UserId## and PU.CreationDate <= '##Date##' ORDER BY Score ) as t; -- improved median declare @median2_below50 int; declare @median2_total int; select @median2_below50 = ( SELECT count(*) FROM (SELECT TOP 50 percent Score FROM Posts as PU WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and PU.OwnerUserId = ##UserId## and PU.CreationDate <= '##Date##' ORDER BY Score ) as t WHERE Score = @median ) select @median2_total = (SELECT count(*) FROM Posts as PU WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and PU.OwnerUserId = ##UserId## and Score = @median and PU.CreationDate <= '##Date##' ) -- report results SELECT Count(Posts.Id) AS Answers, @median as MedianAnswerScore, @median + cast(@median2_below50 as DECIMAL(3,0)) / @median2_total AS MedianAnswerScore2, avg(cast(Score as DECIMAL(3,0))) as AverageScore FROM Posts WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and OwnerUserId = ##UserId## and CreationDate <= '##Date##' -- select @median2_below50, @median2_total ;