# MySQL SQL
# Table Schema
Table github_events, columns = [id, type, created_at, repo_id, repo_name, actor_id, actor_login, additions, deletions, action, number, org_login, org_id, state, closed_at, comments, pr_merged_at, pr_merged, pr_changed_files, pr_review_comments, pr_or_issue_id, push_size, push_distinct_size, creator_user_login, creator_user_id, pr_or_issue_created_at]
- Column type, enums = ['PullRequestEvent', 'PushEvent', 'IssueCommentEvent', 'IssuesEvent', 'PullRequestReviewCommentEvent', 'WatchEvent', 'CreateEvent', 'DeleteEvent', 'ForkEvent', 'ReleaseEvent']
- Column action:
* type in [PullRequestReviewCommentEvent, IssueCommentEvent, ReviewEvent]: created
* type in [PullRequestEvent, IssuesEvent]: opened, closed, reopened
- Column number, number is issue number
- Column created_at, closed_at, pr_merged_at, pr_or_issue_created_at DEFAULT '1970-01-01 00:00:00'
- Column additions, deletions: only existed in PullRequestEvent
- Column actor_id, actor_login: who trigger the event

Table github_repos, columns = [repo_id, repo_name, owner_id, owner_login, owner_is_org, description, primary_language, license, stars, forks, parent_repo_id, is_archived, is_deleted, latest_released_at, pushed_at, created_at, updated_at]
- Column repo_id: primary key
- Column primary_language: refers to the primary programming language by the repo, not the framework or software, invalid = ['']

Table github_users, columns = [id, login, type, name, organization, country_code, followers, followings, created_at, updated_at]
- Column id: primary key
- Column type, enums = ['USR', 'ORG']
- Column country_code, invalid = ['', 'N/A', 'UND']
- Column organization: include company information, invalid = ['',  null]

Table trending_repos, contains the most popular repositories recently, columns = [repo_name, created_at]
Table github_repo_topics, columns = [repo_id, topic]

# Table Relations
github_events.repo_id = github_repos.repo_id
github_events.actor_id = github_users.id
github_events.actor_login = github_users.login
github_events.creator_user_id = github_users.id
github_repos.owner_id = github_users.id
github_repos.repo_id = github_repo_topics.repo_id
trending_repos.repo_name = github_repos.repo_name

# Common Sense

- What is X: If X has a repository (eg. tidb, tikv, react, vue .etc.), what is X means select the description field of that repo_name from github_repos
- project: in github, "project" is equal to "repository"
- machine learning: machine learning is a new topic, it's value used in topic field should be \"machine-learning\"
- TiDB: TiDB is a database product developed by PingCAP. Its repository is pingcap/tidb.

# Examples

## Example 1

question: repositories @634750802 and @Mini256 both interested in
sql: SELECT `gr`.`repo_name`, `gr`.`owner_login`, `gr`.`description`, `gr`.`primary_language`, `gr`.`license`, `gr`.`size`, `gr`.`stars`, `gr`.`forks`, `gr`.`parent_repo_id`, `gr`.`is_fork`, `gr`.`is_archived`, `gr`.`is_deleted`, `gr`.`latest_released_at`, `gr`.`pushed_at`, `gr`.`created_at`, `gr`.`updated_at` FROM `github_repos` AS `gr` WHERE `gr`.`owner_login` IN ('634750802', 'Mini256') LIMIT 200;

## Exampl 2

question: A topic about the Corona virus
sql: SELECT gr.repo_name AS repo_name, COUNT(*) AS stars FROM github_events ge JOIN github_repos gr ON ge.repo_id = gr.repo_id WHERE ge.type = 'WatchEvent' AND ge.action = 'started' AND LOWER(gr.description) LIKE LOWER('%corona virus%') GROUP BY gr.repo_name ORDER BY stars DESC LIMIT 20;

## Example 3:

question: Want to create an application in Ballerina
sql: SELECT `gr`.`repo_name` AS `repo_name`, `gr`.`stars` AS `stars` FROM `github_repos` AS `gr` WHERE LOWER(`gr`.`description`) LIKE LOWER('%ballerina%') AND `gr`.`pushed_at` > DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY `stars` DESC LIMIT 20;

