Q&A for power users of web applications
-- 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.
UserPosts AS (
COUNT(Users.Id) AS Count
ON Posts.OwnerUserId = Users.Id
GROUP BY Users.Id, Posts.PostTypeId
UserQA AS (
Users.Id AS Id,
ISNULL(UserQuestions.Count, 0) + ISNULL(UserAnswers.Count, 0) AS QACount,
ISNULL(UserQuestions.Count, 0) AS QuestionCount,
ISNULL(UserAnswers.Count, 0) AS AnswerCount
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
Users.Id <> -1 -- Exclude 'Community' pseudo-user
UserQAQuintiles AS (
NTILE(5) OVER (ORDER BY QACount, Reputation) AS QAQuintile
WHERE QACount > 0
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]
GROUP BY QAQuintile
ORDER BY QAQuintile;