sand_box

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 stub_list AS
(SELECT p.Id,
        p.Score,
        p.OwnerUserId, -- OP from source (stub)
        pl.RelatedPostId -- Post Destination (master)
 FROM Posts p
 JOIN PostLinks pl ON pl.PostId = p.Id
 WHERE p.PostTypeId = 1 -- questions.
       AND EXISTS 
       (
        SELECT *
        FROM PostHistory ph
        WHERE ph.PostId = p.Id
        AND ph.PostHistoryTypeId IN (10) -- Post Closed (duplicate)     
      )
      AND pl.LinkTypeId = 3 -- duplicate.
      AND p.AnswerCount > 0
)
SELECT s.Id AS [Post Link],
       s.Score
FROM stub_list s
JOIN Posts p ON p.Id = s.RelatedPostId
WHERE s.OwnerUserid = p.OwnerUserId -- OP from source (stub) = OP from destination (master)
      AND p.AnswerCount = 0
ORDER BY s.Score ASC; -- low qualities first

Enter Parameters

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