++++++++++++++++++++++++++++
True: SQL statements are identical to human understanding and can be run without modification to obtain results
False: sql statements are inconsistent with the human understanding of natural language or contain syntax errors or wrong values.
++++++++++++++++++++++++++++
[0] Q: what's the best repo
A: SELECT repo_name, SUM(additions) AS total_additions, SUM(deletions) AS total_deletions
FROM github_events
GROUP BY repo_name
ORDER BY total_additions DESC, total_deletions ASC;
False
Reason: 'Best' is ambiguous. It can be interpreted as the most popular repo, or the repo with the most additions, or the repo with the most deletions.
++++++++++++++++++++++++++++
[1] Q: show me the SQL to get current DAY of this MONTH
A: SELECT DAY(CURDATE()) AS 'Current Day of this Month';
True
Reason:
++++++++++++++++++++++++++++
[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 repo_name = 'pingcap/tidb' 
AND actor_login = '<your_username>' 
AND created_at > DATE_SUB(NOW(), INTERVAL 3 MONTH);
True
Reason: 
++++++++++++++++++++++++++++
[3] 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;
False
Reason: replace 'Kubernetes' with 'kubernetes/kubernetes'
++++++++++++++++++++++++++++
[4] Q: How many repo were written by js
A: SELECT COUNT(*)
FROM github_events
WHERE language = 'js'
AND type = 'PushEvent';
False
Reason: this query will return the number of events that have js as the language, not the number of repos that are written by js.
++++++++++++++++++++++++++++
[5] Q: How many contributors in @pingcap/tidb
A: SELECT COUNT(DISTINCT actor_login) 
FROM github_events 
WHERE repo_name = '@pingcap/tidb';
True
Reason: 
++++++++++++++++++++++++++++
[6] 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;
True
Reason:
++++++++++++++++++++++++++++
[7] Q: how many repos are written in Python?
A: SELECT COUNT(*) 
FROM github_events 
WHERE language = 'Python';
False
Reason: this query will return the number of events that have Python as the language, not the number of repos that are written in Python.
++++++++++++++++++++++++++++
[8] 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;
False
Reason: 'popular' is ambiguous. It can be interpreted as the repo with the most events, or the repo with the most commits, or the repo with the most contributors.
++++++++++++++++++++++++++++
[9] Q: find how many developer come from US
A: SELECT COUNT(*)
FROM github_events
WHERE actor_location LIKE '%US%';
True
Reason: actor_location is always a blank column.
++++++++++++++++++++++++++++
[10] Q: latest commit TIME
A: SELECT MAX(created_at)
FROM github_events
WHERE type = 'PushEvent';
False
Reason: replace 'PushEvent'.
++++++++++++++++++++++++++++
[11] Q: get the total count of events
A: SELECT COUNT(*) AS total_events
FROM github_events;
True
Reason: 
++++++++++++++++++++++++++++
[12] Q: get the number of rows in event table
A: SELECT COUNT(*) FROM github_events;
True
Reason: 
++++++++++++++++++++++++++++
[13] 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;
True
Reason: 
++++++++++++++++++++++++++++
[14] Q: give me sql to find how many repo were written by python
A: SELECT COUNT(*) 
FROM github_events 
WHERE language = 'Python';
False
Reason: this query will return the number of events that have Python as the language, not the number of repos that are written in Python.
++++++++++++++++++++++++++++
[15] Q: sql function to get current time
A: SELECT CURRENT_TIMESTAMP();
True
Reason:
++++++++++++++++++++++++++++
[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;
False
Reason: we want a repo , not a list of repos
++++++++++++++++++++++++++++
[17] Q: which pr in @pingcap/tidb has the most comments
A: SELECT repo_name, pr_or_issue_id, MAX(comments) 
FROM github_events 
WHERE org_login = 'pingcap' AND repo_name = 'tidb' AND type = 'PullRequestEvent' 
GROUP BY repo_name, pr_or_issue_id;
False
Reason: we want a pr, not a list of prs. Besides, repo_name is not correct.
++++++++++++++++++++++++++++
[18] Q: what day is the day that @pingcap/tidb has the most commits
A: SELECT event_day, COUNT(*) AS commits
FROM github_events
WHERE repo_name = '@pingcap/tidb' AND type = 'PushEvent'
GROUP BY event_day
ORDER BY commits DESC
LIMIT 1;
False
Reason: type 'PushEvent' is not correct.
++++++++++++++++++++++++++++
[19] 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;
True
Reason:
++++++++++++++++++++++++++++
[20] 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;
True
Reason:
++++++++++++++++++++++++++++
[21] 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;
False
Reason: we want a repo, not a list of repos.
++++++++++++++++++++++++++++
[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;
True
Reason:
++++++++++++++++++++++++++++
[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';
True
Reason:
++++++++++++++++++++++++++++
[24] 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;
False
Reason: 'PullRequestReviewCommentEvent' is not correct 
++++++++++++++++++++++++++++
[25] Q: Which repo has the most commits in the past year
A: SELECT repo_name, COUNT(*) AS commits_in_past_year
FROM github_events
WHERE event_year = YEAR(CURDATE())
AND type = 'PushEvent'
GROUP BY repo_name
ORDER BY commits_in_past_year DESC;
False
Reason: 'PushEvent' is not correct
++++++++++++++++++++++++++++
[26] 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;
False
Reason: we want a user, not a list of users.
++++++++++++++++++++++++++++
[27] 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;
False
Reason: event_year should be YEAR(CURDATE())
++++++++++++++++++++++++++++
[28] Q: are there any repo that has more than 1000 commits in the past year
A: 
False
Reason: no sql sentence generated.
++++++++++++++++++++++++++++
[29] 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';
False
Reason: last year is not 2019
++++++++++++++++++++++++++++
[30] Q: who created more than 500 pr
A: SELECT actor_login
FROM github_events
WHERE type = 'PullRequestEvent'
GROUP BY actor_login
HAVING COUNT(*) > 500;
True
Reason:
++++++++++++++++++++++++++++
[31] 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;
True
Reason:
++++++++++++++++++++++++++++
[32] Q: get all repo id and name 
A: SELECT repo_id, repo_name 
FROM github_events;
True
Reason:
++++++++++++++++++++++++++++
[33] Q: get all repo id and name and sort it
A: SELECT repo_id, repo_name
FROM github_events
ORDER BY repo_id;
True
Reason:
++++++++++++++++++++++++++++
[34] Q: get all repo id and name and remove replication
A: SELECT DISTINCT repo_id, repo_name
FROM github_events;
True
Reason:
++++++++++++++++++++++++++++
[35] Q: get all repo names include 'golang'
A: SELECT repo_name 
FROM github_events 
WHERE repo_name LIKE '%golang%';
True
Reason:
++++++++++++++++++++++++++++
[36] 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;
True
Reason:
++++++++++++++++++++++++++++
[37] Q: find who commits in at least 2 repo in the same day
A: SELECT actor_login, COUNT(DISTINCT repo_id) AS repo_count, event_day
FROM github_events
WHERE type = 'PushEvent'
GROUP BY actor_login, event_day
HAVING repo_count >= 2;
False
Reason: 'PushEvent' is not correct
++++++++++++++++++++++++++++
[38] Q: how many commiter from US
A: SELECT COUNT(*)
FROM github_events
WHERE actor_location LIKE '%US%';
True
Reason:
++++++++++++++++++++++++++++
[39] Q: get all repos that have active commits from 2014 to 2022
A: SELECT repo_name 
FROM github_events 
WHERE type = 'PushEvent' 
AND event_year BETWEEN 2014 AND 2022;
False
Reason: PushEvent is not correct
++++++++++++++++++++++++++++
[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;
False
Reason: we want a repo, not a list of repos.
++++++++++++++++++++++++++++
[41] Q: get all type
A: SELECT type FROM github_events;
True
Reason:
++++++++++++++++++++++++++++
[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;
True
Reason:
++++++++++++++++++++++++++++
[43] 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;
False
Reason: can not execute.
++++++++++++++++++++++++++++
[44] Q: list all org
A: SELECT DISTINCT org_login 
FROM github_events;
True
Reason:
++++++++++++++++++++++++++++
[45] 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;
True
Reason:
++++++++++++++++++++++++++++
[46] Q: create table name test in database gharchive_dev, including one filed num 
A: CREATE TABLE gharchive_dev.test (num INT);
True
Reason:
++++++++++++++++++++++++++++
[47] Q: how many repos are written in Python? please insert the result to the previous table
A: No SQL Generated.
False
Reason: No SQL Generated.
++++++++++++++++++++++++++++
[48] Q: please drop table test 
A: DROP TABLE test;
True
Reason:
++++++++++++++++++++++++++++
[49] Q: get all collaborator that has more prs than owner
A: 
False
Reason: No SQL Generated.
++++++++++++++++++++++++++++
[50] 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(*) > 1;
False
Reason: repo name is not correct, and this sql will get 'who contribute to tidb or kubernetes as least once'
++++++++++++++++++++++++++++
[51] 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;
False
Reason: repo name is not correct, and this sql will get 'who contribute to tidb or kubernetes as least twice'
++++++++++++++++++++++++++++
[52] Q: get current time in utc+8
A: SELECT CONVERT_TZ(NOW(), 'UTC', '+8:00');;
True
Reason:
++++++++++++++++++++++++++++
[53] 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;
True
Reason:
++++++++++++++++++++++++++++
[54] 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;
True
Reason:
++++++++++++++++++++++++++++
[55] Q: Top languages in the open source world over the past four years
A: 
False
Reason: No SQL Generated.
++++++++++++++++++++++++++++
[56] Q: Geographic distribution of developer behavior
A: SELECT actor_location, COUNT(*) AS count
FROM github_events
GROUP BY actor_location;
True
Reason:
++++++++++++++++++++++++++++
[57] 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
False
Reason: SQL Generated is not complete.
++++++++++++++++++++++++++++
[58] 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',
    COUNT(*) AS 'Count'
FROM github_events
GROUP BY DAYOFWEEK(created_at);
False
Reason: GROUP BY DAYOFWEEK(created_at) is not correct.
++++++++++++++++++++++++++++
[59] Q: which actor only had actions in 2014
A: SELECT actor_login 
FROM github_events 
WHERE event_year = 2014;
False
Reason: this sql will get 'the actor who had actions in 2014'
++++++++++++++++++++++++++++
[60] 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;
False
Reason: this sql will get 'the company who has most number of events'
++++++++++++++++++++++++++++
[61] 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;
False
Reason: Unknown column 'stars' in 'where clause'
++++++++++++++++++++++++++++
[62] 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;
False
Reason: 'PushEvent' is not correct.
++++++++++++++++++++++++++++
[63] Q: the most active developer in repo pingcap/tidb
A: SELECT actor_login, COUNT(*) AS activity_count
FROM github_events
WHERE repo_name = 'pingcap/tidb'
GROUP BY actor_login
ORDER BY activity_count DESC
LIMIT 1;
True
Reason: