Second-Most Borrowed Book by Country

An international library network records every book loan and the branch where it occurred. The catalog team wants to identify books at the second-highest distinct borrowing level within each country.

Table: books

+--------------+--------------+
| Column Name  | Type         |
+--------------+--------------+
| book_id      | integer      |
| title        | varchar(255) |
| genre        | varchar(255) |
| published_on | date         |
+--------------+--------------+
+--------------+--------------+
| Column Name  | Type         |
+--------------+--------------+
| book_id      | integer      |
| title        | varchar(255) |
| genre        | varchar(255) |
| published_on | date         |
+--------------+--------------+

book_id is the primary key. Each row contains catalog information for one book.

Table: library_loans

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| loan_id     | integer |
| member_id   | integer |
| book_id     | integer |
| loan_date   | date    |
| branch_id   | integer |
+-------------+---------+
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| loan_id     | integer |
| member_id   | integer |
| book_id     | integer |
| loan_date   | date    |
| branch_id   | integer |
+-------------+---------+

loan_id is the primary key. Each row represents one completed book loan.

Table: library_branches

+-------------+--------------+
| Column Name | Type         |
+-------------+--------------+
| branch_id   | integer      |
| branch_name | varchar(255) |
| country     | varchar(255) |
+-------------+--------------+
+-------------+--------------+
| Column Name | Type         |
+-------------+--------------+
| branch_id   | integer      |
| branch_name | varchar(255) |
| country     | varchar(255) |
+-------------+--------------+

branch_id is the primary key. Each row identifies a library branch and its country.

For each country, count the loans for every book across all branches in that country. Rank books by their loan count using distinct ranks: books with equal counts share the same rank, and the next different count receives the next rank.

Return every book whose loan count has rank 2 within its country. This is the second-highest distinct loan count, so all ties at that level must be included.

Books with no loans are excluded. If a country has only one distinct positive loan count—for example, all borrowed books are tied for the highest count—return no row for that country.

Return country, book_id, and loan_count. Order the results by country ascending, then book_id ascending.

Example

Input:

books table:
+---------+--------------+---------+--------------+
| book_id | title        | genre   | published_on |
+---------+--------------+---------+--------------+
| 1       | Northern Sky | Fiction | 2024-01-01   |
| 2       | River Map    | Travel  | 2024-01-01   |
| 3       | Small Worlds | Science | 2024-01-01   |
+---------+--------------+---------+--------------+
books table:
+---------+--------------+---------+--------------+
| book_id | title        | genre   | published_on |
+---------+--------------+---------+--------------+
| 1       | Northern Sky | Fiction | 2024-01-01   |
| 2       | River Map    | Travel  | 2024-01-01   |
| 3       | Small Worlds | Science | 2024-01-01   |
+---------+--------------+---------+--------------+

library_loans table:
+---------+-----------+---------+------------+-----------+
| loan_id | member_id | book_id | loan_date  | branch_id |
+---------+-----------+---------+------------+-----------+
| 1       | 10        | 1       | 2026-01-01 | 100       |
| 2       | 11        | 1       | 2026-01-02 | 100       |
| 3       | 12        | 2       | 2026-01-03 | 100       |
| 4       | 13        | 3       | 2026-01-04 | 100       |
+---------+-----------+---------+------------+-----------+
library_loans table:
+---------+-----------+---------+------------+-----------+
| loan_id | member_id | book_id | loan_date  | branch_id |
+---------+-----------+---------+------------+-----------+
| 1       | 10        | 1       | 2026-01-01 | 100       |
| 2       | 11        | 1       | 2026-01-02 | 100       |
| 3       | 12        | 2       | 2026-01-03 | 100       |
| 4       | 13        | 3       | 2026-01-04 | 100       |
+---------+-----------+---------+------------+-----------+

library_branches table:
+-----------+-------------+---------+
| branch_id | branch_name | country |
+-----------+-------------+---------+
| 100       | Toronto     | CA      |
+-----------+-------------+---------+
library_branches table:
+-----------+-------------+---------+
| branch_id | branch_name | country |
+-----------+-------------+---------+
| 100       | Toronto     | CA      |
+-----------+-------------+---------+

Output:

+---------+---------+------------+
| country | book_id | loan_count |
+---------+---------+------------+
| CA      | 2       | 1          |
| CA      | 3       | 1          |
+---------+---------+------------+
+---------+---------+------------+
| country | book_id | loan_count |
+---------+---------+------------+
| CA      | 2       | 1          |
| CA      | 3       | 1          |
+---------+---------+------------+

Explanation:

Book 1 has two Canadian loans. Books 2 and 3 each have one, so both share the second-highest distinct loan count.