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
    ),
    UserQA AS (
        SELECT
                Users.Id AS Id,
                Users.Reputation,
                ISNULL(UserQuestions.Count, 0) + ISNULL(UserAnswers.Count, 0) AS QACount,
                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
    ),
    UserQAQuintiles AS (
        SELECT  *,
                NTILE(5) OVER (ORDER BY QACount) AS QAQuintile
        FROM UserQA
    )
SELECT
        QAQuintile,
        AVG(Reputation) AS [Average reputation],
        AVG(100 * QuestionCount / QACount) AS [% Q],
        AVG(100 * AnswerCount / QACount) AS [% A]
    FROM UserQAQuintiles
    WHERE QACount > 0
    GROUP BY QAQuintile
    ORDER BY QAQuintile;

Enter Parameters

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