Self-duplicates: stub has answer, target does not (list of stub Qs)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Questions with answers which are duplicates from other questions with no answers, and both questions are asked by the same OP. In such cases, it would be better to reverse the relationship between stub/source and master/target.

GIS

Q&A for cartographers, geographers and GIS professionals

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 Q has no answers (while stub has).
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

Enter Parameters

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