declare @targetBadgeName nvarchar(50) = N##badgeName:str...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results