0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

select u.id as [User Link]
     , u.reputation
     , [Deputy]      -- which badges you want
     , [Civic Duty]
     , [Reviewer]
     , [Proofreader]
     , [Strunk & White]
     , [Refiner]
     , [Sportsmanship]
     , [Commentator]
from
(
  select userid         -- userid is our group
         , [Deputy]      -- which badges you want
         , [Civic Duty]
         , [Reviewer]
         , [Proofreader]
         , [Strunk & White]
         , [Refiner]
         , [Sportsmanship]
         , [Commentator]
  from (
    select id
         , userid
         , name 
    from badges
    where tagbased = 0
  ) as data
  pivot 
  (
    count(data.id)          -- count the number of badges per user per badge
    for name in (
         [Deputy]     -- which badges you want
       , [Civic Duty]
       , [Reviewer]
       , [Proofreader]
       , [Strunk & White]
       , [Refiner]
       , [Sportsmanship]
       , [Commentator]
    ) 
  ) pvt
    where [Deputy] +  [Civic Duty] +  [Reviewer] + [Proofreader] + 
    [Strunk & White] + [Refiner] + [Sportsmanship] + [Commentator] > 4
) main 
inner join users u on u.id = main.userid -- for reputation
inner join badges bu on bu.userid = u.id
where bu.name in ('r') 
and tagbased = 1 
and class=1
order by reputation desc

fork query

edited mar 31 anonymous

created jan 31 18 rene