0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- TagName: Filter questions by this tag. Leave empty (' ') to list all questions.

-- 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.

-- When was the new duplicate-closure system (with real banners rather than ad
-- hoc markdown) implemented? <http://meta.stackexchange.com/q/166707/224428>
DECLARE @lti3_start_date AS datetime = (
  SELECT MIN(CreationDate) FROM StackOverflow..PostLinks WHERE LinkTypeId = 3);

WITH raw AS ( -- grab both real duplicate banners and pseudo duplicate banners
  SELECT
    PostId,
    RelatedPostId
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 3 -- duplicate
  
  UNION
  
  SELECT
    PostId,
    RelatedPostId
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 1
    AND pl.CreationDate < @lti3_start_date -- safety check
    AND EXISTS (
      SELECT 1 
      FROM Posts AS p 
      WHERE 
        p.Id = pl.PostId 
        AND p.Body LIKE 
'%<blockquote>
  <p><strong>Possible Duplicate:</strong><br>
  <a href="%/questions/' + CAST(pl.RelatedPostId AS nvarchar) + '/%</a>  </p>
</blockquote>%'
    )
)

SELECT DISTINCT TOP 1000 
  (SELECT COUNT(PostId) 
   FROM raw AS r2 
   WHERE r1.RelatedPostId = r2.RelatedPostId) AS [DuplicateCount],
  r1.RelatedPostId AS [Post Link],
  p.Score,
  p.ViewCount AS [Views],
  p.FavoriteCount AS [Favs],
  p.Tags
FROM 
  raw AS r1
  INNER JOIN Posts AS p ON r1.RelatedPostId = p.Id
WHERE
  ##TagName:string? ## = ' '
  OR ##TagName## IN (
    SELECT t.TagName
    FROM Tags AS t
    INNER JOIN PostTags AS pt ON pt.TagId = t.Id
    WHERE pt.PostId = p.Id
  )
ORDER BY 
  [DuplicateCount] DESC,
  [ViewCount] DESC

fork query

edited may 17 15 Deer Hunter

created jan 16 15 senshin