Average reputation and posts per user, broken down by user's quintile of activity

1

Please login or register to vote for this query.

(click on this box to dismiss)

Code Review

Q&A for peer programmer code reviews

WITH
    UserQuestions AS (
        SELECT Users.Id,
               COUNT(Users.Id) AS Count
            FROM Users
                JOIN Posts
                    ON Posts.OwnerUserId = Users.Id
            WHERE
                PostTypeId = 1     -- Questions
            GROUP BY Users.Id
    ),
    UserAnswers AS (
        SELECT Users.Id,
               COUNT(Users.Id) AS Count
            FROM Users
                JOIN Posts
                    ON Posts.OwnerUserId = Users.Id
            WHERE
                PostTypeId = 2     -- Answers
            GROUP BY Users.Id
    )
SELECT TOP 50
        Users.Id AS [User Link],
        Users.Reputation,
        UserQuestions.Count AS QuestionCount,
        UserAnswers.Count AS AnswerCount
    FROM Users
        LEFT OUTER JOIN UserQuestions
            ON Users.Id = UserQuestions.Id
        LEFT OUTER JOIN UserAnswers
            ON Users.Id = UserAnswers.Id
    WHERE
        Users.Id <> -1             -- Exclude 'Community' pseudo-user
        ;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results