++++++++++++++++++++++++++++ [0] Q: what's the best repo A: SELECT repo_name, COUNT(*) AS count FROM github_events WHERE type = 'PullRequestEvent' GROUP BY repo_name ORDER BY count DESC LIMIT 1 ++++++++++++++++++++++++++++ [1] Q: show me the SQL to get current DAY of this MONTH A: SELECT CONCAT('SELECT * FROM github_events WHERE YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) AND DAY(created_at) = ', DAY(CURDATE()), ' ++++++++++++++++++++++++++++ [2] Q: How many issues did I created in pingcap/tidb in last three months A: SELECT COUNT(*) FROM github_events WHERE type = 'IssuesEvent' AND actor_login = 'YOUR_GITHUB_USERNAME' AND repo_name = 'pingcap/tidb' AND created_at > '2017-01-01' ++++++++++++++++++++++++++++ [3] Q: Which companies contribute the most to Kubernetes? A: SELECT actor_login, COUNT(*) AS count FROM github_events WHERE repo_name = 'kubernetes' GROUP BY actor_login ORDER BY count DESC LIMIT 10 ++++++++++++++++++++++++++++ [4] Q: How many repo were written by js A: SELECT COUNT(*) FROM github_events WHERE language = 'JavaScript' ++++++++++++++++++++++++++++ [5] Q: How many contributors in @pingcap/tidb A: SELECT COUNT(DISTINCT actor_login) FROM github_events WHERE repo_name = 'tidb' ++++++++++++++++++++++++++++ [6] Q: Top 10 developers from PingCAP who contributes most of PRs in the past year A: SELECT actor_login, COUNT(*) AS num_of_prs FROM github_events WHERE type = 'PullRequestEvent' AND created_at >= '2017-01-01' AND created_at < '2018-01-01' AND actor_login IN (SELECT actor_login FROM github_events WHERE type = 'PushEvent' AND created_at >= '2017-01-01' AND created_at < '2018-01-01' AND actor_login LIKE '%pingcap%') GROUP BY actor_login ORDER BY num_of_prs DESC LIMIT 10 ++++++++++++++++++++++++++++ [7] Q: how many repos are written in Python? A: ++++++++++++++++++++++++++++ [8] Q: find the most popular repo from github_events A: SELECT repo_name, COUNT(*) AS count FROM github_events GROUP BY repo_name ORDER BY count DESC LIMIT 1 ++++++++++++++++++++++++++++ [9] Q: find how many developer come from US A: SELECT COUNT(*) FROM github_events WHERE actor_login IN (SELECT login FROM users WHERE location LIKE '%US%') ++++++++++++++++++++++++++++ [10] Q: latest commit TIME A: SELECT * FROM github_events WHERE type = 'PushEvent' ORDER BY created_at DESC LIMIT 1 ++++++++++++++++++++++++++++ [11] Q: get the total count of events A: SELECT COUNT(*) FROM github_events ++++++++++++++++++++++++++++ [12] Q: get the number of rows in event table A: SELECT COUNT(*) FROM github_events ++++++++++++++++++++++++++++ [13] Q: Top 10 developers from PingCAP who contributes most of PRs in the past year A: SELECT actor_login, COUNT(*) AS num_of_prs FROM github_events WHERE type = 'PullRequestEvent' AND created_at >= '2017-01-01' AND created_at < '2018-01-01' AND actor_login IN (SELECT actor_login FROM github_events WHERE type = 'PushEvent' AND created_at >= '2017-01-01' AND created_at < '2018-01-01' AND actor_login LIKE '%pingcap%') GROUP BY actor_login ORDER BY num_of_prs DESC LIMIT 10 ++++++++++++++++++++++++++++ [14] Q: give me sql to find how many repo were written by python A: SELECT COUNT(*) FROM github_events WHERE language = 'Python' ++++++++++++++++++++++++++++ [15] Q: sql function to get current time A: SELECT NOW() ++++++++++++++++++++++++++++ [16] Q: which repo has the most actor A: SELECT repo_name, COUNT(actor_id) AS actor_count FROM github_events GROUP BY repo_name ORDER BY actor_count DESC LIMIT 1 ++++++++++++++++++++++++++++ [17] Q: which pr in @pingcap/tidb has the most comments A: ++++++++++++++++++++++++++++ [18] Q: what day is the day that @pingcap/tidb has the most commits A: ++++++++++++++++++++++++++++ [19] Q: which repo has the most unmerged pr A: SELECT repo_name, COUNT(*) AS unmerged_pr_count FROM github_events WHERE type = 'PullRequestEvent' AND state = 'open' GROUP BY repo_name ORDER BY unmerged_pr_count DESC LIMIT 1 ++++++++++++++++++++++++++++ [20] Q: what is the most popular language in the past year A: SELECT language, COUNT(*) AS count FROM github_events WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY language ORDER BY count DESC LIMIT 1 ++++++++++++++++++++++++++++ [21] Q: which repo has the most open issues A: ++++++++++++++++++++++++++++ [22] Q: what language is the most popular in repo @pingcap/tikv A: SELECT language, COUNT(*) AS count FROM github_events WHERE repo_name = '@pingcap/tikv' GROUP BY language ORDER BY count DESC LIMIT 1 ++++++++++++++++++++++++++++ [23] Q: I would like to know how many languages appear in pingcap/tidb and what they all are A: SELECT DISTINCT language FROM github_events WHERE repo_name = 'pingcap/tidb' ++++++++++++++++++++++++++++ [24] Q: Which user has given the most pr comments A: SELECT actor_login, COUNT(*) AS pr_comments_count FROM github_events WHERE type = 'PullRequestReviewCommentEvent' GROUP BY actor_login ORDER BY pr_comments_count DESC LIMIT 1 ++++++++++++++++++++++++++++ [25] Q: Which repo has the most commits in the past year A: SELECT repo_name, COUNT(*) AS num_commits FROM github_events WHERE type = 'PushEvent' GROUP BY repo_name ORDER BY num_commits DESC LIMIT 1 ++++++++++++++++++++++++++++ [26] Q: who created the most pr in US A: SELECT actor_login, COUNT(*) AS pr_count FROM github_events WHERE type = 'PullRequestEvent' GROUP BY actor_login ORDER BY pr_count DESC LIMIT 1 ++++++++++++++++++++++++++++ [27] Q: how many closed pr in pingcap/tidb in the past year A: SELECT COUNT(*) FROM github_events WHERE repo_name = 'pingcap/tidb' AND type = 'PullRequestEvent' AND created_at > '2017-01-01' AND state = 'closed' ++++++++++++++++++++++++++++ [28] Q: are there any repo that has more than 1000 commits in the past year A: SELECT COUNT(*) FROM github_events WHERE type = 'PushEvent' AND created_at > DATE_SUB(NOW(), INTERVAL 1 YEAR) ++++++++++++++++++++++++++++ [29] Q: how many pr closed at christmas last year A: SELECT COUNT(*) FROM github_events WHERE type = 'PullRequestEvent' AND closed_at LIKE '%2016-12-25%' ++++++++++++++++++++++++++++ [30] Q: who created more than 500 pr A: SELECT actor_login, COUNT(*) AS pr_count FROM github_events WHERE type = 'PullRequestEvent' GROUP BY actor_login HAVING pr_count > 500 ++++++++++++++++++++++++++++ [31] Q: show the top 10 contributors A: SELECT actor_login, COUNT(*) AS count FROM github_events GROUP BY actor_login ORDER BY count DESC LIMIT 10 ++++++++++++++++++++++++++++ [32] Q: get all repo id and name A: SELECT id, name FROM github_repos ++++++++++++++++++++++++++++ [33] Q: get all repo id and name and sort it A: SELECT repo_id, repo_name FROM github_events ORDER BY repo_id ++++++++++++++++++++++++++++ [34] Q: get all repo id and name and remove replication A: SELECT DISTINCT repo_id, repo_name FROM github_events ++++++++++++++++++++++++++++ [35] Q: get all repo names include 'golang' A: SELECT repo_name FROM github_events WHERE repo_name LIKE '%golang%' ++++++++++++++++++++++++++++ [36] Q: find who commits in at least 2 repo A: SELECT actor_login, COUNT(DISTINCT repo_name) AS repo_count FROM github_events WHERE type = 'PushEvent' GROUP BY actor_login HAVING repo_count >= 2 ++++++++++++++++++++++++++++ [37] Q: find who commits in at least 2 repo in the same day A: SELECT DISTINCT actor_login FROM github_events WHERE type = 'PushEvent' GROUP BY actor_login, DATE(created_at) HAVING COUNT(DISTINCT repo_name) >= 2 ++++++++++++++++++++++++++++ [38] Q: how many commiter from US A: SELECT COUNT(*) FROM github_events WHERE actor_login IN (SELECT login FROM github_users WHERE location LIKE '%US%') ++++++++++++++++++++++++++++ [39] Q: get all repos that have active commits from 2014 to 2022 A: SELECT * FROM github_events WHERE created_at BETWEEN '2014-01-01' AND '2022-01-01' AND type = 'PushEvent' ++++++++++++++++++++++++++++ [40] Q: which repo has most issues A: SELECT repo_name, COUNT(*) AS issue_count FROM github_events WHERE type = 'IssuesEvent' GROUP BY repo_name ORDER BY issue_count DESC LIMIT 1 ++++++++++++++++++++++++++++ [41] Q: get all type A: SELECT DISTINCT type FROM github_events ++++++++++++++++++++++++++++ [42] Q: list 10 commiter that have more than 500 prs A: SELECT actor_login, COUNT(*) AS pr_count FROM github_events WHERE type = 'PullRequestEvent' GROUP BY actor_login HAVING pr_count > 500 ORDER BY pr_count DESC LIMIT 10 ++++++++++++++++++++++++++++ [43] Q: list 10 commiter that have more than 500 prs , and order them by id A: ++++++++++++++++++++++++++++ [44] Q: list all org A: SELECT * FROM github_events WHERE org_login = 'apache' ++++++++++++++++++++++++++++ [45] Q: list all org and sort them by the frequency of using python A: SELECT org_login, COUNT(*) AS count FROM github_events WHERE language = 'Python' GROUP BY org_login ORDER BY count DESC ++++++++++++++++++++++++++++ [46] Q: create table name test in database gharchive_dev, including one filed num A: SELECT * FROM github_events WHERE type = 'PullRequestEvent' AND repo_name = 'rails/rails' AND created_at >= '2017-01-01' AND created_at < '2017-02-01' AND action = 'closed' AND pr_merged = 'true' AND pr_changed_files > 10 AND pr_review_comments > 0 AND pr_merged_at IS NOT NULL ++++++++++++++++++++++++++++ [47] Q: how many repos are written in Python? please insert the result to the previous table A: SELECT COUNT(*) FROM github_events WHERE language = 'Python' ++++++++++++++++++++++++++++ [48] Q: please drop table test A: SELECT * FROM github_events WHERE type = 'PushEvent' AND repo_id = '1296269' AND actor_id = '9919' AND repo_name = 'react' AND actor_login = 'gaearon' AND language = 'JavaScript' AND additions = '0' AND deletions = '0' AND action = 'commented' AND number = '0' AND org_login = 'facebook' AND org_id = '1296269' AND state = 'open' AND closed_at = '0' AND comments = '0' AND pr_merged_at = '0' AND pr_merged = '0' AND pr_changed_files = '0' ++++++++++++++++++++++++++++ [49] Q: get all collaborator that has more prs than owner A: SELECT * FROM github_events WHERE actor_login != repo_name AND type = 'PullRequestEvent' AND action = 'opened' AND repo_name = 'rails/rails' AND actor_login IN (SELECT actor_login FROM github_events WHERE type = 'PullRequestEvent' AND action = 'opened' AND repo_name = 'rails/rails' GROUP BY actor_login HAVING COUNT(*) > (SELECT COUNT(*) FROM github_events WHERE type = 'PullRequestEvent' AND action = 'opened' AND repo_name = 'rails/rails' AND actor_login = repo_name)) ++++++++++++++++++++++++++++ [50] Q: who contribute to tiDB and kubernetes as the same time A: SELECT * FROM github_events WHERE repo_name = 'tidb' AND actor_login IN (SELECT actor_login FROM github_events WHERE repo_name = 'kubernetes') ++++++++++++++++++++++++++++ [51] Q: who contribute to tiDB and kubernetes as the same time, please use join not or A: SELECT * FROM github_events WHERE repo_name = 'tidb' AND actor_login IN (SELECT actor_login FROM github_events WHERE repo_name = 'kubernetes') ++++++++++++++++++++++++++++ [52] Q: get current time in utc+8 A: SELECT NOW() + INTERVAL 8 HOUR ++++++++++++++++++++++++++++ [53] Q: list the pr amount of each month of 2020 , and sort it. A: SELECT COUNT(pr_or_issue_id) AS pr_amount, MONTH(pr_or_issue_created_at) AS month FROM github_events WHERE YEAR(pr_or_issue_created_at) = 2020 GROUP BY MONTH(pr_or_issue_created_at) ORDER BY pr_amount DESC ++++++++++++++++++++++++++++ [54] Q: Top languages in the open source world over the past four years A: SELECT language, COUNT(*) AS count FROM github_events WHERE type = 'PushEvent' GROUP BY language ORDER BY count DESC LIMIT 10 ++++++++++++++++++++++++++++ [55] Q: Geographic distribution of developer behavior A: SELECT COUNT(*) AS count, country_code FROM github_users GROUP BY country_code ORDER BY count DESC LIMIT 10 ++++++++++++++++++++++++++++ [56] Q: Ranking of back-end programming languages A: SELECT language, COUNT(*) AS count FROM github_events WHERE type = 'PushEvent' GROUP BY language ORDER BY count DESC LIMIT 10 ++++++++++++++++++++++++++++ [57] Q: Developer behavior distribution on weekdays and weekends A: SELECT CASE WHEN DAYOFWEEK(created_at) IN (1, 7) THEN 'weekend' ELSE 'weekday' END AS day_type, COUNT(*) AS count FROM github_events GROUP BY day_type