0

Please login or register to vote for this query.

(click on this box to dismiss)

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;

fork query

edited jan 26 16 jon.doe12636

created jan 26 16 anonymous