Top Answerers for Tag: score = sum of scores with a cap score per answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Top 20 users for a given tag, ordered by descending score with
--   score = sum of scores for all answers for ##TagName##
--             with a ##Cap## to each answer

-- TagName: Which tag to list Top Answerers for
-- Cap: Maximum score to earn for a single answer (int)

DECLARE @Cap int = ##Cap:int##

SELECT TOP 20
    P.OwnerUserId AS [User Link],
    SUM(
      CASE WHEN P.Score < @Cap THEN P.Score ELSE @Cap END
    ) AS TotalScore,
    COUNT(*) AS '#Answers'
FROM Posts P
    INNER JOIN PostTags PT on PT.PostId = P.ParentId
    INNER JOIN Tags T on T.Id = PT.TagId
WHERE
    T.TagName = '##TagName##' AND
    P.OwnerUserId IS NOT null
    
GROUP BY P.OwnerUserId
ORDER BY TotalScore DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results