1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for designers and implementers of computer programming languages

declare @userid int = 1318479;

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 DISTINCT QuestionId,
                         ViewCount
         FROM   Intermediate
         WHERE  IsQuestionAsker = 1
                 OR ( AnswerScore > 0
                      AND IsAnswerer = 1
                      AND ( AnswerRank <= 3
                             OR IsAcceptedAnswer = 1
                             OR AnswerScore >= 5
                             OR AnswerScore >= 0.2 * TotalAnswerScores ) ))
SELECT [Post Link] = QuestionId,
       ViewCount = FORMAT(SUM(ViewCount), 'N0')
FROM   DistinctContributingQuestions
GROUP  BY GROUPING SETS( ( ), ( QuestionId ) )
ORDER  BY SUM(ViewCount) DESC

fork query

created apr 13 23 cb4