9

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/246194/is-there-a-way-to-get-people-reached-sum-across-entire-network-for-a-user

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

---- accountid: Account on stackexchnage.com ! "In the url on stackexchange.com"

-- 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
      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 #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

declare @accountid int = ##accountid:int##  -- 93484

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE
                     
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_reached ( site nvarchar(250)
                            , userid int
                            , displayname nvarchar(50)
                            , reached bigint);
                            
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
declare @userid int = null
declare @displayname nvarchar(50)
select @userid = id 
     , @displayname = displayname
from users where accountid =  '+ cast(@accountid as nvarchar) + '
if @userid is not null 
begin
insert into #all_reached
select @url
     , @userid
     , @displayname
     , sum(isnull(p.viewcount,0))
from posts p
inner join (
  Select Id 
  From Posts 
  Where PostTypeId = 1 
  and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from Posts)
     and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Score > 5
     and owneruserid = @userid
  Union
  Select a.ParentId
    From Posts a
         Join Posts q On a.ParentId = q.Id
   Where a.PostTypeId = 2
     And a.Score > 0.20 * (select sum(Score) from Posts where ParentId=q.Id)
     And a.Score > 0
     and a.owneruserid = @userid
  Union
  Select x.ParentId
    From (Select a.ParentId, Rank() Over(Partition By a.ParentId Order By ta.Score Desc) AnswerRank
            From Posts a
                 Join Posts ta On ta.ParentId = a.ParentId
           Where a.PostTypeId = 2
             And a.Score > 0
             and a.owneruserid = @userid
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
end;'
               
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select 'all ' as [site]
      , 'networkwide' as [display name]
      , sum(reached)
from #all_reached
union all
select replace(site,'.StackExchange','') as [site]
     , 'http://' 
     + site 
     + '.com/'
     + 'users'
     + '/'
     + cast(userid as nvarchar)
     + '|'
     + displayname
     , reached
from #all_reached
order by 3 desc

drop table #all_reached

fork query

edited apr 23 15

created apr 23 15 rene