0

Please login or register to vote for this query.

(click on this box to dismiss)

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

-- Currently this does "What are the top answers for a tag?" instead of "Who?"

-- UserId: User ID
--DECLARE @UserId int = ##UserId:int##
--DECLARE @UserId int = ##UserId:int?149428##
DECLARE @UserId int = 149428

-- TagList: Tags "A CSV list of tags"
--DECLARE @TagList varchar(8000) = ##TagList:string##
--DECLARE @TagList varchar(8000) = ##TagList:string?python,python-2.7,python-3.x##
DECLARE @TagList varchar(8000) = 'python,python-2.7,python-3.x'

-- Without GROUP BY, I'm seeing duplicate a.Id when they use multiple of my included tag names.. http://stackoverflow.com/q/5391564/149428

SELECT TOP 25 a.Id AS 'Answer ID', q.Id AS 'Question ID', q.Id AS [Post Link], a.Score AS 'Answer Score', Users.Id AS 'User ID', Users.Id AS [User Link], q.Tags
--SELECT TOP 25 a.Id, a.Score, a.ParentId, a.OwnerUserId
FROM Posts AS a
INNER JOIN Posts AS q ON q.Id = a.ParentId
INNER JOIN PostTags ON q.Id = PostTags.PostId
INNER JOIN Users ON a.OwnerUserId = Users.Id
WHERE
  PostTags.TagId IN (
    SELECT Id
    FROM Tags
    WHERE TagName IN (
      SELECT * FROM string_split(@TagList, ',')
    )
  )
  AND a.Score >= 1
  AND a.OwnerUserId = @UserId
--GROUP BY a.Id, a.ParentId, a.Score, a.OwnerUserId
--GROUP BY a.OwnerUserId
ORDER BY a.Score DESC

fork query

edited may 21 17

created may 21 17 Taylor Edmiston