16

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?

8
  • 4
    Ah, the dreaded cross-site query. It's technically possible, but a pain in the butt to write (think querying ~120 separate databases...). Apr 8 '14 at 11:23
  • 1
    "universal geniuses". You misunderstand what reputation means. It is a measure of community trust in a user, and nothing else.
    – Louis
    Apr 8 '14 at 11:30
  • 8
    You'll need to consider that it's much, much harder to get reputation in the smaller sites of the network. 5K rep on a young beta site is as awesome as 50K on Stack Overflow, if not more.
    – yannis
    Apr 8 '14 at 11:32
  • "universal geniuses" being tongue-in-cheek for people who are trusted on a lot of stackexchange sites and therefore seem to be able to provide expertise in a lot of different fields.
    – chaosflaws
    Apr 8 '14 at 11:36
  • While @Louis is right, I think your meaning was obvious with "universal geniuses". Apr 8 '14 at 13:13
  • I'm not a native English speaker so I felt that clarification necessary in case I used an inappropriate term.
    – chaosflaws
    Apr 8 '14 at 13:16
  • 1
    @Yannis that may true in general but it does seem to vary by the site and it's very personal in any case. For example on the workplace, recently out of beta, avg votes per q/a is 9/6, on cross-validated it is 3/3, on mathematica 5/5, c.f. the main sites stackoverflow and math which are lower at around 2/2(see data.stackexchange.com/stackoverflow/query/81058/…). Personally, as a programmer using 4 sites on and off, I found stackoverflow somewhere in the middle in terms of ease of gaining reputation (whatever that means!:).
    – TooTone
    Apr 8 '14 at 14:58
  • @michaelb958 The difficulty of cross-site queries is overrated. It is actually not too hard by using built-in system tables.
    – dwitvliet
    Aug 22 '14 at 23:21
12

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
5
  • 4
    I don't think it's necessary to write "s/he" when the display name is Gilles... it's more telling than, say, Banana. :)
    – user259867
    Aug 22 '14 at 23:34
  • I found it interesting to add a (select top 1 Rep from #topusers where accountid = u.accountid order by Rep desc) as 'Top Site Rep' column.
    – mmyers
    Aug 23 '14 at 0:14
  • @mmyers You can just use max(rep) as 'Top Site Rep'
    – dwitvliet
    Aug 23 '14 at 1:17
  • Typo alert: Meta SE is id 37, not 38. (source) Aug 24 '14 at 22:58
  • @michaelb958 Thank you, I fixed it with a cleaner solution - instead of adding Meta.StackExchange after, the query now just doesn't filter it out in the first place.
    – dwitvliet
    Aug 25 '14 at 1:33

You must log in to answer this question.

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