3

When I visit accounts tab (https://stackexchange.com/users/userid/username?tab=accounts) I can see global Meta site and all SE sites but can't see any submetas of SE sites.

Given the fact that meta sites have autonomous systems of badges that differ from the main sites badges, I have to go around all meta sites one by one to see the full list of earned badges.

I would like to know whether it is possible to see on one page a full list of sites, including submates, with a full list of badges for each of them? Is there an address where I can view this list?

3

You can do this with a SEDE query.

If you insert all your badges in each database that represents a Meta site you can then project them as you wish.

In below query you can remove (twice!)

where name like '%.meta'
and name <> 'StackExchange.meta' 

to get the list of badges for all sites. I leave it as an exercise for the reader to either parameterise this or use it the projection query. Keep in mind that 'StackExchange.Meta' is the this site you're currently on but is considered to be a main site (as it has reputation).

Here is the base query you need:

declare @sql nvarchar(max)

-- result table, don't rename and keep the site column
create table #users ( site nvarchar(250)
                       , id int
                       , displayname nvarchar(250)
                       , accountid int);

create table #badges ( site nvarchar(250)
                       , id int
                       , Userid int
                       , name nvarchar(50)
                       , Date datetime
                       , Class tinyint
                       , Tagbased int);



select @sql = 'insert into #users' + string_agg(concat('
select ''', convert(nvarchar(max), name), ''', id, displayname, accountid
from ', quotename(name), '.dbo.users
where accountid =', ##accountid?14268056## ,'
'),
' union all ') 
from sys.databases
where name like '%.meta'
and name <> 'StackExchange.meta' 

exec (@sql)


select @sql = 'insert into #badges' + string_agg(concat('
select ''', convert(nvarchar(max), name), ''', b.id, accountid, Name, Date, class, tagbased
from ', quotename(name), '.dbo.badges b
inner join ', quotename(name), '.dbo.users u on u.id = b.userid
where accountid =', ##accountid?14268056## ,'
'),
' union all ') 
from sys.databases
where name like '%.meta'
and name <> 'StackExchange.meta' 

exec (@sql)


select *
from #users

select *
from #badges

select case class when 1 then 'gold' when 2 then 'silver' when 3 then 'bronze' end [Badge color]
     , count(*) [number of badges]
from #badges 
group by class

On the third result tab you'll see how many badges you earned for each color:

enter image description here

On result tab two you'll find all your earned meta badges.

Keep in mind SEDE is updated only once a week, on Sunday. To learn how to use SEDE try the tutorial and if you have a specific question, say hi in chat

  • Thanks a lot, Renee! Very interesting. I've never looked at the StackExchange from this side before. P.S.: As I understand, it's impossible to solve the problem by using existing forms on Stackexchange? I mean, there is no documented (or undocumented) key like "?tab=accounts&show_metas=yes" or something similar? – Ivan Olshansky Dec 9 '18 at 9:22
  • 1
    @IvanOlshansky if I knew it existed on SE I wouldn't have offered a SEDE query ... – rene Dec 9 '18 at 9:32

You must log in to answer this question.

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