1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- top users: NYC
-- forked from Avinash Raj's query for Chennai
select
    row_number() over(order by u.Reputation desc) as [#], 
    u.Id as [User Link], 
    u.Reputation,
    u.Location 
from -- denormalize the Users with their Badges, i.e., flatten into a single table.
    Users u
  join 
    Badges b
  on
    u.Id = b.UserId
where
    b.name = N'python'
  and
    b.class = 3 -- 1 is gold, 2: silver, 3: bronze
  and
    u.Reputation >= 1000 
  and 
    (
      lower(u.Location) like '%nyc%'
      or upper(Location) like '%NY, NY%'
      or lower(Location) like '%staten%'
      or lower(Location) like '%bronx%'
      or lower(Location) like '%queens%'
      or lower(Location) like '%new york, ny%'
      or lower(Location) like '%manhattan%'
      or lower(Location) like '%brooklyn%'
    )
order by
    u.Reputation desc;

fork query

edited jan 25 16 jon.doe12636

created jun 15 15 anonymous