Is there a way to get "people reached" sum across entire network for a user?

Question

The New Model profiles on Meta.SE (pretty big improvement!) have a section for "People reached" stat.

Now, unless I'm much mistaken, that stat is specific to a given site on the network.

If that assumption is indeed correct, is there a way to find out the "People Reached" summary across 100% of the sites on the network for all your connected profiles?

Answer 1

As indicated in the comments a SEDE query would do so here it is:

To run it you need your accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 41067, mine is 281857 and Annonomus Penguin is 1640390.

---- 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

This query uses the technique described here to iterate over all databases in SEDE. In the cursor loop I execute this query for each single database and add the results to the temporary table #all_reached. After all databases are processed a select over that temp-table produces this result:

Notice that there is this feature request to include the statistic in the users table so this query wouldn't require that much resources to run.