Books and Authors

Problem

You have two tables. Books holds one row per book with a genre; Authors maps authors to books (an author may appear once per book they wrote).

Books                         Authors
+----------+---------+        +-----------+---------+---------------------+
| book_id  | genre   |        | author_id | book_id | author_name         |
+----------+---------+        +-----------+---------+---------------------+
| 1        | Fiction |        | 101       | 1       | Leo Tolstoy         |
| 2        | Fiction |        | 101       | 2       | Leo Tolstoy         |
| 3        | Fiction |        | 102       | 3       | Fyodor Dostoevsky   |
| 4        | Fiction |        | 103       | 4       | F. Scott Fitzgerald |
| 5        | Romance |        | 104       | 5       | Jane Austen         |
+----------+---------+        +-----------+---------+---------------------+

Task: for each author, count how many 'Fiction' books they wrote, returning author_name and fiction_books_count. Authors with zero Fiction books are excluded. Order by fiction_books_count descending; on a tie, order by author_name ascending.

Solution

The shape is the classic filter → join → group → order pipeline. Join authors to their books, keep only Fiction rows, count per author, then apply the two-level sort.

SELECT A.author_name,
       COUNT(B.book_id) AS fiction_books_count
FROM   Authors A
       JOIN Books B ON A.book_id = B.book_id
WHERE  B.genre = 'Fiction'
GROUP  BY A.author_name
ORDER  BY fiction_books_count DESC, A.author_name ASC;

Because the join is an inner join, an author with no matching Fiction book contributes no rows and is dropped automatically — no extra filter needed for the "exclude zero" rule. The WHERE runs before GROUP BY, so the COUNT only ever sees Fiction rows.

Result:

+---------------------+---------------------+
| author_name         | fiction_books_count |
+---------------------+---------------------+
| Leo Tolstoy         | 2                   |
| F. Scott Fitzgerald | 1                   |
| Fyodor Dostoevsky   | 1                   |
+---------------------+---------------------+

The tie-break, demonstrated correctly

Tolstoy is alone at count 2, so he sits on top. The interesting part is the tie at count 1 between Fyodor Dostoevsky and F. Scott Fitzgerald. The tie-break is author_name ASC, which compares the full strings character by character by code point, left to right, stopping at the first position where they differ.

Compare the two strings position by position:

The very first difference is at position 1: '.' (code point 0x2E = 46) versus 'y' (code point 0x79 = 121). Since 46 < 121, F. Scott Fitzgerald sorts first, and the comparison never reaches any later characters. There is no 'i' involved: char[1] of F. Scott Fitzgerald is the period, not an 'i'. It would be wrong to reason about "Fi" vs "Fy" here — that compares characters that are not at the same position in these two strings. What actually breaks the tie is the period preceding the letters.

Pitfall: the filter and the sort both depend on collation

Two collation-sensitive things hide in this query: the = 'Fiction' filter and the author_name tie-break. How each behaves is engine- and collation-specific, so be precise rather than assuming a universal rule:

• PostgreSQL string comparison with the default collation is case-sensitive and space-sensitive. 'Fiction' will not match 'fiction' or 'Fiction ' (trailing space), and the sort orders by raw byte/code-point order much like the table above.
• MySQL defaults to a case-insensitive collation (e.g. utf8mb4_0900_ai_ci in 8.0, historically latin1_swedish_ci). There 'fiction' = 'Fiction' is true, and accent-insensitive variants treat accented letters as equal too. Trailing-space handling is its own axis: comparisons with = have traditionally ignored trailing spaces (PAD SPACE), and the exact behavior varies by collation and version.

So the same WHERE B.genre = 'Fiction' can match different rows on different engines, and the tie-break can order names differently (case-folding can change which of two near-identical names comes first). If you need deterministic, portable behavior, normalize the data on insert or pin the collation explicitly — e.g. WHERE B.genre = 'Fiction' COLLATE utf8mb4_bin in MySQL, or an explicit ORDER BY ... COLLATE "C" in PostgreSQL — rather than relying on whatever the server default happens to be.

Source

Problem statement and example adapted from the LeetCode-style "Books and Authors" SQL exercise. Code-point values cited from the ASCII table ('.' = 0x2E = 46, 'F' = 0x46 = 70, 'y' = 0x79 = 121). Collation behavior per the MySQL 8.0 collation reference and the PostgreSQL collation documentation.