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).
Q&A for database professionals who wish to improve their database skills and learn from others in the community
WITH stub_list AS
p.OwnerUserId, -- OP from source (stub)
pl.RelatedPostId -- Post Merge Destination (master)
FROM Posts p
JOIN PostLinks pl ON pl.PostId = p.Id
WHERE p.PostTypeId = 1 -- questions.
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],
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
--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.