4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of chess

-- Select all valid databases.
select dbid, name
into #dbs
from master..sysdatabases
where name not in ('Data.StackExchange', 'master', 'model', 'msdb', 'tempdb')
  and right(name, 5) <> '.Meta'
   or name = 'StackExchange.Meta'

-- Loop through databases to find all users with high enough reputation.
create table #topusers (
  accountid int,
  userid int,
  rep int,
  site varchar(40)
)
declare @id int, @db varchar(40), @sql varchar(max)
while (select count(*) from #dbs) > 0
begin
  select top 1 @id = dbid, @db = name from #dbs
  set @sql = '
    insert into #topusers
    select AccountId, Id, Reputation, ''' + @db + '''
    from [' + @db + ']..[Users]
    where Reputation > ' + cast(##MinReputation:int?5000## as varchar(max))
  exec (@sql)
  delete from #dbs where dbid = @id
end

-- Summarize the results nice and clean
select 
  'http://stackexchange.com/users/' + cast(accountid as varchar(15)) 
    + '|' + cast(accountid as varchar(15)) as [Account Link],
  count(accountid) as [Site count],
  stuff (
    (select ', ' + 
     case when left(site, 14) = 'stackexchange.' 
       then substring(site, 15, len(site))
       else site
     end + ':' + cast(rep as varchar(max)) 
       from #topusers 
       where (accountid = u.accountid)
       order by rep desc
       for xml path(''),type
       ).value('(./text())[1]','varchar(max)')
    ,1,2,'') as [Site:rep]
from #topusers u
group by accountid having count(accountid) >= ##OnMinSites:int?2##
order by count(accountid) desc

fork query

edited aug 25 14

created aug 22 14 TheBanana