Top Answerers for Tag: score = sum of answer scores/viewCount

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##

-- TagName: Which tag to list Top Answerers for (string)

SELECT --TOP 20
    P.OwnerUserId AS [User Link],
    SUM(1000*P.Score/Q.ViewCount) AS TotalScore,
    SUM(P.Score) AS TotalVotes,
    SUM(Q.ViewCount) AS TotalViews,
    COUNT(*) AS '#Answers'
FROM Posts P
    INNER JOIN Posts Q ON Q.Id = P.ParentId
    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