Number of users who can run for mod on SO

0

Please login or register to vote for this query.

(click on this box to dismiss)

Earth Science

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

select u.id as [User Link]
     , u.reputation
       , [Deputy]     -- which badges you want
       , [Civic Duty]
       , [Convention]
       , [Strunk & White]
from
(
  select userid         -- userid is our group
       , [Deputy]     -- which badges you want
       , [Civic Duty]
       , [Convention]
       , [Strunk & White]
  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]
       , [Convention]
       , [Strunk & White]
    ) 
  ) pvt
    where [Deputy] +  [Civic Duty] +  [Strunk & White] + [Convention] = 4
) main 
inner join users u on u.id = main.userid -- for reputation
order by reputation desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results