6

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/a/279137/158100

Q&A about the site for gardeners and landscapers

-- 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));
                            
create index ix_all_users on  #all_users( accountid);
                   
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
               where reputation > 1;'
    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 [rep minus main]
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

fork query

edited aug 15 19

created may 7 16 rene