Q&A about the site for musicians, students, and enthusiasts
WITH UserComments AS ( SELECT C.UserId, COUNT(DISTINCT C.PostId) AS CommentCount FROM Comments C GROUP BY C.UserId HAVING COUNT(DISTINCT C.PostId) >= 10 ), CorrectAnswers AS ( SELECT A.OwnerUserId AS UserId FROM Posts Q JOIN Posts A ON Q.AcceptedAnswerId = A.Id WHERE Q.PostTypeId = 1 AND A.PostTypeId = 2 ) SELECT U.Id, U.DisplayName, COUNT(CA.UserId) AS CorrectAnswerCount FROM Users U JOIN CorrectAnswers CA ON U.Id = CA.UserId JOIN UserComments UC ON U.Id = UC.UserId WHERE U.CreationDate < DATEADD(month, -6, GETDATE()) GROUP BY U.Id, U.DisplayName ORDER BY CorrectAnswerCount DESC;