Badge count across the network to match what would be your flair, but for meta sites only

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 only for site meta's

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

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 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