top FAQ dup targets over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
  YEAR(MAX(L.CreationDate)) AS Year,
  'site://posts/'+CAST(MAX(P.Id) AS nvarchar)+'|'+MAX(P.Title),
  COUNT(*) AS DupedCount
FROM (
  SELECT TOP ##TopFAQs:int?10##
    MAX(L.RelatedPostId) AS PostId, COUNT(*) AS DupedCount
  FROM PostLinks L
  WHERE L.LinkTypeId = 3 -- dup
  GROUP BY L.RelatedPostId
  HAVING COUNT(*) > ##MinDupedCount:int?2000##
  --ORDER BY DupedCount DESC
) PopularDupTargets
JOIN Posts P ON P.Id = PopularDupTargets.PostId
JOIN PostLinks L ON L.RelatedPostId = PopularDupTargets.PostId
GROUP BY YEAR(L.CreationDate), PopularDupTargets.PostId
ORDER BY YEAR(L.CreationDate) ASC

Enter Parameters

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