Users with reputation on many sites #2

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- 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 selected user (whose position in the list will be shown). You can find this in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID; choose 0 if you don't want to focus on a user.

drop table #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results