4

Please login or register to vote for this query.

(click on this box to dismiss)

Top 50 users sorted by ratio of their answers that have been accepted to their total answers, adjustable by minimum total answers

Q&A for SharePoint enthusiasts

-- Top users by accept answer rate
-- Top 50 users sorted by ratio of accepted answers to total answers, adjustable by minimum total answers

WITH Answers AS (
  SELECT
    AcceptedAnswers.OwnerUserId AS UserId,
    Count(AcceptedAnswers.Id) AS AcceptedAnswers,
    (SELECT Count(Id) FROM Posts TotalAnswers WHERE TotalAnswers.OwnerUserId = AcceptedAnswers.OwnerUserId AND TotalAnswers.PostTypeId = 2) AS TotalAnswers
  FROM
    Posts AcceptedAnswers
  INNER JOIN
    Posts Questions ON Questions.AcceptedAnswerId = AcceptedAnswers.Id
  WHERE
    AcceptedAnswers.PostTypeId = 2
  GROUP BY AcceptedAnswers.OwnerUserId
)

SELECT TOP 50
  UserId AS [User Link],
  Users.Reputation AS Rep,
  (CAST(AcceptedAnswers AS FLOAT) / TotalAnswers) AS AcceptedPercentage
FROM
  Answers
INNER JOIN Users ON Users.Id = UserId
WHERE TotalAnswers >= ##MinimumAnswers##
ORDER BY AcceptedPercentage DESC

fork query

created feb 15 11 Renesis