Quick query on portfolio effects

This post is just a quick note.

I was helping a customer the other day and wrote the following back-of-the-envelope query for them:

select count(*), sub.apps from (
  SELECT count(distinct app_id) as apps,
         advertising_id
  FROM events as e
  WHERE created_at > '2019-03-01'
  GROUP BY 2
  order by 1 desc
  limit 20000000) sub
group by 2
order by 2 desc;

This query creates a histogram showing the the number of users that play x number of games in their portfolio of apps. What we found is that there are several users in the last month that will play up to 60 apps in their portfolio!

You can see that there must be some level of synergistic value in portfolio network effects beyond direct user acquisition.