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?
1 Answer
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.