39

I know the new profile design hasn't been updated for every site on the network yet, so maybe this is a bit too early. Nonetheless, please include the "People Reached" data in SEDE at your earliest convenience.

Also, I was just made aware that users helpful flag counts are now publicly displayed on their activity page towards the top-right, underneath the people reached stat. So please include this data in SEDE as well.

  • 2
    Well, it can be calculated with a custom query - I know someone who will gladly write such a query... ;) – Shadow The Dragon Wizard Apr 16 '15 at 8:06
  • 5
    No idea who that could be @ShadowWizard – rene Apr 16 '15 at 10:45
  • 1
    @rene we're so lucky Mr. Roomba doesn't visit our secret room... :D – Shadow The Dragon Wizard Apr 16 '15 at 10:49
  • 4
    It can be calculated for a site by a custom query, but having the field in the data dump would help with cross-site/multi-site queries (like "how many people have I helped, everywhere?"). – Monica Cellio Apr 16 '15 at 14:19
27
+150

I fully support this request. If you don't mind hammer the SEDE database you can use this query that I forked from the original post.

select top 100 
       sum(isnull(p.viewcount,0)) as reached
     , rp.owneruserid as [User Link]
from posts p
inner join (
  Select Id, OwnerUserId 
  From Posts 
  Where PostTypeId = 1 
  and owneruserid is not null
  Union
  Select ParentId, OwnerUserId
    From Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from Posts)
     and owneruserid is not null
  Union
  Select ParentId, OwnerUserId
    From Posts
   Where PostTypeId = 2
     And Score > 5
     and owneruserid is not null
  Union
  Select a.ParentId, a.OwnerUserId
    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 is not null
  Union
  Select x.ParentId, x.OwnerUserId 
    From (Select a.ParentId , a.OwnerUserId, Rank() Over(Partition By a.ParentId, a.OwnerUserId 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 is not null
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
group by rp.owneruserid
order by sum(isnull(p.viewcount,0)) desc

I removed the per user where clause in all union sub queries so you can analyze people reached over the complete community. This query does output results but it takes a while:

people reached SEDE output

  • 8
    That's a lot of milliseconds. – canon May 11 '15 at 13:13
  • Small follow-up question. The query in the answer above returns the value for at most top 50000 users (after which the execution times out). I couldn't find the "people_reached" data in the data dump/SEDE. Is there a way to get this value for all users? – madman_with_a_box Jul 22 '18 at 12:20
  • @madman_with_a_box SEDE never returns more then 50000 rows – rene Jul 22 '18 at 12:25

You must log in to answer this question.

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