Monthly Library Issues by Genre — SQL Dates Problem
The branch librarian's annual return to the city council shows, for every month of 2024 in which at least one book was issued, how many issues fell in each…
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #88
Problem statement
The branch librarian's annual return to the city council shows, for every month of 2024 in which at least one book was issued, how many issues fell in each genre.
Return issue_month in the form 'YYYY-MM', then the columns fiction, non_fiction, children (issues of books in each genre) and total_issues, ordered by issue_month. Issues outside 2024 are not counted.
Tables
Table: Book
| Column | Type |
|---|---|
| book_id | int |
| title | varchar |
| genre | enum(fiction, non_fiction, children) |
Primary key: book_id.
One row per title.
Table: Issue
| Column | Type |
|---|---|
| issue_id | int |
| book_id | int |
| member_id | int |
| issued_on | date |
Primary key: issue_id.
One row each time a book is lent out. Every book_id is in Book.
Examples
Example 1
Book
| book_id | title | genre |
|---|---|---|
| 1 | Malgudi Days | fiction |
| 2 | India After Gandhi | non_fiction |
| 3 | Panchatantra Tales | children |
| 4 | The Guide | fiction |
Issue
| issue_id | book_id | member_id | issued_on |
|---|---|---|---|
| 1 | 1 | 31 | 2023-12-30 |
| 2 | 1 | 32 | 2024-01-05 |
| 3 | 3 | 33 | 2024-01-12 |
| 4 | 4 | 34 | 2024-01-31 |
| 5 | 2 | 31 | 2024-03-01 |
| 6 | 3 | 35 | 2024-03-18 |
| 7 | 2 | 36 | 2024-12-31 |
| 8 | 4 | 37 | 2025-01-02 |
Output
| issue_month | fiction | non_fiction | children | total_issues |
|---|---|---|---|---|
| 2024-01 | 2 | 0 | 1 | 3 |
| 2024-03 | 0 | 1 | 1 | 2 |
| 2024-12 | 0 | 1 | 0 | 1 |
How to solve Monthly Library Issues by Genre
Turning categories into columns is conditional aggregation (a pivot without a PIVOT keyword). Group the 2024 issues by month, and in each group compute one sum per genre where the CASE contributes 1 only for that genre's rows. The three genre columns always add up to total_issues, since every book has exactly one genre.
The month bucket is DATE_FORMAT(issued_on, '%Y-%m'), which also gives the requested 'YYYY-MM' text and sorts chronologically as a string. Months with no issue simply produce no group; the statement only asks for months with at least one issue. Issues on 30 December 2023 and 2 January 2025 in the example sit just outside the year and are dropped by the filter, while 31 December 2024 is inside.
The alternative uses COUNT(IF(…, 1, NULL)) — COUNT ignores NULLs — and LEFT(issued_on, 7) for the month. Both are one join and one grouping pass.
Reference solution (MySQL)
SELECT DATE_FORMAT(i.issued_on, '%Y-%m') AS issue_month,
SUM(CASE WHEN b.genre = 'fiction' THEN 1 ELSE 0 END) AS fiction,
SUM(CASE WHEN b.genre = 'non_fiction' THEN 1 ELSE 0 END) AS non_fiction,
SUM(CASE WHEN b.genre = 'children' THEN 1 ELSE 0 END) AS children,
COUNT(*) AS total_issues
FROM Issue i
JOIN Book b ON b.book_id = i.book_id
WHERE i.issued_on BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY DATE_FORMAT(i.issued_on, '%Y-%m')
ORDER BY issue_monthAnother way
SELECT LEFT(i.issued_on, 7) AS issue_month,
COUNT(IF(b.genre = 'fiction', 1, NULL)) AS fiction,
COUNT(IF(b.genre = 'non_fiction', 1, NULL)) AS non_fiction,
COUNT(IF(b.genre = 'children', 1, NULL)) AS children,
COUNT(*) AS total_issues
FROM Issue i JOIN Book b ON b.book_id = i.book_id
WHERE YEAR(i.issued_on) = 2024
GROUP BY LEFT(i.issued_on, 7)
ORDER BY issue_month← Books With the Longest Reservation Queue · Grievance Tickets per Ward From Reference Codes →