My median and average answer score at given date

3

Please login or register to vote for this query.

(click on this box to dismiss)

Reports median and average answer score. The MedianAnswerScore2 is more precise version of median.

Ask Ubuntu Meta

Q&A about the site for Ubuntu users and developers

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 ;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results