Self-duplicates with no answers (list of stub Qs)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Find duplicate unanswered questions where OP from the stub post (source) is the same OP as from the destination post (master/target). Usually, duplicates serve a purpose of linking nearly identical posts in scope so the best/canonical answer can be found. They are useful because since people can ask related questions with different wording, there are more chances the result of a search leading to one of the dupes (and then, the canonical answer). HOWEVER, duplicate questions asked by the same OP* are likely to have the same wording and therefore, the corresponding stubs are useless to the site. Those should be purged (flag for moderator attention or cast a delete vote, after confirming it is a useless question)! *Usually this happens because OP asks, then nobody answers, then he/she asks the same Q again instead of improving the first one (there are exceptions, this is why results from the query should be reviewed carefully).

Database Administrators

Q&A for database professionals who wish to improve their database skills and learn from others in the community

WITH stub_list AS
(SELECT p.Id,
        p.Score,
        p.OwnerUserId, -- OP from source (stub)
        p.OwnerDisplayName,
        pl.RelatedPostId -- Post Merge 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, 37) -- Post Closed (duplicate), Post Merge Source (stub)
        --AND ph.PostHistoryTypeId IN (37) -- [alternative to above] when running out votes, review only merged (by flags)       
      )
      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)
      OR s.OwnerDisplayName = p.OwnerDisplayName -- attemp to get same OPs, but with different accounts (this is under testing)
ORDER BY s.Score ASC; -- low qualities first

--Linked posts: 

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

Enter Parameters

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