0

Please login or register to vote for this query.

(click on this box to dismiss)

Find duplicate questions where OP from the stub post (source) is the same OP as from the destination post (master/target) and both stub/source and master/target questions have answers (hence, they are candidates to be merged).

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 -- target question has answers, so as the stub.
ORDER BY s.Score ASC; -- low qualities first

--Linked posts: 

--GIS - https://gis.meta.stackexchange.com/questions/4988/deleting-merged-questions-asked-by-the-same-op/5003#5003
--MSE - https://meta.stackexchange.com/questions/322241/how-to-find-duplicates-that-could-be-deleted/322243#322243.

fork query

edited apr 7

created feb 26 Andre Silva