Tasty and delicious Dupe Loops

1

Please login or register to vote for this query.

(click on this box to dismiss)

Finds loops of questions closed as duplicates of each other. Returns the oldest question (by question ID) in each loop. This query currently finds loops of up to 20 questions, but is easily extensible.

Stack Overflow

Q&A for professional and enthusiast programmers

With Dupes as (
  Select PostId as Source, RelatedPostId as Target
  From PostLinks
  Where LinkTypeId = 3
)
Select Distinct
  Question.Id as [Post Link],
  Case
    When Dupe1.Target = Question.Id Then 1
    When Dupe2.Target = Question.Id Then 2
    When Dupe3.Target = Question.Id Then 3
    When Dupe4.Target = Question.Id Then 4
    When Dupe5.Target = Question.Id Then 5
    When Dupe6.Target = Question.Id Then 6
    When Dupe7.Target = Question.Id Then 7
    When Dupe8.Target = Question.Id Then 8
    When Dupe9.Target = Question.Id Then 9
  End as [Len],
  Case
    When Author.Id is null Then Question.OwnerDisplayName
    Else 'site://users/' + Cast(Author.Id as Varchar) + '|' + Author.DisplayName
  End as [Author],
  Question.Score,
  Question.CreationDate,
  Question.ViewCount as [Views],
  Question.Tags as [Tags]
From
  Posts as Question
  Join Dupes as Dupe1 on Dupe1.Source = Question.Id
  Join Dupes as Dupe2 on Dupe2.Source = Dupe1.Target
  Join Dupes as Dupe3 on Dupe3.Source = Dupe2.Target
  Join Dupes as Dupe4 on Dupe4.Source = Dupe3.Target
  Join Dupes as Dupe5 on Dupe5.Source = Dupe4.Target
  Join Dupes as Dupe6 on Dupe6.Source = Dupe5.Target
  Join Dupes as Dupe7 on Dupe7.Source = Dupe6.Target
  Join Dupes as Dupe8 on Dupe8.Source = Dupe7.Target
  Join Dupes as Dupe9 on Dupe9.Source = Dupe8.Target
  Left Join Users as Author on Author.Id = Question.OwnerUserId
Where
  Question.PostTypeId = 1
  And Question.Id in (
    Dupe1.Target, Dupe2.Target, Dupe3.Target,
    Dupe4.Target, Dupe5.Target, Dupe6.Target,
    Dupe7.Target, Dupe8.Target, Dupe9.Target)
  And Question.Id <= Dupe1.Target
  And Question.Id <= Dupe2.Target
  And Question.Id <= Dupe3.Target
  And Question.Id <= Dupe4.Target
  And Question.Id <= Dupe5.Target
  And Question.Id <= Dupe6.Target
  And Question.Id <= Dupe7.Target
  And Question.Id <= Dupe8.Target
  And Question.Id <= Dupe9.Target

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results