## Example 4:

question: best artificial intelligence  projects in 2022
sql: SELECT `gr`.`repo_name`, `gr`.`stars` FROM `github_repos` AS `gr` WHERE `gr`.`description` LIKE '%artificial intelligence%' AND YEAR(`gr`.`created_at`) = 2022 ORDER BY `gr`.`stars` DESC LIMIT 20

## Example 5: 

question: top 50 react projects by stars that started 2022, use topic to filter
sql: SELECT `gr`.`repo_name`, `gr`.`stars` FROM `github_repos` AS `gr` INNER JOIN `github_repo_topics` AS `grt` ON `gr`.`repo_id` = `grt`.`repo_id` WHERE `grt`.`topic` = 'react' AND YEAR(`gr`.`created_at`) = 2022 ORDER BY `gr`.`stars` DESC LIMIT 50;

## Example 6:

question: The most used programming language 2022
sql: SELECT `gr`.`primary_language`, COUNT(*) AS `repo_id` FROM `github_repos` AS `gr` WHERE YEAR(`gr`.`created_at`) = 2022 AND `gr`.`primary_language` NOT IN ('', 'N/A', 'UND') GROUP BY `gr`.`primary_language` ORDER BY `repo_id` DESC LIMIT 20;

## Example 7:
question: The open source projects most similar to @pingcap/docs
sql: SELECT `gr`.`repo_id`, `gr`.`repo_name`, `gr`.`description`, `gr`.`primary_language`, `gr`.`license`, `gr`.`size`, `gr`.`stars`, `gr`.`forks`, `gr`.`parent_repo_id`, `gr`.`is_fork`, `gr`.`is_archived`, `gr`.`is_deleted`, `gr`.`latest_released_at`, `gr`.`pushed_at`, `gr`.`created_at`, `gr`.`updated_at`, `gt`.`topic` FROM `github_repos` AS `gr` INNER JOIN `github_repo_topics` AS `gt` ON `gr`.`repo_id` = `gt`.`repo_id` WHERE `gr`.`repo_name` != 'pingcap/docs' AND `gr`.`primary_language` = (SELECT `primary_language` FROM `github_repos` WHERE `repo_name` = 'pingcap/docs') AND `gt`.`topic` IN (SELECT `topic` FROM `github_repo_topics` WHERE `repo_id` = (SELECT `repo_id` FROM `github_repos` WHERE `repo_name` = 'pingcap/docs')) ORDER BY `gr`.`stars` DESC LIMIT 10

## Example 8:
question: The monthly change of average time of torvalds/linux pull requests from open to closed from 2012 to 2022
sql: SELECT DATE_FORMAT(`ge`.`created_at`, '%Y-%m-01') AS `t_month`, COUNT(*) AS `opened_prs`, AVG(TIMESTAMPDIFF(SECOND, `ge`.`pr_or_issue_created_at`, `ge`.`closed_at`)) AS `avg_time` FROM `github_events` AS `ge` WHERE `ge`.`type` = 'PullRequestEvent' AND `ge`.`action` = 'closed' AND `ge`.`repo_id` = (SELECT `repo_id` FROM `github_repos` WHERE `repo_name` = 'torvalds/linux' LIMIT 1) AND YEAR(`ge`.`created_at`) BETWEEN 2012 AND 2022 GROUP BY `t_month` ORDER BY `t_month` ASC LIMIT 200;

## Example 9:

question: Star history of @carbon-language/carbon-lang vs something alike
sql: SELECT `t_month`, `carbon_stars`, `other_stars`, SUM(`carbon_stars`) OVER (ORDER BY `t_month` ASC) AS `carbon_cumulative_stars`, SUM(`other_stars`) OVER (ORDER BY `t_month` ASC) AS `other_cumulative_stars` FROM (SELECT DATE_FORMAT(`created_at`, '%Y-%m-01') AS `t_month`, SUM(CASE WHEN `ge`.`repo_name` = 'carbon-language/carbon-lang' THEN 1 ELSE 0 END) AS `carbon_stars`, SUM(CASE WHEN `ge`.`repo_name` != 'carbon-language/carbon-lang' THEN 1 ELSE 0 END) AS `other_stars` FROM `github_events` AS `ge` WHERE `ge`.`type` = 'WatchEvent' AND `ge`.`created_at` != '1970-01-01 00:00:00' GROUP BY `t_month`) AS `star_counts` ORDER BY `t_month` ASC LIMIT 200;

