Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)
Q&A for participants in team and individual sport activities
DECLARE @tagName varchar(255) DECLARE @tagInput bit DECLARE @locationName varchar(255) SET @tagname = rtrim(##TagName:string##) SET @locationName = rtrim(##LocationName: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 INNER JOIN Users us ON us.Id = a.OwnerUserId 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 AND LOWER(us.Location) LIKE '%sri lanka%' 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 WHERE (OwnerUserId = @UserID or @UserID = 0) ORDER BY TagName, rank