4

I wanted a query that would rank users by total count of badges per class. That is, a ranking like the user card; with 3 columns: gold, silver, bronze - having the total counts (and possibly with the total reputation rank number in the first column).

I tried to search the existing posts tagged with [tag:data-explorer] and [tag:badges], but the closest available queries don't give results as I described.

8

Here are two queries I made some time ago. And I have added two more which also have some info on the reputation of those users.

2
  • Rank in the first column doesn't coincide with order by number total number of badges? I don't understand why but those 2 columns should reflect each other.
    – bad_coder
    Jan 29 at 5:05
  • Ah OK, rank in the first column is sorted by gold badge count.
    – bad_coder
    Jan 29 at 5:06
2

As a technology demonstration I share this query that uses the PIVOT feature of SQL Server. It turns out the Badges table is perfect to use. The resulting query isn't necessarily easier to grasp then the one offered by Martin.

select TOP(##num?200##)
       rank() over(order by [1]+[2]+[3] desc) [rank] -- and ranking functions works
     , UserId as [User Link]  -- we pivot over this column
     , [1] [Gold]    -- first value column
     , [2] [Silver]  -- 2nd value column
     , [3] [Bronze]  -- 3rd value column
     , [1]+[2]+[3] [total]  -- \o/ we can calc as well
from (
  -- this is the main resultset 
  select id
       , Userid
       , class
  from badges
) data
pivot 
(
  count(id) -- aggregate count can work here
  FOR 
  class in ([1], [2], [3]) -- each value in 'class' becomes a column
) pvt
order by [1]+[2]+[3] desc

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

You must log in to answer this question.

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