Q&A for professional and enthusiast programmers
-- top users in NYC region for desired tag (default Python) -- forked from Avinash Raj's query for Chennai -- badgeName: Badge to search for: declare @targetBadgeName nvarchar(50) = N##badgeName:string?python##; -- minimumRep: Minimum reputation: makes no sense to be less than 1000 declare @minimumReputation int = ##minimumRep:int?1000##; select row_number() over(order by usr.Reputation desc) as [Rank], usr.Id as [User Link], usr.Reputation, case min(bdg.Class) when 1 then 'Gold' when 2 then 'Silver' when 3 then 'Bronze' else 'unknown' end as [Highest Badge], usr.Location from dbo.Users as usr -- denormalize the Users with their Badges, i.e., flatten into a single table. join dbo.Badges as bdg on usr.Id = bdg.UserId where bdg.name = @targetBadgeName and usr.Reputation >= @minimumReputation and ( lower(usr.Location) like '%nyc%' or upper(usr.Location) like '%NY, NY%' or lower(usr.Location) like '%staten%' or lower(usr.Location) like '%bronx%' or lower(usr.Location) like '%queens[!l]%' or lower(usr.Location) like '%new york, ny%' or lower(usr.Location) like '%manhattan%' or lower(usr.Location) like '%brooklyn%' ) group by usr.Id, usr.Reputation, usr.Location;