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;