Q&A about the site for members of the workforce navigating the professional setting
-- Step 1: Select users whose accounts are older than 6 months WITH ActiveUsers AS ( SELECT Id FROM Users WHERE CreationDate < DATEADD(MONTH, -6, GETDATE()) -- Limit the number of users to process to avoid timeouts ORDER BY Id OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY ), -- Step 2: Find users who have commented on at least 10 unique posts UsersWithComments AS ( SELECT UserId, COUNT(DISTINCT PostId) AS UniqueCommentedPosts FROM Comments WHERE UserId IN (SELECT Id FROM ActiveUsers) GROUP BY UserId HAVING COUNT(DISTINCT PostId) >= 10 ), -- Step 3: Count the number of correct answers (score > 0) for each user CorrectAnswers AS ( SELECT OwnerUserId, COUNT(*) AS CorrectAnswerCount FROM Posts WHERE PostTypeId = 2 AND Score > 0 AND OwnerUserId IN (SELECT UserId FROM UsersWithComments) GROUP BY OwnerUserId ) -- Step 4: Join the filtered results to get the leaderboard, limiting the number of rows SELECT TOP 50 u.Id AS UserId, ISNULL(a.CorrectAnswerCount, 0) AS CorrectAnswers, ISNULL(c.UniqueCommentedPosts, 0) AS UniqueCommentedPosts FROM ActiveUsers u JOIN UsersWithComments c ON u.Id = c.UserId LEFT JOIN CorrectAnswers a ON u.Id = a.OwnerUserId ORDER BY a.CorrectAnswerCount DESC;