Badge count across the network to match what is in your flair

1

Please login or register to vote for this query.

(click on this box to dismiss)

Only take into account sites you have > 200 reputation and leave out site meta's

Space Exploration

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

declare @sql nvarchar(max)

create table #result ( site nvarchar(100)
                    , reputation int
                    , id int
                    , userid int
                    , name nvarchar(50)
                    , [Date] datetime
                    , class tinyint
                    , tagbased bit
                    )

select @sql = 'insert into #result ' + string_agg(concat(N'
select ''', name ,''' as site
, u.reputation 
, b.*
from ', convert(nvarchar(max),quotename(name)), N'.dbo.badges b
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.users u on b.userid = u.id
where u.accountid = ', ##accountid:int?4485506## ,'
and u.reputation > 200
'),N'union all')
from sys.databases
where database_id > 5
and (name not like '%.Meta'  or name = 'StackExchange.Meta')

exec (@sql)

declare @rep int = (select sum(reputation)
         from (
         select min(reputation) reputation
         from #result
         group by site
       ) as rep)

select 'all' [site]
     , @rep reputation
    , count(*) [# badges]
    , sum (cASE class WHEN 1 THEN 1 else 0 end) as gold
    , sum (case class WHEN 2 THEN 1 else 0 end) as silver
    , sum (case class WHEN 3 THEN 1 else 0 end) as bronze                 
from #result
union all
select site
    , reputation
    , count(*) [# badges]
    , sum (cASE class WHEN 1 THEN 1 else 0 end) as gold
    , sum (case class WHEN 2 THEN 1 else 0 end) as silver
    , sum (case class WHEN 3 THEN 1 else 0 end) as bronze                 
from #result
group by site, reputation
order by count(*) desc, reputation desc

Enter Parameters

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