0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals, academics, and students working within the systems development life cycle

WITH CorrectAnswers AS (
    SELECT Answers.OwnerUserId, COUNT(*) AS CorrectAnswerCount
    FROM Posts AS Answers
    JOIN Posts AS Questions ON Answers.Id = Questions.AcceptedAnswerId
    WHERE Answers.PostTypeId = 2
    GROUP BY Answers.OwnerUserId
),
UserComments AS (
    SELECT UserId, COUNT(DISTINCT PostId) AS UniqueCommentedPosts
    FROM Comments
    GROUP BY UserId
    HAVING COUNT(DISTINCT PostId) >= 10
)
SELECT TOP 10 
    Users.Id AS UserId, 
    Users.DisplayName,
    ISNULL(CorrectAnswers.CorrectAnswerCount, 0) AS CorrectAnswerCount,
    ISNULL(UserComments.UniqueCommentedPosts, 0) AS UniqueCommentedPosts
FROM Users
LEFT JOIN CorrectAnswers ON Users.Id = CorrectAnswers.OwnerUserId
LEFT JOIN UserComments ON Users.Id = UserComments.UserId
WHERE Users.CreationDate <= DATEADD(MONTH, -6, GETDATE())
  AND ISNULL(CorrectAnswers.CorrectAnswerCount, 0) > 0
ORDER BY CorrectAnswerCount DESC;

fork query

created nov 2 anonymous