Holders of gold tag badges, networkwide

0

Please login or register to vote for this query.

(click on this box to dismiss)

Engineering

Q&A for professionals and students of engineering

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , accountid int
                       , displayname nvarchar(40)
                       , badgecount 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 + '''
     , u.accountid
     , u.displayname
     , count(*)
from ' + quotename(name) + '.dbo.badges b
inner join ' + quotename(name) + '.dbo.users u on b.userid = u.id
where b.class = 1 and b.tagbased = 1
group by u.accountid, u.displayname '
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql)

select rank() over (order by count(*) desc) as 'Rank',
  'https://stackexchange.com/users/' + CAST(accountid AS nvarchar)
  + '|' + MAX(displayname) AS 'User',
  sum(badgecount) AS 'Total badges',
  count(*) AS 'Total sites'
  from #results
  group by accountid
  order by count(*) desc, sum(badgecount) desc

drop table #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results