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 QuestionCount
            FROM Users
                JOIN Posts
                    ON Posts.OwnerUserId = Users.Id
            WHERE
                PostTypeId = 1 AND -- Questions
                Users.Id <> -1     -- Exclude 'Community' pseudo-user
            GROUP BY Users.Id
    ),
    UserAnswers AS (
        SELECT Users.Id,
               COUNT(Users.Id) AS AnswerCount
            FROM Users
                JOIN Posts
                    ON Posts.OwnerUserId = Users.Id
            WHERE
                PostTypeId = 2 AND -- Answers
                Users.Id <> -1     -- Exclude 'Community' pseudo-user
            GROUP BY Users.Id
    )
SELECT TOP 50 Users.Id AS [User Link]

    FROM Users
        LEFT OUTER JOIN UserQuestions
            ON Users.Id = UserQuestions.Id
        LEFT OUTER JOIN UserAnswers
            ON Users.Id = UserAnswers.Id
    GROUP BY Users.Id
    ORDER BY Users.Id;

Enter Parameters

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