9 CNCF projects groupped data for last 12 months 201604-201703

README.txt

1. Use cncf.io page and then all 9 projects repos on github (this is the base)
2. Use finder sql with all 9 projects to find their repos, also some of them moved during last year. We need complex condition
  to get all CNCF's projects data (possible some more) - it will be postprocessed by ruby tool.
3. Got final condition and saved results to google sheet.
4. File `data.csv` manually updated (added project value where it is different than org)
5. File `data.csv` processed by `analysis.rb` to produce `projects.csv` to be used as google sheet chart source.

Files:
`finder.sql` to lookup github repos
`final.sql` final bigquery that produces input data for ruby tool
`data.csv` - data retruned by `final.sql` with added column "project" and values for project set in rows where it should be different than "org" column value
`analysis.rb` - ruby script to analyse `data.csv` and produce `projects.csv`
`projects.csv` - analyse output that will be used for google sheet chart input data. 

Final chart here: https://docs.google.com/spreadsheets/d/1vuHdxQ1pgwLUUSSlJvJ6i_CzEuHChkxZYmkpxQTURqM/edit?usp=sharing

analysis.rb

require 'csv'
require 'pry'

orgs = {}
projects = {}
CSV.foreach('./data.csv', headers: true) do |row|
  h = row.to_h
  k = h['project']
  if k
    projects[k] = 0 unless projects.key?(k)
    projects[k] += 1
  end
  k = h['org'] unless k
  k = h['repo'] unless k
  next unless k
  orgs[k] = { items: [] } unless orgs.key? k
  h.each do |p, v|
    vi = v.to_i
    vis = vi.to_s
    h[p] = vi if vis == v
  end
  orgs[k][:items] << h
end

orgs.each do |name, org|
    org[:sum] = {}
    org[:items].each do |repo|
        repo.each do |k, v|
          if v.is_a?(String)
            if repo['org'] && k == 'repo' && v.include?('/')
              v = v.split('/')[1]
            end
            if org[:sum].key? k
              org[:sum][k] = '' if org[:sum][k].nil?
              org[:sum][k] += '+' + v
            else
              org[:sum][k] = v 
            end
          elsif v.is_a?(Integer)
            org[:sum][k] = 0 unless org[:sum].key? k
            org[:sum][k] += v
          else
            org[:sum][k] = nil
          end
        end
    end
    new_org = org[:sum]['org']
    org[:sum]['org'] = new_org.split('+').uniq.join('+') if new_org
    new_prj = org[:sum]['project']
    org[:sum]['project'] = new_prj.split('+').uniq.join('+') if new_prj
end

orgs_arr = []
orgs.each do |name, org|
  orgs_arr << [name, org[:sum]['activity'], org]
end

res = orgs_arr.sort_by { |item| -item[1] }

puts "Defined projects: "
prjs = []
projects.keys.sort.each do |k|
  prjs << "#{k}: #{projects[k]}"
end
prjs = prjs.join(', ')
puts prjs

tops = res[0..30].map { |it| it[0] }.join(', ')
puts tops
puts 'Now debug values res[0][2][:items][0][\'project\'] = \'proj_name\' and save'
puts 'res[0..20].map { |it| it[0] }'
binding.pry

CSV.open("projects.csv", "w", headers: res[0][2][:sum].keys) do |csv|
  csv << res[0][2][:sum].keys
  res.each do |row|
    csv << row[2][:sum].values
  end
end

res2 = []
res3 = []
res.each do |line|
  org = line[2]
  org[:items].each do |repo|
    res2 << [repo['repo'], repo['activity'], repo]
    res3 << [repo['repo'], repo['activity'], repo]
  end
  res3 << [org[:sum]['repo'], org[:sum]['activity'], org[:sum]]
end

res2 = res2.sort_by { |item| -item[1] }
res3 = res3.sort_by { |item| -item[1] }

CSV.open("combined.csv", "w", headers: res3[0][2].keys) do |csv|
  csv << res3[0][2].keys
  res3.each do |row|
    csv << row[2].values
  end
end

data.csv

final.sql

select
  org.login as org,
  repo.name as repo,
  count(*) as activity,
  SUM(IF(type = 'IssueCommentEvent', 1, 0)) as comments,
  SUM(IF(type = 'PullRequestEvent', 1, 0)) as prs,
  SUM(IF(type = 'PushEvent', 1, 0)) as commits,
  SUM(IF(type = 'IssuesEvent', 1, 0)) as issues,
  EXACT_COUNT_DISTINCT(JSON_EXTRACT(payload, '$.commits[0].author.email')) AS authors
from 
  (select * from
    [githubarchive:month.201604],
    [githubarchive:month.201605],
    [githubarchive:month.201606],
    [githubarchive:month.201607],
    [githubarchive:month.201608],
    [githubarchive:month.201609],
    [githubarchive:month.201610],
    [githubarchive:month.201611],
    [githubarchive:month.201612],
    [githubarchive:month.201701],
    [githubarchive:month.201702],
    [githubarchive:month.201703]
  )
where
  (
    org.login in (
      'kubernetes', 'prometheus', 'opentracing', 'fluent', 'linkerd', 'grpc', 'coredns', 'containerd',
      'rkt', 'opentracing-contrib', 'kubernetes-client', 'kubernetes-contrib', 'kubernetes-cluster-automation',
      'kubernetes-incubator', 'kubernetes-ui'
    )
    or repo.name in (
      'docker/containerd', 'coreos/rkt', 'fabric8io/docker-fluentd', 'fabric8io/docker-fluentd-kubernetes',
      'deis/fluentd', 'grpc-ecosystem/grpc-opentracing', 'coreos/kube-prometheus', 'coreos/prometheus',
      'coreos/prometheus-operator', 'coreos/coreos-kubernetes', 'coreos/kubernetes', 'abric8io/kubernetes-client',
      'abric8io/kubernetes-model', 'vmware/kubernetes'
    )
  )
  and type in ('IssueCommentEvent', 'PullRequestEvent', 'PushEvent', 'IssuesEvent')
  and actor.login not like '%bot%'
  AND actor.login NOT IN (
    SELECT
      actor.login
    FROM (
      SELECT
        actor.login,
        COUNT(*) c
      FROM
      [githubarchive:month.201603]
      WHERE
        type = 'IssueCommentEvent'
      GROUP BY
        1
      HAVING
        c > 1000
      ORDER BY
      2 DESC
    )
  )
group by org, repo
order by
  activity desc
limit 10000;

finder.sql

select
  org.login as org,
  repo.name as repo,
  count(*) as cnt
from 
  [githubarchive:month.201604],
  [githubarchive:month.201610],
  [githubarchive:month.201703]
where
  org.login is not null and
  repo.name like '%<<insert_one_of_9_cncf_projects>>'
group by
  org, repo
order by
  org, repo

projects.csv