Posts that are the target of the most duplicate closures

2

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

DECLARE @lti3_start_date AS datetime = (
  SELECT MIN(CreationDate) FROM StackOverflow..PostLinks WHERE LinkTypeId = 3)
  

-- Some deleted posts still live in PostLinks.RelatedPostId because they are the
-- closure target of a non-deleted post. I've excluded those from the query below.

SELECT
  PostId AS [Post Link]
FROM (
  SELECT
    *
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 3 -- duplicate
  
  UNION
  
  SELECT
    *
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 1
    AND pl.CreationDate < @lti3_start_date
    --AND EXISTS (SELECT 1 FROM Posts AS p WHERE p.Id = pl.PostId AND p.Body LIKE '
  ) AS subq
WHERE
  RelatedPostId IN (SELECT p.Id FROM Posts AS p)
  AND LinkTypeId = 1

Enter Parameters

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