1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for peer programmer code reviews

-- Do active users of a site mainly ask or answer questions?

-- For all users of a site who have asked or answered anything,
-- count their questions and answers.
-- The number of questions + answers is their "activity".
-- Categorize users according to their quintile of activity.
-- For each quintile of users, get the average reputation,
-- the average proportion of activity that are questions, and
-- the average proportion of activity that are answers.
WITH
    UserPosts AS (
        SELECT Users.Id,
               Posts.PostTypeId,
               COUNT(Users.Id) AS Count
            FROM Users
                JOIN Posts
                    ON Posts.OwnerUserId = Users.Id
            GROUP BY Users.Id, Posts.PostTypeId
    ),
    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 UserPosts AS UserQuestions
                    ON Users.Id = UserQuestions.Id AND
                    UserQuestions.PostTypeId = 1  -- Questions
                LEFT OUTER JOIN UserPosts AS UserAnswers
                    ON Users.Id = UserAnswers.Id AND
                    UserAnswers.PostTypeId = 2    -- Answers
            WHERE
                Users.Id <> -1     -- Exclude 'Community' pseudo-user
    ),
    UserQAQuintiles AS (
        SELECT  *,
                NTILE(5) OVER (ORDER BY QACount, Reputation) AS QAQuintile
        FROM UserQA
        WHERE QACount > 0
    )
SELECT
        QAQuintile AS [Activity (#Q + #A) quintile],
        AVG(CONVERT(FLOAT, QACount)) AS [Avg #Q+#A],
        AVG(100 * QuestionCount / QACount) AS [Avg % Q],
        AVG(100 * AnswerCount / QACount) AS [Avg % A],
        AVG(Reputation) AS [Avg reputation]
    FROM UserQAQuintiles
    GROUP BY QAQuintile
    ORDER BY QAQuintile;

fork query

edited nov 19 13

created nov 18 13 200_success