5

On a site, I've gotten 4 of a certain gold badge now and that made me curious to see who has the most, or maybe a list of users ordered by the number of times they've earned that badge.

I'm not familiar with the data API for SE and was wondering if someone knew of a search function written for this already? It seems plausible that I wouldn't be the first person interested in this particular query.

Edit: I'm particularly interested in a per-site query, but network wide would also be really interesting.

2
  • 2
    Per site or network wide?
    – rene
    Commented Oct 12, 2017 at 15:34
  • @rene sorry, Ill edit - per site. Network wide would also be interesting, but I was particularly interested in per site.
    – J.Todd
    Commented Oct 12, 2017 at 15:38

1 Answer 1

6

This query does what you want:

-- badgename:badgename or * for all badges

select name
     , concat(
        'site://'
       , 'users/'
       , userid
       , '|'
       , u.displayname)
       as [User]
     , count(*) as[# of badges] 
from badges b
inner join users u on u.id = b.userid
where (##badgename:string?*## = '*' or name = ##badgename:string?*##)
group by name, userid, u.displayname
order by count(*) desc

You can either enter a single * to get a list of all badgenames and who earned the most or enter a specific badgename (or tagname for that matter) to find the league for a specific badge.

When run on Stack Overflow today this is the result for all badges:

enter image description here

Keep in mind SEDE is only updated once a week, on Sunday. To learn how to make your own queries take a look at the awesome tutorial.

2
  • This looks really cool, BUT I get this error when I try to specify a specific badge at the query you linked: "The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."
    – Andrew
    Commented Oct 18, 2019 at 12:20
  • 1
    @Andrew that is now fixed. If you use the [User Link] magic column the query that gets run can have so many id's in its where clause that the query engine gives up. I made a slight adaption that gives the same result without relying on a magic column.
    – rene
    Commented Oct 18, 2019 at 14:16

You must log in to answer this question.

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