0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jan 7 13 Jeroen

created jan 7 13 anonymous