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.
Q&A for professional and enthusiast programmers
With Dupes as (
Select PostId as Source, RelatedPostId as Target
Where LinkTypeId = 3
Question.Id as [Post Link],
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 [Loop],
When Author.Id Is Null Then Question.OwnerDisplayName
Else 'site://users/' + Cast(Author.Id as Varchar) + '|' + Author.DisplayName
End as [Author],
Question.ViewCount as [Views],
Question.Tags as [Tags]
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
Question.PostTypeId = 1