Q&A for users and developers of the Cardano cryptocurrency ecosystem
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