++++++++++++++++++++++++++++
[0] Q: create a table x with two fields(id, content), inside test database, and insert 10 records
A: CREATE TABLE test.x (
    id INT NOT NULL AUTO_INCREMENT,
    content VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO test.x (content) VALUES
    ('Record 1'),
    ('Record 2'),
    ('Record 3'),
    ('Record 4'),
    ('Record 5'),
    ('Record 6'),
    ('Record 7'),
    ('Record 8'),
    ('Record 9'),
    ('Record 10');
++++++++++++++++++++++++++++
[1] Q: 坚决拥护共产党
A: No SQL Generated.
++++++++++++++++++++++++++++
[2] Q: what's the best repo
A: SELECT repo_id, repo_name, SUM(additions) AS total_additions, SUM(deletions) AS total_deletions
FROM github_events
GROUP BY repo_id, repo_name
ORDER BY total_additions DESC, total_deletions ASC;
++++++++++++++++++++++++++++
[3] Q: show me the SQL to get current DAY of this MONTH
A: SELECT DAY(CURDATE()) AS 'Current Day of this Month';
++++++++++++++++++++++++++++
[4] Q: How many issues did I created in pingcap/tidb in last three months
A: SELECT COUNT(*) 
FROM github_events 
WHERE type = 'IssuesEvent' 
AND repo_name = 'pingcap/tidb' 
AND actor_login = '<your_username>' 
AND created_at > DATE_SUB(NOW(), INTERVAL 3 MONTH);
++++++++++++++++++++++++++++
[5] Q: Which companies contribute the most to Kubernetes?
A: SELECT org_login, COUNT(*) AS contribution_count
FROM github_events
WHERE repo_name = 'Kubernetes'
GROUP BY org_login
ORDER BY contribution_count DESC;
++++++++++++++++++++++++++++
[6] Q: How many repo were written by js
A: SELECT COUNT(*)
FROM github_events
WHERE language = 'js'
AND type = 'PushEvent';
++++++++++++++++++++++++++++
[7] Q: How many contributors in @pingcap/tidb
A: SELECT COUNT(DISTINCT actor_login) 
FROM github_events 
WHERE repo_name = '@pingcap/tidb';
++++++++++++++++++++++++++++
[8] Q: Top 10 developers from PingCAP who contributes most of PRs in the past year
A: SELECT actor_login, COUNT(*) AS PR_count
FROM github_events
WHERE type = 'PullRequestEvent'
AND org_login = 'PingCAP'
AND event_year = YEAR(CURDATE())
GROUP BY actor_login
ORDER BY PR_count DESC
LIMIT 10;
++++++++++++++++++++++++++++
[9] Q: how many repos are written in Python?
A: SELECT COUNT(*) 
FROM github_events 
WHERE language = 'Python';
++++++++++++++++++++++++++++
[10] Q: find the most popular repo from github_events
A: SELECT repo_name, COUNT(*) AS Popularity
FROM github_events
GROUP BY repo_name
ORDER BY Popularity DESC
LIMIT 1;
++++++++++++++++++++++++++++
[11] Q: find how many developer come from US
A: SELECT COUNT(*) 
FROM github_events 
WHERE actor_location LIKE '%US%';
++++++++++++++++++++++++++++
[12] Q: latest commit TIME
A: SELECT MAX(created_at)
FROM github_events
WHERE type = 'PushEvent';
++++++++++++++++++++++++++++
[13] Q: get the total count of events
A: SELECT COUNT(*) FROM github_events;
++++++++++++++++++++++++++++
[14] Q: get the number of rows in event table
A: SELECT COUNT(*) FROM github_events;
++++++++++++++++++++++++++++
[15] Q: Top 10 developers from PingCAP who contributes most of PRs in the past year
A: SELECT actor_login, COUNT(*) AS PR_Count
FROM github_events
WHERE type = 'PullRequestEvent'
AND org_login = 'PingCAP'
AND event_year = YEAR(CURDATE())
GROUP BY actor_login
ORDER BY PR_Count DESC
LIMIT 10;
++++++++++++++++++++++++++++
[16] Q: give me sql to find how many repo were written by python
A: SELECT COUNT(*) 
FROM github_events 
WHERE language = 'Python';
++++++++++++++++++++++++++++
[17] Q: sql function to get current time
A: SELECT CURRENT_TIMESTAMP();
++++++++++++++++++++++++++++
[18] Q: which repo has the most actor 
A: SELECT repo_id, COUNT(actor_id) AS actor_count
FROM github_events
GROUP BY repo_id
ORDER BY actor_count DESC
LIMIT 1;
++++++++++++++++++++++++++++
[19] Q: which pr in @pingcap/tidb has the most comments
A: SELECT repo_name, pr_or_issue_id, comments
FROM github_events
WHERE type = 'PullRequestEvent'
AND org_login = '@pingcap/tidb'
ORDER BY comments DESC
LIMIT 1;
++++++++++++++++++++++++++++
[20] Q: what day is the day that @pingcap/tidb has the most commits
A: SELECT event_day, COUNT(*) AS commit_count
FROM github_events
WHERE repo_name = '@pingcap/tidb'
AND type = 'PushEvent'
GROUP BY event_day
ORDER BY commit_count DESC
LIMIT 1;
++++++++++++++++++++++++++++
[21] Q: which repo has the most unmerged pr
A: SELECT repo_name, COUNT(*) AS unmerged_pr
FROM github_events
WHERE type = 'PullRequestEvent' AND pr_merged = 0
GROUP BY repo_name
ORDER BY unmerged_pr DESC;
++++++++++++++++++++++++++++
[22] Q: what is the most popular language in the past year
A: SELECT language, COUNT(*) AS language_count
FROM github_events
WHERE event_year = YEAR(CURDATE())
GROUP BY language
ORDER BY language_count DESC
LIMIT 1;
++++++++++++++++++++++++++++
[23] Q: which repo has the most open issues
A: SELECT repo_name, COUNT(*) AS open_issues
FROM github_events
WHERE type = 'IssuesEvent' AND state = 'open'
GROUP BY repo_name
ORDER BY open_issues DESC;
++++++++++++++++++++++++++++
[24] 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;
++++++++++++++++++++++++++++
[25] 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';
++++++++++++++++++++++++++++
[26] Q: Which user has given the most pr comments
A: SELECT actor_login, COUNT(*) AS pr_comment_count
FROM github_events
WHERE type = 'PullRequestReviewCommentEvent'
GROUP BY actor_login
ORDER BY pr_comment_count DESC
LIMIT 1;
++++++++++++++++++++++++++++
[27] Q: Which repo has the most commits in the past year
A: SELECT repo_name, COUNT(*) AS commits_count
FROM github_events
WHERE event_year = YEAR(CURDATE())
AND type = 'PushEvent'
GROUP BY repo_name
ORDER BY commits_count DESC
LIMIT 1;
++++++++++++++++++++++++++++
[28] Q: who created the most pr in US
A: SELECT actor_login, COUNT(*) AS pr_count
FROM github_events
WHERE type = 'PullRequestEvent' AND actor_location LIKE '%US%'
GROUP BY actor_login
ORDER BY pr_count DESC;
++++++++++++++++++++++++++++
[29] Q: how many closed pr in pingcap/tidb in the past year
A: SELECT COUNT(*) 
FROM github_events 
WHERE type = 'PullRequestEvent' 
AND repo_name = 'pingcap/tidb' 
AND state = 'closed' 
AND event_year = YEAR(CURDATE()) - 1;
++++++++++++++++++++++++++++
[30] Q: are there any repo that has more than 1000 commits in the past year
A: SELECT repo_id, COUNT(*) AS commit_count
FROM github_events
WHERE type = 'PushEvent'
AND event_year = YEAR(CURDATE())
GROUP BY repo_id
HAVING commit_count > 1000;
++++++++++++++++++++++++++++
[31] Q: how many pr closed at christmas last year
A: SELECT COUNT(*) 
FROM github_events 
WHERE type = 'PullRequestEvent' 
AND closed_at LIKE '%2019-12-25%' 
AND state = 'closed';
++++++++++++++++++++++++++++
[32] Q: who created more than 500 pr
A: SELECT actor_login
FROM github_events
WHERE type = 'PullRequestEvent'
GROUP BY actor_login
HAVING COUNT(*) > 500;
++++++++++++++++++++++++++++
[33] Q: show the top 10 contributors
A: SELECT actor_login, COUNT(*) AS contribution_count
FROM github_events
GROUP BY actor_login
ORDER BY contribution_count DESC
LIMIT 10;
++++++++++++++++++++++++++++
[34] Q: get all repo id and name 
A: SELECT repo_id, repo_name 
FROM github_events;
++++++++++++++++++++++++++++
[35] Q: get all repo id and name and sort it
A: SELECT repo_id, repo_name
FROM github_events
ORDER BY repo_id;
++++++++++++++++++++++++++++
[36] Q: get all repo id and name and remove replication
A: SELECT DISTINCT repo_id, repo_name
FROM github_events;
++++++++++++++++++++++++++++
[37] Q: get all repo names include 'golang'
A: SELECT repo_name 
FROM github_events 
WHERE repo_name LIKE '%golang%';
++++++++++++++++++++++++++++
[38] Q: find who commits in at least 2 repo
A: SELECT actor_login
FROM github_events
GROUP BY actor_login
HAVING COUNT(DISTINCT repo_name) >= 2;
++++++++++++++++++++++++++++
[39] Q: find who commits in at least 2 repo in the same day
A: SELECT actor_login, COUNT(DISTINCT repo_name) AS repo_count, event_day
FROM github_events
WHERE type = 'PushEvent'
GROUP BY actor_login, event_day
HAVING repo_count >= 2;
++++++++++++++++++++++++++++
[40] Q: how many commiter from US
A: SELECT COUNT(*) 
FROM github_events 
WHERE actor_location LIKE '%US%';
++++++++++++++++++++++++++++
[41] Q: get all repos that have active commits from 2014 to 2022
A: SELECT repo_id, repo_name
FROM github_events
WHERE type = 'PushEvent'
AND event_year BETWEEN 2014 AND 2022;
++++++++++++++++++++++++++++
[42] 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;
++++++++++++++++++++++++++++
[43] Q: get all type
A: SELECT type FROM github_events;
++++++++++++++++++++++++++++
[44] 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;
++++++++++++++++++++++++++++
[45] Q: list 10 commiter that have more than 500 prs , and order them by id
A: SELECT actor_login, COUNT(*) AS pr_count
FROM github_events
WHERE type = 'PullRequestEvent'
GROUP BY actor_login
HAVING pr_count > 500
ORDER BY id
LIMIT 10;
++++++++++++++++++++++++++++
[46] Q: list all org
A: SELECT DISTINCT org_login 
FROM github_events;
++++++++++++++++++++++++++++
[47] Q: list all org and sort them by the frequency of using python
A: SELECT org_login, COUNT(language) AS frequency
FROM github_events
WHERE language = 'Python'
GROUP BY org_login
ORDER BY frequency DESC;
++++++++++++++++++++++++++++
[48] Q: create table name test in database gharchive_dev, including one filed num 
A: CREATE TABLE gharchive_dev.test (num INT);
++++++++++++++++++++++++++++
[49] Q: how many repos are written in Python? please insert the result to the previous table
A: INSERT INTO github_events (repo_name, language)
SELECT repo_name, language
FROM github_events
WHERE language = 'Python';
++++++++++++++++++++++++++++
[50] Q: please drop table test 
A: DROP TABLE test;
++++++++++++++++++++++++++++
[51] Q: get all collaborator that has more prs than owner
A: SELECT actor_login, COUNT(*) AS pr_count
FROM github_events
WHERE type = 'PullRequestEvent'
AND author_association != 'OWNER'
GROUP BY actor_login
HAVING COUNT(*) > (SELECT COUNT(*)
                    FROM github_events
                    WHERE type = 'PullRequestEvent'
                    AND author_association = 'OWNER');
++++++++++++++++++++++++++++
[52] Q: who contribute to tiDB and kubernetes as the same time
A: SELECT actor_login
FROM github_events
WHERE (repo_name = 'tiDB' OR repo_name = 'kubernetes')
GROUP BY actor_login
HAVING COUNT(*) = 2;
++++++++++++++++++++++++++++
[53] Q: who contribute to tiDB and kubernetes as the same time, please use join not or
A: SELECT actor_login
FROM github_events
WHERE (repo_name = 'tiDB' OR repo_name = 'kubernetes')
GROUP BY actor_login
HAVING COUNT(*) = 2;
++++++++++++++++++++++++++++
[54] Q: get current time in utc+8
A: SELECT CONVERT_TZ(NOW(), 'UTC', '+8:00');;
++++++++++++++++++++++++++++
[55] Q: list the pr amount of each month of 2020 , and sort it.
A: SELECT event_month, COUNT(*) AS pr_amount
FROM github_events
WHERE event_year = 2020
AND type = 'PullRequestEvent'
GROUP BY event_month
ORDER BY pr_amount DESC;
++++++++++++++++++++++++++++
[56] Q: who create most prs in weekend, list 10 of them
A: SELECT actor_login, COUNT(*) AS pr_count
FROM github_events
WHERE type = 'PullRequestEvent'
AND (DAYOFWEEK(created_at) = 6 OR DAYOFWEEK(created_at) = 7)
GROUP BY actor_login
ORDER BY pr_count DESC
LIMIT 10;
++++++++++++++++++++++++++++
[57] Q: Top languages in the open source world over the past four years
A: SELECT language, COUNT(*) AS language_count
FROM github_events
WHERE event_year >= 2016
GROUP BY language
ORDER BY language_count DESC;
++++++++++++++++++++++++++++
[58] Q: Geographic distribution of developer behavior
A: SELECT actor_location, COUNT(*) AS count
FROM github_events
GROUP BY actor_location;
++++++++++++++++++++++++++++
[59] Q: Ranking of back-end programming languages
A: SELECT language, COUNT(*) AS language_count
FROM github_events
WHERE type IN ('PushEvent', 'PullRequestEvent', 'IssueCommentEvent', 'IssuesEvent', 'CreateEvent', 'ForkEvent', 'PullRequestReviewCommentEvent', 'PullRequestReviewEvent', 'ReleaseEvent', 'WatchEvent')
AND language IN ('Java', 'Python', 'C
++++++++++++++++++++++++++++
[60] 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;
++++++++++++++++++++++++++++
[61] Q: which actor only had actions in 2014
A: SELECT actor_login 
FROM github_events 
WHERE event_year = 2014;
++++++++++++++++++++++++++++
[62] Q: the company who has most number of developers
A: SELECT org_login, COUNT(*) AS num_developers
FROM github_events
GROUP BY org_login
ORDER BY num_developers DESC
LIMIT 1;
++++++++++++++++++++++++++++
[63] Q: the repo which earned most stars in 2021
A: SELECT repo_name, SUM(stars) AS total_stars
FROM github_events
WHERE event_year = 2021
AND type = 'WatchEvent'
GROUP BY repo_name
ORDER BY total_stars DESC;
++++++++++++++++++++++++++++
[64] Q: the most numebr of push per day for a developer
A: SELECT actor_login, COUNT(*) AS push_count, event_day
FROM github_events
WHERE type = 'PushEvent'
GROUP BY actor_login, event_day
ORDER BY push_count DESC;
++++++++++++++++++++++++++++
[65] Q: the most active developer in repo pingcap/tidb
A: SELECT actor_login, COUNT(*) AS total_events
FROM github_events
WHERE repo_name = 'pingcap/tidb'
GROUP BY actor_login
ORDER BY total_events DESC
LIMIT 1;
++++++++++++++++++++++++++++
[66] Q: list 5 pr has most comments
A: SELECT pr_or_issue_id, comments
FROM github_events
WHERE type = 'PullRequestEvent'
ORDER BY comments DESC
LIMIT 5;
++++++++++++++++++++++++++++
[67] Q: Distribution of the number of events in the first and second half of each month
A: SELECT 
    event_month, 
    SUM(CASE WHEN event_day <= 15 THEN 1 ELSE 0 END) AS first_half, 
    SUM(CASE WHEN event_day > 15 THEN 1 ELSE 0 END) AS second_half 
FROM github_events 
GROUP BY event_month;
++++++++++++++++++++++++++++
[68] Q: how many actor only submit prs in golang
A: SELECT COUNT(*)
FROM github_events
WHERE type = 'PullRequestEvent'
AND language = 'golang'
AND actor_id IN (SELECT actor_id
                 FROM github_events
                 WHERE type = 'PullRequestEvent'
                 GROUP BY actor_id
                 HAVING COUNT(*) = 1);
++++++++++++++++++++++++++++
[69] Q: how many front-end developers in org pingcap
A: SELECT COUNT(*) 
FROM github_events 
WHERE org_login = 'pingcap' 
AND language = 'front-end' 
AND author_association = 'Developer';
++++++++++++++++++++++++++++
[70] Q: how many prs in pingcap/tidb have been reverted
A: SELECT COUNT(*) 
FROM github_events 
WHERE type = 'PullRequestEvent' 
AND repo_name = 'pingcap/tidb' 
AND action = 'reverted';