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` descending. ### 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 | api-service | false | | 2 | web-client | false | | 3 | old-demo | 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 `api-service` has two open issues. `web-client` has no open issues, so it appears with a count of zero. `old-demo` is archived and is excluded.
DROP 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,'api-service',FALSE), (2,'web-client',FALSE), (3,'old-demo',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');