rails_actors.sql

select 
  all.actor, 
  datediff(all.last_at, all.first_at) as length, 
  all.cnt as all, all.cnt_meaningful as meaningful, 
  datediff(all.last_at, all.first_at)/all.cnt as freq,
  all.cnt_contributions > 0 as is_contributor,
  all.cnt_contributions as contributions
from 
  (SELECT 
    actor_attributes_login as actor, 
    count(*) as cnt,
    sum(if(type = 'PushEvent' || type = 'CreateEvent' || type = 'PullRequestEvent' || type = 'GollumEvent'
     || type = 'IssueCommentEvent' || type = 'IssuesEvent' || type = 'PullRequestReviewCommentEvent' 
     || type = 'CommitCommentEvent' || type = 'MemberEvent' || type = 'ForkApplyEvent', 1, 0)) as cnt_meaningful,
    sum(if(type = 'PushEvent' || type = 'CreateEvent' || type = 'PullRequestEvent' || type = 'GollumEvent', 1, 0)) as cnt_contributions,
    min(created_at) as first_at, 
    max(created_at) as last_at 
   from
    (SELECT *
     FROM [githubarchive:github.timeline] 
     where repository_url = 'https://github.com/rails/rails')      
   group by actor) all 
where all.cnt > 1 and all.cnt_meaningful >= 1
order by contributions desc
limit 100

rails_neighbors.sql

select 'https://github.com/rails/rails' as repository1, t.repository_url as repository2, a.actor as actor, count(*) as cnt
from [githubarchive:github.timeline] t join [githubdata.rails_actors] a on t.actor = a.actor
where t.type = 'PushEvent' || t.type = 'CreateEvent' || t.type = 'PullRequestEvent' || t.type = 'GollumEvent'
and not t.repository_url contains 'https://github.com/rails/rails'
group by repository1, repository2, actor

rails_network2.sql

select repository1, repository2, count(*) as actors_cnt from (
select actors.repository_url as repository1, ti.repository_url as repository2, ti.actor as actor, count(*) as cnt
from [githubarchive:github.timeline] ti
join each
  (
    select t.actor, t.repository_url
    from [githubarchive:github.timeline] t 
    join [githubdata.rails_true_neighbors] n on n.repository2 = t.repository_url
    where t.actor in (select actor from [githubdata.rails_actors_contributors])
    group by t.actor, t.repository_url
  ) actors
on ti.actor = actors.actor
where ti.repository_url in (select repository2 from [githubdata.rails_true_neighbors])
and ti.repository_url <> actors.repository_url
and ti.repository_url < actors.repository_url
and not ti.repository_url contains 'https://github.com/rails/rails'
and (ti.type = 'PushEvent' || ti.type = 'CreateEvent' || ti.type = 'PullRequestEvent' || ti.type = 'GollumEvent')
group each by repository1, repository2, actor) x
group by repository1, repository2;

rails_true_neighbors.sql

select ne.repository1, ne.repository2, ne.actor, ne.cnt, count(*) as contributor_cnt from
( 
  SELECT n.repository2, t.actor as actor_login
  FROM [githubarchive:github.timeline] t join 
    (select repository2 from [githubdata.rails_neighbors] group by repository2) n 
  on t.repository_url = n.repository2
  where t.type = 'PushEvent' or t.type = 'PullRequestEvent'
  group by n.repository2, actor_login
) pushers
join [githubdata.rails_neighbors] ne on ne.repository2 = pushers.repository2
group by ne.repository1, ne.repository2, ne.actor, ne.cnt
having contributor_cnt > 1