Open Issues by RepositoryCompany: Github | Difficulty: easy

GitHub's engineering operations team wants a quick view of unresolved work across active repositories.

For every non-archived repository, count the number of issues whose state is open.

Repositories with no open issues must still appear with a count of zero.

Return the columns in this exact order: repo_id, repo_name, and open_issue_count.

Sort by open_issue_count descending, then repo_id ascending.

Table Schema

Table	Column	Type
`repositories`	`repo_id`	integer
`repositories`	`repo_name`	varchar(80)
`repositories`	`is_archived`	boolean
`issues`	`issue_id`	integer
`issues`	`repo_id`	integer
`issues`	`title`	varchar(120)
`issues`	`state`	varchar(20)

Example Input: `repositories`

repo_id	repo_name	is_archived
1	github-actions-runner	false
2	copilot-cli	false
3	hubot-archive	true

Example Input: `issues`

issue_id	repo_id	title	state
101	1	Fix login redirect	open
102	1	Add audit logging	open
103	1	Update dependencies	closed
104	2	Improve documentation	closed

Example Output Explanation

github-actions-runner has two open issues. copilot-cli has no open issues, so it appears with a count of zero. hubot-archive is archived and is excluded.

SchemaDROP TABLE IF EXISTS issues; DROP TABLE IF EXISTS repositories; CREATE TABLE repositories ( repo_id INT, repo_name VARCHAR(80), is_archived BOOLEAN ); CREATE TABLE issues ( issue_id INT, repo_id INT, title VARCHAR(120), state VARCHAR(20) ); INSERT INTO repositories VALUES (1, 'github-actions-runner', FALSE), (2, 'copilot-cli', FALSE), (3, 'hubot-archive', TRUE); INSERT INTO issues VALUES (101, 1, 'Fix login redirect', 'open'), (102, 1, 'Add audit logging', 'open'), (103, 1, 'Update dependencies', 'closed'), (104, 2, 'Improve documentation', 'closed');← All SQL Challenges · Github Questions · Learn SQL Free