0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and independent game developers

select distinct
  Users.DisplayName,
  Users.Location,
  case
    when lower(Users.Location) like '%netherlands%' or lower(Users.Location) like '%nederland%' or lower(Users.Location) like '%holland%' then 'NL'
    when lower(Users.Location) like '%belgium%' then 'BE'
  end as Country,
  Users.Age,
  Users.CreationDate as RegistrationDate,
  Users.LastAccessDate,
  Users.Reputation,
  Users.Views,
  concat('https://stackoverflow.com/users/', Users.Id) as ProfileUrl,
  Users.WebsiteUrl,
  concat('https://www.google.com/search/?q=', lower(replace(Users.DisplayName collate database_default, ' ', '+'))) as GoogleSearchUrl,
  concat('https://plus.google.com/s/', lower(replace(Users.DisplayName collate database_default, ' ', '+'))) as GooglePlusSearchUrl,
  concat('https://github.com/search?type=Users&q=', lower(replace(Users.DisplayName collate database_default, ' ', '+'))) as GitHubSearchUrl,
  concat('https://www.linkedin.com/vsearch/p?keywords=', lower(replace(Users.DisplayName collate database_default, ' ', '+'))) as LinkedInSearchUrl,
  concat('https://twitter.com/search?mode=users&q=', lower(replace(Users.DisplayName collate database_default, ' ', '+'))) as TwitterSearchUrl
FROM
    Users, Badges
where
  Users.Id = Badges.UserId
    AND Badges.Class = 2 
    AND Badges.TagBased = 1
    AND Badges.name = 'java' and
  Users.LastAccessDate >= dateadd(month, -3, getdate()) and
  (
    LOWER(location) LIKE LOWER('%netherlands%') or 
    LOWER(location) LIKE LOWER('%Ireland%') or 
    LOWER(Location) LIKE LOWER ('%belgium%') or 
    LOWER(Location) LIKE LOWER ('%luxembourg%') or
    LOWER(Location) LIKE LOWER ('%united kingdom%') or
    LOWER(Location) LIKE LOWER ('%germany%') or
    LOWER(Location) LIKE LOWER ('%france%') or 
    LOWER(Location) LIKE LOWER ('%italy%') or 
    LOWER(Location) LIKE LOWER ('%switzerland%') or 
    LOWER(Location) LIKE LOWER ('%austria%') or
    LOWER(Location) LIKE LOWER ('%spain%') or
    LOWER(Location) LIKE LOWER ('%portugal%') or
    LOWER(Location) LIKE LOWER ('%norway%') or
    LOWER(Location) LIKE LOWER ('%denmark%') or
    LOWER(Location) LIKE LOWER ('%sweden%') or
    LOWER(Location) LIKE LOWER ('%finland%') or
    LOWER(Location) LIKE LOWER ('%poland%') or
    LOWER(Location) LIKE LOWER ('%czech%')
  )
order by
  Users.Reputation desc

fork query

edited oct 17 17

created oct 5 17 anonymous