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,
				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))
         FROM   Intermediate
							 GROUP BY  QuestionId,
                         ViewCount
							 )
SELECT [Post Link] = QuestionId,       
       ViewCount = FORMAT(SUM(ViewCount), 'N0'),
	   GROUPING(QuestionId)
FROM   DistinctContributingQuestions
CROSS APPLY (SELECT IIF(1 IN (IsQuestionAsker, AcceptedAnswer, Top3Answer, Score5PlusAnswer, Score20PctAnswer),1,0)) CA(eligible)
GROUP  BY GROUPING SETS( ( ), ( QuestionId ) )
ORDER  BY SUM(ViewCount) DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results