Top Answerers by Median Score

1

Please login or register to vote for this query.

(click on this box to dismiss)

(Inefficiently) lists users with at least 20 answers having a median score of 2 or more

SharePoint

Q&A for SharePoint enthusiasts

-- Top Answerers by Median Score
-- (Inefficiently) lists users with at least 20 answers having a median score of 3 or more

SELECT
  OwnerUserId AS [User Link],
  Answers,
  CASE WHEN Answers % 2 = 1
    THEN LowerTileMax
    ELSE (LowerTileMax + UpperTileMin)/2.0
    END AS [Median Score]
FROM
  (SELECT
     OwnerUserId,
     COUNT(Score) AS Answers,
     MAX(CASE WHEN Tile = 1 THEN Score END) AS LowerTileMax,
     MIN(CASE WHEN Tile = 2 THEN Score END) AS UpperTileMin
   FROM
     (SELECT
        OwnerUserId,
        Score,
        NTILE(2) OVER(PARTITION BY OwnerUserId ORDER BY Score) AS Tile
      FROM Posts
      WHERE PostTypeId = 2
        AND CommunityOwnedDate IS NULL
        AND ClosedDate IS NULL) T
   GROUP BY T.OwnerUserId) G
WHERE Answers > 20
  AND ( (Answers % 2 = 1 AND LowerTileMax >= 2)
        OR (Answers % 2 = 0 AND (LowerTileMax + UpperTileMin)/2.0 >= 2) )
ORDER BY [Median Score] DESC, Answers DESC

Enter Parameters

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