0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

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

fork query

edited oct 24

created oct 24 anonymous