Put banks in for Tag Name for all. Tag names should be in lower case Put 0 for user ID for all users
Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
-- Tag Rankings Fun -- Put banks in for Tag Name for all. Tag names should be in lower case -- Put 0 for user ID for all users DECLARE @tagName varchar(255) DECLARE @tagInput bit SET @tagname = rtrim(##TagName:string##) DECLARE @UserID int = ##UserId## IF RTRIM(@tagname) = '' SET @tagInput = 0 ELSE SET @tagInput = 1 if @tagInput = 0 and @UserID = 0 SELECT 'Don' + '''' + 't do that' ELSE WITH UserTagScores as (SELECT a.OwnerUserId, t.TagName, SUM(CASE v.VoteTypeID WHEN 2 THEN 1 WHEN 3 THEN -1 END) as score FROM votes v INNER JOIN Posts a ON v.PostID = a.ID INNER JOIN Posts q ON a.parentId = q.id INNER JOIN PostTags pt ON q.id = pt.PostId INNER JOIN Tags T ON pt.TagId = t.id WHERE v.voteTypeId in (2,3) -- Up and down votes and a.PostTypeID = 2 -- Answers only AND (a.CommunityOwnedDate is null or a.CommunityOwnedDate > v.CreationDate) AND (@tagInput = 0 or t.TagName = @TagName) AND a.OwnerUserId is not null GROUP BY a.OwnerUserId, t.TagName) , userTagRank as ( SELECT TagName, OwnerUserId, rank() over (partition by TagName order by score desc) rank, Score FROM UserTagScores) SELECT TagName, Rank, OwnerUserId , OwnerUserId [User Link], Score FROM userTagRank ORDER BY TagName, rank