1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

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


WITH UserAnswerStats AS (
  SELECT
    Answers.OwnerUserId AS UserId,
    Sum(CASE WHEN AcceptingQuestions.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END) AS AcceptedAnswers,
    Count(*) AS TotalAnswers
  FROM Posts Answers
  LEFT JOIN Posts AcceptingQuestions ON AcceptingQuestions.AcceptedAnswerId = Answers.Id
  WHERE Answers.PostTypeId = 2
  GROUP BY Answers.OwnerUserId
),

UserActivityStats AS (
  SELECT
    COALESCE(PostInfoByUser.UserId, CommentInfoByUser.UserId) AS UserId,
    (SELECT Max(LastCreationDate) FROM (VALUES
      (PostInfoByUser.LastCreationDate),
      (CommentInfoByUser.LastCreationDate)
    ) MaxValues) AS LastActivityDate
  FROM (
    SELECT
      OwnerUserId AS UserId,
      Max(CreationDate) AS LastCreationDate
    FROM Posts
    GROUP BY OwnerUserId
  ) PostInfoByUser
  FULL JOIN (
    SELECT
      UserId,
      Max(CreationDate) AS LastCreationDate
    FROM Comments
    GROUP BY UserId
  ) CommentInfoByUser ON PostInfoByUser.UserId = CommentInfoByUser.UserId
)

SELECT TOP 50
  Users.Id AS [User Link],
  Users.Reputation AS Rep,
  (CAST(AcceptedAnswers AS FLOAT) / TotalAnswers) AS AcceptedPercentage,
  LastActivityDate
FROM Users
JOIN UserAnswerStats ON UserAnswerStats.UserId = Users.Id
JOIN UserActivityStats ON UserActivityStats.UserId = Users.Id
WHERE TotalAnswers >= ##MinimumAnswers:int##
  AND LastActivityDate >= TRY_PARSE(##MinimumActivityDate:string## AS DATE USING 'en-us')
ORDER BY AcceptedPercentage DESC

fork query

edited jan 5 17 jnm2

created sep 12 14 J. Musser