Users with high reputation on several Stack Exchange network sites

Question

It's easy to track down the users with most reputation on a particular network site. I am much more interested in those universal geniuses who have earned a lot of reputation on many different sites. Is there a way to ask the Stack Exchange Data Explorer about, say, people with more than 5k reputation on at least two sites?

Answer 1

I have created a query to find your definition of universal geniuses, which is available here.

The query basically loops through all available databases (excluding meta sites where user reputation is the same as on its corresponding site) and picks users with more than n reputation (default 5000) on at least n different sites (default 2).

It reveals that by your definition, Gilles is currently the "most universal genius", as s/he has more than 5000 reputation on the 11 different sites: Unix:211723, Meta:50246, StackOverflow:37746, SuperUser:30930, French:26017, Ubuntu:22065, Scifi:19700, Security:17464, Cs:14029, Travel:8984, ServerFault:6149.

Query:

-- 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 
  'https://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