People Reached

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @userid int = ##userid:int##;

WITH CandidateQuestions
     AS (SELECT DISTINCT QuestionId = IIF(PostTypeId = 1, Id, ParentId)
         FROM   Posts
         WHERE  PostTypeId IN ( 1, 2 )
                AND OwnerUserId = @userid),
     Intermediate
     AS (SELECT CQ.QuestionId,
                Q.ViewCount,
                IsQuestionAsker = IIF(Q.OwnerUserId = @userid, 1, 0),
                IsAnswerer = IIF(A.OwnerUserId = @userid, 1, 0),
                AnswerRank = RANK() OVER (PARTITION BY Q.ID ORDER BY A.Score DESC),
                IsAcceptedAnswer = IIF(A.Id = Q.AcceptedAnswerId, 1, 0),
                AnswerScore = A.Score,
                TotalAnswerScores = SUM(A.Score) OVER (PARTITION BY Q.ID)
         FROM   CandidateQuestions CQ
                INNER JOIN Posts Q
                  ON Q.Id = CQ.QuestionId
                LEFT JOIN Posts A
                  ON Q.Id = A.ParentId),
     DistinctContributingQuestions
     AS (SELECT QuestionId,
                ViewCount,
                TotalAnswerScores = MAX(TotalAnswerScores),
                IsQuestionAsker = MAX(IIF(IsQuestionAsker = 1, 1, 0)),
                AcceptedAnswer = MAX(IIF(AnswerScore > 0
                                         AND IsAnswerer = 1
                                         AND IsAcceptedAnswer = 1, 1, 0)),
                Top3Answer = MAX(IIF(AnswerScore > 0
                                     AND IsAnswerer = 1
                                     AND AnswerRank <= 3, 1, 0)),
                Score5PlusAnswer = MAX(IIF(AnswerScore > 0
                                           AND IsAnswerer = 1
                                           AND AnswerScore >= 5, 1, 0)),
                Score20PctAnswer = MAX(IIF(AnswerScore > 0
                                           AND IsAnswerer = 1
                                           AND AnswerScore >= 0.2 * TotalAnswerScores, 1, 0)),
                AnswerScore = MAX(IIF(IsAnswerer = 1, AnswerScore, NULL)),
                AnswerRank = MIN(IIF(IsAnswerer = 1, AnswerRank, NULL))
         FROM   Intermediate
         GROUP  BY QuestionId,
                   ViewCount)
SELECT [Post Link] = QuestionId,
       ViewCount = FORMAT(SUM(ViewCount), 'N0'),
       IsQuestionAsker = CAST(IsQuestionAsker AS BIT),
       AcceptedAnswer = CAST(AcceptedAnswer AS BIT),
       AnswerRank,
       AnswerScore,
       AnswerScorePct = FORMAT(100.0 * AnswerScore / NULLIF(TotalAnswerScores,0),'N2'), 
       eligible = CAST(eligible AS BIT),
       TotalEligibleViews = CASE WHEN GROUPING(QuestionId) = 1 THEN FORMAT(SUM(eligible * ViewCount), 'N0') END
FROM   DistinctContributingQuestions
       CROSS APPLY (SELECT IIF(1 IN ( IsQuestionAsker, AcceptedAnswer, Top3Answer, Score5PlusAnswer, Score20PctAnswer ), 1, 0)) CA(eligible)
GROUP  BY GROUPING SETS( ( ), 
                         ( QuestionId, eligible, 
                           IsQuestionAsker, AcceptedAnswer,
                           AnswerRank, AnswerScore,
                           TotalAnswerScores
                          ) )
ORDER  BY SUM(ViewCount) DESC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results