List Users with reciprocal questions and answers

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
            'site://q/' + CAST(a.Id AS NVARCHAR) + '|Ans ' + CAST(a.Id AS NVARCHAR) AS [Answer Link]
            , 'site://u/' + CAST(a.OwnerUserId AS NVARCHAR) + '|Usr ' + CAST(a.OwnerUserId AS NVARCHAR) AS [Answerer]
            , 'site://u/' + CAST(q.OwnerUserId AS NVARCHAR) + '|Usr ' + CAST(q.OwnerUserId AS NVARCHAR) AS [Asker]
            , a.CreationDate
FROM        Posts a
INNER JOIN  Posts q             ON q.Id = a.ParentId
WHERE       a.PostTypeId        = 2  --  answers
AND         q.OwnerUserId       != a.OwnerUserId  --  No self answers
AND         q.OwnerUserId       > 1  -- Not Community user
AND         q.CreationDate      > ##CutoffDate:string?2014##    -- SEDE limits to 50K rows, so pick more recent ones.
AND         EXISTS (
    SELECT      q2.Id
    FROM        Posts q2
    INNER JOIN  Posts a2            ON q2.Id = a2.ParentId
    WHERE       q2.PostTypeId       = 1
    AND         q2.OwnerUserId      > 1  -- Not Community user
    AND         q2.OwnerUserId      = a.OwnerUserId
    AND         a2.OwnerUserId      = q.OwnerUserId
)
ORDER BY    a.OwnerUserId
            , q.OwnerUserId
            , a.CreationDate

Enter Parameters

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