I am trying to compose a query that will list all high rep users who have a silver badge, but no gold ones.

I am new to the SEDE. I have gone through the tutorial and properties documentation, but I cannot figure out how to use them to find out badges.


The following query does that for you.

I've first created a Common Table Expression and named it Gold to give me a set with all the users that have a Gold Badge (The Class = 1 depicts that).

Then I select from the users table and do a LEFT OUTER JOIN with Gold, joining on the userid. The Users that don't have a Gold badge will not have a corresponding row in Gold. That is the criteria we can use to filter the result set on, where Gold.id is null will select those rows that don't have a matching row in the Gold set.

;with gold as
  select id
       , userid
  from badges
  where class = 1 -- Gold

select users.id as [User Link]
     , reputation
     , creationdate
     , lastaccessdate
     , views
     , upvotes
     , downvotes
from users 
left outer join gold on gold.userid = users.id
where gold.id is null
order by reputation desc

When run today this is the result:

result set showing user Doug Currie with 37639 rep, Neville Kuyt with 25667 rep and 13 more rows of users

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the magnificent Monica Cellio.
Say "Hi" in SEDE chat.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .