Select top N users by upvotes they received for their answers for a given tag located in the specified city or country
Q&A about the site for power users of web applications
-- 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 Results - 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 @Results INT = ##Results?50##; SELECT TOP (@Results) ROW_NUMBER() OVER(ORDER BY SUM(a.Score) DESC) AS [#], u.Id AS [User Link], SUM(a.Score) AS [Tag Reputation], u.Reputation AS [Overall 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 (probably not needed; see join clause) -- select all possible aliases for the given tag AND t.TagName IN ( SELECT TagName FROM Tags WHERE LOWER(TagName) LIKE LOWER('%' + @Tag + '%') UNION SELECT TargetTagName FROM TagSynonyms WHERE LOWER(SourceTagName) 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 + '%')) OR @City = @Country -- don't care about location (blank input) ) GROUP BY u.Id, u.Reputation ORDER BY SUM(a.Score) DESC