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.

Q&A for ebook publishers and readers

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 ;

fork query

edited feb 18 14

created jul 1 13 Tomas