Badges by rarity

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
create table #results (name nvarchar(50), badges 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, COUNT(*) FROM ' + quotename(name) + '.dbo.Badges
  WHERE TagBased = 0 GROUP BY Name '
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql)

select name, sum(badges) AS '# of badges networkwide',
  count(*) AS '# of sites with badge' from #results
  group by name
  order by sum(badges)
  
drop table #results

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results