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
    ),
    QACount AS (
        SELECT
                Users.Id AS Id,
                Users.Reputation,
                ISNULL(UserQuestions.Count, 0) AS QuestionCount,
                ISNULL(UserAnswers.Count, 0) 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
    )
SELECT
        QACount.Id AS [User Link],
        *
    FROM QACount;

Enter Parameters

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