Find popular originals (questions referenced in duplicate notices)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

/* Select the top 100 original posts and how many duplicates they have */
/* from all duplicate questions. */
SELECT TOP 100
  CAST(QUESTION.Identifier AS INT) AS [Post Link],
  COUNT(QUESTION.PostId) AS DuplicateCount
FROM
(
  /* Select the Identifier part out of the path */
  /* from all duplicate link URLs. */
  SELECT PostId, SUBSTRING(URL.Path, 0, CHARINDEX('/', URL.Path)) Identifier
  FROM
  (
    /* Select the path part from the URL after '(questio)ns/' */
    /* from the duplicate notices in the post history of the community user. */
    SELECT
      PostId, SUBSTRING(Text, CHARINDEX('ns/', Text) + LEN('ns/'), 999) Path
    FROM PostHistory
    WHERE UserId = -1
    AND Comment = 'insert duplicate link'
  ) AS URL
) AS QUESTION
GROUP BY QUESTION.Identifier
ORDER BY DuplicateCount DESC

Enter Parameters

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