Is there a way to see missing badges among subscribed communities?

Question

Let's say I'm interested in having the Enthusiast badge for every community I belong to. How would I go about seeing which communities lack the Enthusiast badge without visiting every community and checking the badges?

Answer 1

You can use the following query to determine on which sites you have not yet earned a specific badge.
You have to provide a badgename and an accountid (you'll find that in the url of your networkprofile).

declare @accountid int = ##accountid?7246920##
declare @badgename nvarchar(50) = ##badgename:string?enthusiast##

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , badgename nvarchar(250)
                       , userid int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , ( select name
         from ' + quotename(name) + '.dbo.badges b
         where b.userid = u.id
         and b.name = ''' + @badgename + ''' collate sql_latin1_general_cp1_ci_ai
     ) as bname
     , u.id 
from ' + quotename(name) + '.dbo.users u 
where u.accountid = ' + cast(@accountid as nvarchar) + '
'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

print @sql

-- execute it
exec (@sql)

-- show results
select
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + '/badges/'
      + '|'
      + replace(site, 'stackexchange.','')
      -- rest of columns
      , badgename
      , userid
from #results
where badgename is null
-- and userid is not null

drop table #results

For the enthusiast badge for your account the outcome today is:

The query basically insert for each database if you have an account and if so, if the badge you're looking for exists for your userid. A final query filters out the rows that did return a badgename, leaving the rows with sitenames that you don't have the badge yet.

Keep in mind SEDE is only updated once a week.