Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)
Q&A about the site for members of the workforce navigating the professional setting
-- Top Users by location and tag (see http://stackoverflow.com/tags) -- Created by samliew (http://stackoverflow.com/users/584192/samuel-liew) -- Modified by sgryzko WITH QuestionScores AS ( SELECT u.Id AS [User Link], sum(q.score) AS Score FROM Users u inner join posts q on q.ownerUserid = u.id inner join posttags pt on pt.postid = q.id inner join tags t on t.id = pt.tagid WHERE LOWER(Location) LIKE LOWER('%##location##%') and t.tagname='##tagname##' GROUP BY u.id, u.Location /*ORDER BY sum(q.score) DESC;*/ ), AnswerScores AS ( SELECT Users.Id AS [User Link], sum(Answers.score) AS Score FROM Posts AS Answers INNER JOIN Posts AS ParentQuestions ON ParentQuestions.Id = Answers.ParentId INNER JOIN Users AS Users ON Answers.OwnerUserId = Users.Id INNER JOIN PostTags ON PostTags.PostId = Answers.ParentId INNER JOIN Tags ON Tags.Id = PostTags.TagId WHERE LOWER(Location) LIKE LOWER('%##location##%') and Tags.tagname='##tagname##' GROUP BY Users.id, Users.Location /*ORDER BY sum(Answers.score) DESC;*/ ) SELECT TOP 100 ISNULL(QuestionScores.[User Link], AnswerScores.[User Link]) as [User Link], ISNULL(QuestionScores.Score, 0) as [Question Score], ISNULL(AnswerScores.Score, 0) as [Answer Score], ISNULL(QuestionScores.Score, 0) + ISNULL(AnswerScores.Score, 0) as [Total Score] FROM QuestionScores FULL JOIN AnswerScores ON QuestionScores.[User Link] = AnswerScores.[User Link] ORDER BY [Total Score] DESC;