1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

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

fork query

created nov 19 22 starball