0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and scholars of mythology and folklore

SELECT TOP 500
    Users.Id AS UserId,                   -- User ID
    Users.DisplayName,                    -- Username
    Users.Reputation,                     -- Reputation score
    Users.CreationDate,                   -- Account creation date
    Users.LastAccessDate,                 -- Last access date
    Users.UpVotes,                        -- Number of upvotes received
    Users.DownVotes,                      -- Number of downvotes received
    Posts.QuestionCount,                  -- Number of questions asked
    Posts.AnswerCount,                    -- Number of answers provided
    Posts.CommentCount                    -- Number of comments posted
FROM Users
LEFT JOIN (
    SELECT 
        OwnerUserId,
        COUNT(CASE WHEN PostTypeId = 1 THEN 1 END) AS QuestionCount,  -- Count of questions
        COUNT(CASE WHEN PostTypeId = 2 THEN 1 END) AS AnswerCount,    -- Count of answers
        SUM(CommentCount) AS CommentCount                            -- Total comments
    FROM Posts
    GROUP BY OwnerUserId
) AS Posts
ON Users.Id = Posts.OwnerUserId
WHERE Users.Reputation IS NOT NULL          -- Ensure reputation data exists
  AND Users.CreationDate IS NOT NULL        -- Ensure creation date exists
ORDER BY Users.Reputation DESC              -- Sort by reputation (descending)
                              -- Adjust to retrieve more/less users

fork query

edited nov 19

created nov 19 jon.doe46333