This query lists questions that have been closed as duplicates of another question that has since been deleted. Such questions should be either deleted or, if they have good answers or are otherwise of decent quality, reopened. Note that non-locked questions with no answers and a negative score will be automatically deleted by the RemoveDeadQuestions script after 30 days, as indicated by the "Roombable?" column.
By default, the results will be sorted with the most recently closed questions first. You can click the column headings to change the sort order. For example, questions with a high answer score may be good candidates for reopening (or for a historical lock), while questions with a high score but no answers may simply need a new dupe target (see http://meta.stackexchange.com/q/291824).
Q&A about the site for gardeners and landscapers
-- step 1: find dupes with deleted targets
CREATE TABLE #DeletedDupes (
PostId INT NOT NULL,
TargetId INT NULL,
AnswerScore INT NULL,
LockEventId INT NULL
INSERT INTO #DeletedDupes
BrokenLink.PostId as PostId,
MAX(BrokenLink.RelatedPostId) as TargetId,
MAX(Answer.Score) as AnswerScore,
MAX(LockEvent.Id) as LockEventId
PostLinks as BrokenLink
LEFT JOIN Posts as TargetPost
ON TargetPost.Id = BrokenLink.RelatedPostId
LEFT JOIN Posts as Answer
ON Answer.PostTypeId = 2 AND Answer.ParentId = BrokenLink.PostId
LEFT JOIN PostHistory as LockEvent
ON LockEvent.PostHistoryTypeId IN (14, 15) AND LockEvent.PostId = BrokenLink.PostId
BrokenLink.LinkTypeId = 3
AND TargetPost.Id IS NULL
AND NOT EXISTS (
SELECT 1 FROM
PostLinks as OtherLink
JOIN Posts as OtherTarget
ON OtherTarget.Id = OtherLink.RelatedPostId
OtherLink.LinkTypeId = 3 AND OtherLink.PostId = BrokenLink.PostId
-- step 2: format results nicely
BrokenLink.PostId as [Post Link],
SourcePost.ViewCount as Views,
SourcePost.AnswerCount as Answers,
BrokenLink.AnswerScore as AScore,
(CASE WHEN SourcePost.AcceptedAnswerId IS NULL THEN '' ELSE 'Accepted' END) as Accepted,
SourcePost.ClosedDate as Closed,
TargetPost.DeletionDate as Deleted,
WHEN LockEventId IS NOT NULL AND (SELECT PostHistoryTypeId FROM PostHistory WHERE Id = LockEventId) = 14 THEN 'No (Post Locked)'
WHEN SourcePost.AnswerCount = 1 THEN 'No (1 answer)'
WHEN SourcePost.AnswerCount > 1 THEN 'No (' + Cast(SourcePost.AnswerCount as VARCHAR) + ' answers)'
WHEN SourcePost.Score = 0 THEN 'If downvoted'
WHEN SourcePost.Score > 0 THEN 'If downvoted x' + Cast(SourcePost.Score+1 as VARCHAR)
ELSE 'Yes, on ' + Convert(VARCHAR(10), DateAdd(Day, 30, SourcePost.CreationDate), 126)
END) as [Roombable?],
SourcePost.Tags as [Tags],
-- Questions closed before 2013-02-06 have their duplicate link banner
-- edited into the post text by the Community user. Sometimes later edits
-- may have removed or modified the banner.
WHEN CommunityEdit.Id IS NULL THEN 'Auto'
WHEN SourcePost.Body LIKE '<blockquote>%/questions/' + Cast(TargetId as NVARCHAR) + '/%</blockquote>%' THEN 'Legacy'
WHEN SourcePost.Body LIKE '<blockquote>%' THEN 'Modified'
END) as [Banner]
#DeletedDupes as BrokenLink
JOIN Posts as SourcePost ON SourcePost.Id = BrokenLink.PostId
LEFT JOIN PostsWithDeleted as TargetPost ON TargetPost.Id = BrokenLink.TargetId
LEFT JOIN PostHistory as CommunityEdit
ON CommunityEdit.PostId = BrokenLink.PostId
AND CommunityEdit.UserId = -1
AND CommunityEdit.PostHistoryTypeId = 5
AND CommunityEdit.Comment = 'insert duplicate link'
AND CommunityEdit.CreationDate >= DateAdd(Second, -5, SourcePost.ClosedDate)
AND CommunityEdit.CreationDate <= DateAdd(Second, +5, SourcePost.ClosedDate)