The longest dupe chains of the site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

CREATE TABLE #Dupes (
  DupeId INT,
  OrigId INT,
  Depth INT
);

INSERT INTO #Dupes
SELECT
  PostLinks.PostId AS DupeId,
  PostLinks.RelatedPostId AS OrigId, 1 AS Depth
FROM PostLinks, Posts D, Posts O
WHERE
  PostLinks.PostId = D.Id
  AND
  PostLinks.RelatedPostId = O.Id
  AND
  PostLinks.LinkTypeId = 3
  AND
  PostLinks.PostId != PostLinks.RelatedPostId;

DECLARE @Depth INT = 1;

WHILE @Depth < 10
BEGIN
  INSERT INTO #Dupes
  SELECT
    Cur.DupeId AS DupeId,
    Nxt.OrigId AS OrigId,
    @Depth + 1
  FROM #Dupes Cur, #Dupes Nxt
  WHERE
    Cur.Depth = @Depth
    AND
    Cur.OrigId = Nxt.DupeId
    AND
    Nxt.Depth = 1;
  WITH Raw AS (
    SELECT DupeId, OrigId, MIN(Depth) AS Depth
    FROM #Dupes
    GROUP BY DupeId, OrigId
  )
  DELETE FROM #Dupes
  FROM #Dupes, Raw
  WHERE
    #Dupes.OrigId = Raw.OrigId
    AND
    #Dupes.DupeId = Raw.DupeId
    AND
    #Dupes.Depth > Raw.Depth;
  SET @Depth = @Depth + 1;
END;

SELECT TOP 100
  DupeId AS [Post Link], Depth
FROM #Dupes
ORDER BY Depth DESC, DupeId, OrigId

Enter Parameters

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