## Example 10:

question: how many languages is used in github
sql: SELECT gr.primary_language AS language, COUNT(*) AS repos_count FROM github_repos gr WHERE gr.primary_language != '' GROUP BY gr.primary_language ORDER BY repos_count DESC;

## Example 11:

question: what is the most popular language
sql: SELECT `gr`.`primary_language`, COUNT(*) AS `repo_id` FROM `github_repos` AS `gr` WHERE `gr`.`primary_language` NOT IN ('', 'N/A', 'UND') GROUP BY `gr`.`primary_language` ORDER BY `repo_id` DESC LIMIT 20;

## Example 12:

question: How diverse is django's community (by coders‘ company distribution)
sql: SELECT `gu`.`organization` AS `company`, COUNT(*) AS `coders` FROM `github_events` AS `ge` INNER JOIN `github_users` AS `gu` ON `ge`.`actor_id` = `gu`.`id` WHERE `ge`.`type` = 'PullRequestEvent' AND `ge`.`repo_id` = (SELECT `repo_id` FROM `github_repos` WHERE `repo_name` = 'django/django' LIMIT 1) AND `gu`.`organization` != '' AND `gu`.`organization` IS NOT NULL GROUP BY `gu`.`organization` ORDER BY `coders` DESC LIMIT 200;

## Example 13:

question: The most popular blockchain repos on GitHub
sql: SELECT `gr`.`repo_name`, COUNT(*) AS `stars` FROM `github_events` AS `ge` INNER JOIN `github_repos` AS `gr` ON `ge`.`repo_id` = `gr`.`repo_id` WHERE `ge`.`type` = 'WatchEvent' AND `ge`.`action` = 'started' AND LOWER(`gr`.`description`) LIKE LOWER('%blockchain%') AND `ge`.`created_at` > DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY `gr`.`repo_name` ORDER BY `stars` DESC LIMIT 20

# ChartOptions
type Column = string; // must be the column name in the SQL result!!!
PieChart {label: Column; value: Column;}
LineChart {x: Column; y: Column | Column[];}
BarChart {x: Column; y: Column | Column[];}
MapChart {country_code: Column; value: Column;}
NumberCard {value: Column;}
RepoCard {repo_name: Column;}
PersonalCard {user_login: Column;}
Table {columns: Column[];}

# ChartConfig
type ChartName = 'PieChart' | 'LineChart' | 'BarChart' | 'MapChart' | 'NumberCard' | 'RepoCard' | 'PersonalCard' | 'Table';
interface ChartConfig {chartName: ChartName; title: string; options: ChartOptions;}

use the answer.json template:
{
    "RQ": "The question after revised, marked as RQ, RQ should be a clear and concise statement of the question",
    "keywords": ["Output every key information('who', 'when', 'what', 'time range') in RQ"],
    "links": ["List the GitHub links of the repos mentioned in RQ"],
    "subQuestions": ["List at most 2 important sub-questions started with 'No{n}. ' (`{n}` is a placeholder) in order in array to answer the RQ"],
    "notClear": "What is not clear or needs to narrow down",
    "assumption": "What's your assumptions about RQ and sub-questions",
    "deps": "Dependency graph of each sub-question, Question Number only",
    "CQ": "Integrate (RQ,assumptions) into a clarified question,then diff_words_from(RQ) and mark with (<b>)",
    "sqlCanAnswer": "boolean, whether the RQ about GitHub",
    "sql": "use the best practice to write A SINGLE SQL (You can use Common Table Expressions (CTEs) or subqueries  to ensure that the final SQL statement is an SQL statement, not a combination of multiple SQL statements) to answer the question (RQ)",
    "chart": "must a ChartConfig object"
}

Begin:
Let's think step by step, generate the content for an answer.json file to answer the question "In GitHub, {{question}}" (Do not overthink):
---
answer.json // must only output a pretty JSON!!!
---