Select top N users by upvotes they received for their answers for a given tag located in the specified city or country
Q&A for science fiction and fantasy enthusiasts
-- Top N users for tag located in city / country -- Created by Mihai Todor (http://stackoverflow.com/users/1174378/mihai-todor) /* Selects users based in the given city / country, who have the most upvoted answers for the given tag. Usage: Tag - tag name (mandatory) Country, City - specify at least one NoResults - number of desired results */ DECLARE @Tag NVARCHAR(35) = ##Tag:string##; DECLARE @City NVARCHAR(100) = LTRIM(RTRIM(##City:string? ##)) DECLARE @Country NVARCHAR(100) = LTRIM(RTRIM(##Country:string? ##)); DECLARE @NoResults INT = ##NoResults?50##; SELECT TOP (@NoResults) ROW_NUMBER() OVER(PARTITION BY u.Id, u.Reputation ORDER BY SUM(a.Score) DESC) AS [#], u.Id AS [User Link], SUM(a.Score) AS [Tag Score], u.Reputation FROM Posts AS p INNER JOIN Posts AS a ON a.ParentId = p.id -- answers only INNER JOIN PostTags AS pt ON p.Id = pt.PostId INNER JOIN Tags AS t ON pt.TagId = t.Id INNER JOIN Users AS u ON u.Id = a.OwnerUserId -- OR u.Id = a.LastEditorUserId -- Might be useful to play with WHERE a.PostTypeId = 2 -- answers only -- select all possible aliases for the given tag AND t.TagName IN ( SELECT @Tag UNION SELECT SourceTagName FROM TagSynonyms WHERE LOWER(SourceTagName) LIKE LOWER('%' + @Tag + '%') UNION SELECT TargetTagName FROM TagSynonyms WHERE LOWER(TargetTagName) LIKE LOWER('%' + @Tag + '%') ) AND ( @City <> '' AND (LOWER(u.Location) LIKE LOWER('%' + @City + '%') OR LOWER(u.AboutMe) LIKE LOWER('%' + @City + '%')) OR @Country <> '' AND (LOWER(u.Location) LIKE LOWER('%' + @Country + '%') OR LOWER(u.AboutMe) LIKE LOWER('%' + @Country + '%')) ) GROUP BY u.Id, u.Reputation ORDER BY SUM(a.Score) DESC