Find popular originals (questions referenced in duplicate notices)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

SELECT COUNT(PostId) AS DuplicateCount,
/* we want to sort by this, so let's make it an integer */
CAST(
SUBSTRING(
/* search full post text */
Text,
/* get the part starting after http://superuser.com/questions/ */
CHARINDEX(
/* by finding the offset of that URL part */
'http://superuser.com/questions/',
/* in Text */
Text
)
/* and adding its length */
+ LEN('http://superuser.com/questions/'),
/* and get a number of characters from that offset */
CHARINDEX(
/* search for the first forward slash */
'/',
Text,
/* this is the offset again */
CHARINDEX(
'http://superuser.com/questions/',
Text
) + LEN('http://superuser.com/questions/')
/* offset end */
) - CHARINDEX(
/* since only the search skips the first part,
but the return value is still based on the full text,
we need to remove the characters we
skipped when searching */
'http://superuser.com/questions/',
Text
) - LEN('http://superuser.com/questions/')
) AS Int) OriginalPost
FROM PostHistory
/* Community user */
WHERE UserId = -1
/* this has always been the notice */
AND Comment = 'insert duplicate link'
GROUP BY CAST(
SUBSTRING(
/* search full post text */
Text,
/* get the part starting after http://superuser.com/questions/ */
CHARINDEX(
/* by finding the offset of that URL part */
'http://superuser.com/questions/',
/* in Text */
Text
)
/* and adding its length */
+ LEN('http://superuser.com/questions/'),
/* and get a number of characters from that offset */
CHARINDEX(
/* search for the first forward slash */
'/',
Text,
/* this is the offset again */
CHARINDEX(
'http://superuser.com/questions/',
Text
) + LEN('http://superuser.com/questions/')
/* offset end */
) - CHARINDEX(
/* since only the search skips the first part,
but the return value is still based on the full text,
we need to remove the characters we
skipped when searching */
'http://superuser.com/questions/',
Text
) - LEN('http://superuser.com/questions/')
) AS Int)
/* now order by the number of references to the original */
ORDER BY DuplicateCount DESC