I am aware that there is a list of top users for each of the Stack Exchange sites. However, I am wondering if there is a list showing the combined reputation of each Stack Exchange user from every of the sites they have a profile in. For example if a user has 5000 on SO, 5000 on Server Fault and 5000 on Superuser they should be listed as having a combined reputation of 15000 in such a list.
There were so many attempts the last few days to either improve or update this question that I decided to have a go with a multi-database query in SEDE.
This beast is the result:
-- start create url from dbname IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL DROP PROCEDURE #siteurl; GO create procedure #siteurl @dbname nvarchar(250), @res nvarchar(250) OUT as begin -- we have three sites that have borked DNS entries set @dbname = (case @dbname when 'StackExchange.Audio' then 'StackExchange.Avp' when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta' when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta' else @dbname end) -- and one of those doesn't want to end with .com if @dbname <> 'net.Mathoverflow.meta' set @dbname = 'com.' + @dbname exec #recursesiteurl @dbname, @res OUTPUT end GO -- create url from dbname IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL DROP PROCEDURE #recursesiteurl; GO create procedure #recursesiteurl @dbname nvarchar(250), @res nvarchar(250) OUT as begin declare @pos integer declare @rec nvarchar(250) set @res = @dbname set @pos = CHARINDEX('.', @dbname) if (@pos > 0 ) begin set @rec = substring(@dbname, @pos+1, len(@dbname)) exec #recursesiteurl @rec, @rec output set @res = @rec + '.' + substring(@dbname, 0, @pos) end end; GO -- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE and ([name] not like '%.Meta' or [Name] = 'StackExchange.Meta') declare @db_c_name sysname -- holds name of db after fetch declare @sql nvarchar(max) -- holds build up sql string -- result table create table #all_users ( site nvarchar(250) , userid int , rep int , accountid int , displayname nvarchar(250)); open db_c fetch next from db_c into @db_c_name while(@@FETCH_STATUS = 0) begin set @sql = N'use '+ QUOTENAME(@db_c_name) +'; declare @url nvarchar(250) exec #siteurl ''' + @db_c_name + ''', @url output insert into #all_users select @url , id , reputation , accountid , displayname from users;' exec (@sql) fetch next from db_c into @db_c_name end; close db_c; deallocate db_c; ;with max_user_site as ( select row_number() over(order by sum(rep) desc) as rank , accountid , sum(rep) as tot_rep from #all_users group by accountid ), max_site_for_user as ( select userid , accountid , displayname , site , rep from #all_users au where rep = (select max(rep) from #all_users where accountid = au.accountid) ), max_user_site_excl_main as ( select row_number() over(order by sum(rep) desc) as rank , accountid , sum(rep) as tot_rep from #all_users au where rep < (select max(rep) from #all_users where accountid = au.accountid) group by accountid ) select mus.rank , replace( replace( replace(msu.site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + msu.site + '/users/' + cast(msu.userid as nvarchar) + '|' + msu.displayname as [user] , mus.tot_rep as [total rep] , replace( replace( replace(msu2.site, '.StackExchange.com',''), '.com',''), '.net','') as [site] , 'http://' + msu2.site + '/users/' + cast(msu2.userid as nvarchar) + '|' + msu2.displayname as [user] , msuem.tot_rep as [total rep] from max_user_site mus inner join max_site_for_user msu on msu.accountid = mus.accountid inner join max_user_site_excl_main msuem on mus.rank = msuem.rank inner join max_site_for_user msu2 on msu2.accountid = msuem.accountid order by mus.rank drop table #all_users drop procedure #recursesiteurl drop procedure #siteurl
I implemented the suggestion from CodesInChaos to show the rank without their main site rep included. If you let it run for a while you'll get this result today:
The following users were the only users with networkwide more than 600k reputation in May 2016:
- Jon Skeet - 949k (Stack Overflow, Meta)
- Marc Gravell - 694k (Stack Overflow, Meta)
- Gilles - 677k (Unix & Linux, Meta, Stack Overflow)
- Darin Dimitrov - 662k (Stack Overflow)
- BalusC - 653k (Stack Overflow)
- Hans Passant - 634k (Stack Overflow)
- VonC - 617k (Stack Overflow)
- Ignacio Vazquez-Abrams - 606k (Stack Overflow, Super User)
Notes: The sites are all sites they have more than 50k rep on.
As we see, it is very unlikely that anyone will pass Jon Skeet, at least not any time soon.
Besides, the chat list sorted by reputation can be useful, but users that do not use chat are missing and some of the numbers are horribly wrong, also when users don't use chat.