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.

Open Source

Q&A for people organizing, marketing or licensing open source development projects

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 meta site
loading Hold tight while we fetch your results