10

Please login or register to vote for this query.

(click on this box to dismiss)

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 for pro webmasters

-- step 1: find dupes with deleted targets
CREATE TABLE #DeletedDupes (
  PostId INT NOT NULL,
  TargetId INT NULL,
  MaxAnswerScore INT NULL,
  MaxAnswerLength INT NULL,
  LockEventId INT NULL
)

INSERT INTO #DeletedDupes
SELECT
  BrokenLink.PostId as PostId,
  MAX(BrokenLink.RelatedPostId) as TargetId,
  MAX(Answer.Score) as MaxAnswerScore,
  MAX(LEN(Answer.Body)) as MaxAnswerLength,
  MAX(LockEvent.Id) as LockEventId
FROM
  PostLinks as BrokenLink
  JOIN Posts as SourcePost
    ON SourcePost.Id = BrokenLink.PostId
  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
WHERE
  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
    WHERE
      OtherLink.LinkTypeId = 3 AND OtherLink.PostId = BrokenLink.PostId
  )
GROUP BY
  BrokenLink.PostId
  

-- step 2: determine current site host name for migration matching
DECLARE @sitename NVARCHAR(MAX) = DB_NAME()
SET @sitename = REPLACE(@sitename, 'StackExchange.Mathoverflow', 'Mathoverflow')
SET @sitename = REPLACE(@sitename, 'StackExchange.Ubuntu', 'AskUbuntu')
SET @sitename = REPLACE(@sitename, 'StackExchange.Audio', 'StackExchange.Video')
SET @sitename = REPLACE(@sitename, 'StackExchange.Garage', 'StackExchange.Mechanics')
SET @sitename = REPLACE(@sitename, 'StackExchange.Moderators', 'StackExchange.CommunityBuilding')
SET @sitename = REPLACE(@sitename, 'StackExchange.Photography', 'StackExchange.Photo')
SET @sitename = REPLACE(@sitename, 'StackExchange.Programmers', 'StackExchange.SoftwareEngineering')
SET @sitename = REPLACE(@sitename, 'StackOverflow.Br', 'StackOverflow.Pt')

DECLARE @tld NVARCHAR(3) = 
  (CASE WHEN @sitename LIKE 'Mathoverflow%' THEN 'net' ELSE 'com' END)

-- StackExchange.Whatever.Meta -> https://whatever.meta.stackexchange.com
DECLARE @newurlprefix NVARCHAR(MAX) = 'https://' + LOWER(CASE
  WHEN CHARINDEX('.', @sitename, 0) = 0 THEN @sitename
  ELSE RIGHT(@sitename, LEN(@sitename) - CHARINDEX('.', @sitename, 0))
    + '.' + LEFT(@sitename, CHARINDEX('.', @sitename, 0) - 1)
END) + '.' + @tld

-- StackExchange.Whatever.Meta -> http://meta.whatever.stackexchange.com
DECLARE @oldurlprefix NVARCHAR(MAX) = ''
SELECT @oldurlprefix = CONCAT(value, '.', @oldurlprefix)
  FROM STRING_SPLIT(@sitename, '.')
SET @oldurlprefix = 'http://' + LOWER(@oldurlprefix) + @tld


-- step 3: exclude duplicates of migrated posts
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange', DB_NAME())

DECLARE @sitedbname AS nvarchar(max)
DECLARE @query AS nvarchar(max)

OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @query = '
    USE [' + @sitedbname + ']

    DELETE FROM #DeletedDupes WHERE TargetId IN (
      SELECT CAST(LEFT(Path, PATINDEX(''%[^0-9]%'', Path) - 1) as INT) FROM (
        SELECT PostId, SUBSTRING(Comment, PATINDEX(''%[0-9]%'', Comment), LEN(Comment)) as Path
        FROM PostHistory JOIN Posts ON Posts.Id = PostId AND PostTypeId = 1
        WHERE PostHistoryTypeId = 36 AND Comment LIKE ''from ' + @oldurlprefix + '/questions/%''
        UNION ALL
        SELECT PostId, SUBSTRING(Comment, PATINDEX(''%[0-9]%'', Comment), LEN(Comment)) as Path
        FROM PostHistory JOIN Posts ON Posts.Id = PostId AND PostTypeId = 1
        WHERE PostHistoryTypeId = 36 AND Comment LIKE ''from ' + @newurlprefix + '/questions/%''
      ) as X
    )'
  EXECUTE(@query)
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites
  

-- step 4: format results nicely
SELECT
  BrokenLink.PostId as [Post Link],
  SourcePost.ViewCount as Views,
  SourcePost.Score,
  SourcePost.AnswerCount as Answers,
  BrokenLink.MaxAnswerScore as AScore,
  BrokenLink.MaxAnswerLength as ALen,
  (CASE WHEN SourcePost.AcceptedAnswerId IS NULL THEN '' ELSE 'Accepted' END) as Accepted,
  SourcePost.ClosedDate as Closed,
  TargetPost.DeletionDate as Deleted,
  (CASE
    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.
  (CASE
    WHEN SourcePost.ClosedDate >= '2013-02-06' AND CommunityEdit.Id IS NULL THEN 'Auto'
    WHEN SourcePost.Body LIKE '<blockquote>%/questions/' + Cast(TargetId as NVARCHAR) + '/%</blockquote>%' THEN 'In body'
    WHEN SourcePost.Body LIKE '<blockquote>%' THEN 'Modified'
    ELSE 'Missing'
  END) as [Banner],
  TargetPost.Id as [Target ID],
  SourcePost.Title as [Title]  -- for sorting
FROM
  #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)
ORDER BY
  TargetPost.DeletionDate DESC

fork query

edited oct 11 19 Ilmari Karonen

created feb 19 14 animuson