Accept ratio and number of questions in NTILE(20), with corresponding averages. - include only non-closed questions - include only non-community owned questions - include only questions with answers - include only questions with non-negative score - only count users with 5 or more such questions
Q&A for professional and enthusiast programmers
WITH ratioInformation AS ( SELECT DISTINCT Users.Id UserId ,COUNT(AcceptedAnswerId) NumberOfAcceptedAnswers ,COUNT(Posts.Id) NumberOfNonClosedQuestions ,NTILE(20) OVER (ORDER BY 1.0 * COUNT(Posts.Id)) NumberOfNonClosedQuestionsVigintile ,NTILE(20) OVER (ORDER BY 1.0 * COUNT(AcceptedAnswerId) / COUNT(Posts.Id), NEWID()) AcceptRatioVigintile ,1.0 * COUNT(AcceptedAnswerId) / COUNT(Posts.Id) AcceptRatio FROM Users INNER JOIN Posts ON Posts.OwnerUserId = Users.Id WHERE Posts.PostTypeId = 1 -- Question AND Posts.ClosedDate IS NULL AND Posts.CommunityOwnedDate IS NULL AND Posts.AnswerCount > 0 AND Posts.Score >= 0 AND Posts.CreationDate > '2012-01-01' GROUP BY Users.Id ) SELECT COUNT(DISTINCT UserId) User_Count ,NumberOfNonClosedQuestionsVigintile QuestionCount_Vigintile ,AVG(1.0 * NumberOfNonClosedQuestions) QuestionCount_Avg ,AcceptRatioVigintile AcceptRatio_Vigintile ,AVG(AcceptRatio) AcceptRatio_Avg FROM ratioInformation GROUP BY AcceptRatioVigintile, NumberOfNonClosedQuestionsVigintile ORDER BY AcceptRatioVigintile, NumberOfNonClosedQuestionsVigintile