12

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?

9

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:

badges not earned on all sites

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.

You must log in to answer this question.

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