4

Please login or register to vote for this query.

(click on this box to dismiss)

Show all badge holders for a given tag and location

Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

-- Search users by tag badge and location
-- Show all badge holders for a given tag and location

create table #BadgeTiers
(
 Badge text,
 Tier int
)

insert into #BadgeTiers (Badge, Tier)
  select 'Gold', 3 union all
  select 'Silver', 2 union all
  select 'Bronze', 1;

with People as (
  select
    Users.Id,
    count(*) as Tier
  from
    Users
    inner join Badges on Badges.UserId = Users.Id
  where
    Name like '##Tag##' and Location like '%##Location##%'
  group by
    Users.Id
)

select
  People.Id as [User Link],
  Badge,
  Location
from
  People
  inner join Users on Users.Id = People.Id
  inner join #BadgeTiers on #BadgeTiers.Tier = People.Tier
order by
  People.Tier desc

fork query

created jan 16 11 chrisaycock