Posts by a user ordered by number of links they contain to other posts on the same site

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

SELECT 
  posts.Id AS [Post Link], 
  (LEN(posts.Body) - 
   LEN(REPLACE(posts.Body,'https://'+LOWER(RIGHT(DB_NAME(db_id()),LEN(DB_NAME(db_id()))-CHARINDEX('.',DB_NAME(db_id())))),'')))/14
  +(LEN(posts.Body) - 
    LEN(REPLACE(posts.Body,'http://'+LOWER(RIGHT(DB_NAME(db_id()),LEN(DB_NAME(db_id()))-CHARINDEX('.',DB_NAME(db_id())))),'')))/13 AS 'Links',
  (CASE WHEN posts.PostTypeId=2 THEN 'A' ELSE 'Q' END) AS 'Type',
  (CASE WHEN posts.PostTypeId=2 THEN questions.Tags 
                                ELSE posts.Tags END) AS 'Tags'
FROM Posts posts
LEFT JOIN Posts questions ON posts.ParentId=questions.Id
WHERE posts.OwnerUserId=##UserId##
ORDER BY Links DESC

Enter Parameters

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