11

Which users have the most accounts with over 200 reputation across the Stack Exchange networks?
Is it common or is it rare? I don't necessarily need a "best answer" - any answer should help (as I tend to be someone who distributes my activity across multiple networks as well).

7

The "association id" or AccountId is now available, so I produced the requested query here.

Note that I explicitly skip all Metas except this one, so that the query results should be the same as the number of sites with more than 200 rep that you see in a user's accounts tab.

6

While it is possible to do cross-site queries using the Data Explorer, I don't believe it is currently possible to use the Data Explorer to get the association id that links users across sites, so for now this would have to be done locally using a data dump, rather than online.

5

A few weeks ago I created a similar query, which automatically scales to new Stack Exchange sites and provides a flexible reputation limit; it works for 200 as well for other limits. This is the current top 10:

enter image description here

You're ranked #345 at the moment:

enter image description here

For reference, here is the complete query.

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , accountid int
                       , displayname nvarchar(40));

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union all'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , accountid
     , displayname
from ' + quotename(name) + '.dbo.users 
where reputation >= ' + cast(##minimumReputation:int?125## as nvarchar)
from sys.databases
where database_id > 5
  and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql);

with allusers as (
select
  rank() over (order by count(*) desc) as 'Rank',
  'https://stackexchange.com/users/' + cast(accountid as nvarchar)
  + '?tab=accounts|' + max(displayname) as 'User',
  count(*) AS '# of sites',
  accountid
  from #results
  group by accountid
)

-- show results
select 'Selected user' as 'Category'
     , [Rank], [User], [# of sites]
from allusers
where accountid = ##AccountId:int##
union all
select 'All users'
     , [Rank], [User], [# of sites]
from allusers
order by 1 desc, [# of sites] desc
-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID; choose if you don't want to focus on a user.

drop table #results

Note that SEDE is updated once a week, on Sunday morning.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .