bigcode/commitpackmeta

1SELECT commit, old_file, new_file, subject, message, unnested_repo_name2FROM (3  SELECT commit, d.old_path old_file, d.new_path new_file, subject, message, unnested_repo_name4FROM (5SELECT *6FROM (7  SELECT *, ROW_NUMBER()OVER(PARTITION BY commit) AS RN8FROM (9  SELECT10    repo_name,11    lang.name AS language_name12  FROM13    `bigquery-public-data.github_repos.languages` AS lang_table,14    UNNEST(LANGUAGE) AS lang) lang_table15JOIN16  `bigquery-public-data.github_repos.licenses` AS license_table17ON18  license_table.repo_name = lang_table.repo_name19JOIN (20  SELECT21    *22  FROM23    `bigquery-public-data.github_repos.commits` AS commits_table,24    UNNEST(repo_name) AS unnested_repo_name25  LIMIT 10000) commits_table26ON27  commits_table.unnested_repo_name = lang_table.repo_name28WHERE29  ((license_table.license LIKE 'mit')30    OR (license_table.license LIKE 'artistic-2.0')31    OR (license_table.license LIKE 'isc')32    OR (license_table.license LIKE 'cc0-1.0')33    OR (license_table.license LIKE 'apache-2.0')34    OR (license_table.license LIKE 'unlicense')35    OR (license_table.license LIKE 'bsd-3-clause')36    OR (license_table.license LIKE 'bsd-2-clause'))37AND ( 38  LENGTH(commits_table.message) > 10 39)40AND ( 41  LENGTH(commits_table.message) < 10000 42)43AND commits_table.message NOT IN (44'update readme.md', 'initial commit', 'update', 'Mirroring from micro.blog.', 'update data.json', 'update data.js', 'add files via upload', 'update readme', "Can't you see I'm updating the time?", 'pi push', 'dummy', 'update index.html', 'first commit', 'create readme.md', 'heartbeat update', 'updated readme', 'update log', 'test', 'no message', 'readme', 'wip', 'updates', 'commit', 'update _config.yaml'45)46AND commits_table.message not like 'Merge%')47WHERE RN = 1), UNNEST(difference) as d48WHERE (d.old_path = d.new_path) AND (d.old_path IS NOT NULL) AND (d.new_path IS NOT NULL)49)50GROUP BY commit, old_file, new_file, subject, message, unnested_repo_name51HAVING COUNT(commit) = 152 53