WITH CTE AS ( SELECT Q.Id AS QuestionId, Q.Title AS Quest...

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

WITH CTE AS (
    SELECT Q.Id AS QuestionId, Q.Title AS QuestionTitle, Q.Score AS QuestionScore, Q.CreationDate AS QuestionCreationDate,
           UQ.Reputation AS QuestionAskerReputation,
           (SELECT COUNT(*)
            FROM Posts A
            WHERE A.ParentId = Q.Id) AS TotalAnswers,
           (SELECT COUNT(DISTINCT A.OwnerUserId)
            FROM Posts A
            WHERE A.ParentId = Q.Id AND A.OwnerUserId != Q.OwnerUserId) AS DistinctAnswerUsers,
           (SELECT TOP 1 A.Body
            FROM Posts A
            WHERE A.ParentId = Q.Id
            ORDER BY A.Score DESC) AS HighestScoreAnswer,
           (SELECT TOP 1 A.CreationDate
            FROM Posts A
            WHERE A.ParentId = Q.Id
            ORDER BY A.Score DESC) AS HighestScoreAnswerCreationDate,
           (SELECT TOP 1 A.OwnerUserId
            FROM Posts A
            WHERE A.ParentId = Q.Id
            ORDER BY A.Score DESC) AS HighestScoreAnswerUserId,
           (SELECT TOP 1 UA.Reputation
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY A.Score DESC) AS HighestScoreAnswerUserReputation,
           (SELECT TOP 1 A.Score
            FROM Posts A
            WHERE A.ParentId = Q.Id
            ORDER BY A.Score DESC) AS HighestScoreAnswerScore,
           (SELECT TOP 1 A.Body
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY UA.Reputation DESC) AS HighestReputationUserAnswer,
           (SELECT TOP 1 A.CreationDate
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY UA.Reputation DESC) AS HighestReputationUserAnswerCreationDate,
           (SELECT TOP 1 A.Score
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY UA.Reputation DESC) AS HighestReputationUserAnswerScore,
           (SELECT TOP 1 A.OwnerUserId
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY UA.Reputation DESC) AS HighestReputationUserId,
           (SELECT TOP 1 UA.Reputation
            FROM Posts A
            JOIN Users UA ON A.OwnerUserId = UA.Id
            WHERE A.ParentId = Q.Id
            ORDER BY UA.Reputation DESC) AS HighestReputationUserReputation
    FROM Posts Q
    JOIN Users UQ ON Q.OwnerUserId = UQ.Id
    WHERE Q.PostTypeId = 1 AND Q.Score >= 1 AND
          (SELECT COUNT(DISTINCT A.OwnerUserId)
           FROM Posts A
           WHERE A.ParentId = Q.Id AND A.OwnerUserId != Q.OwnerUserId) >= 2
)
SELECT TOP 50000 *
FROM CTE
ORDER BY NEWID()

Enter Parameters

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