0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and students of the Korean language

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 DISTINCT 
        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;

view current version

edited nov 2

created nov 2 jon.doe46164