Monthly Library Issues by Genre

Medium
88.

Monthly Library Issues by Genre

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

ColumnTypeValues
book_idint
titlevarchar
genreenumfiction, non_fiction, children

Primary key: book_id.

One row per title.

Table: Issue

ColumnType
issue_idint
book_idint
member_idint
issued_ondate

Primary key: issue_id.

One row each time a book is lent out. Every book_id is in Book.

Example

The rows must come back in the order shown.

Book

book_idtitlegenre
1Malgudi Daysfiction
2India After Gandhinon_fiction
3Panchatantra Taleschildren
4The Guidefiction

Issue

issue_idbook_idmember_idissued_on
11312023-12-30
21322024-01-05
33332024-01-12
44342024-01-31
52312024-03-01
63352024-03-18
72362024-12-31
84372025-01-02

Output

issue_monthfictionnon_fictionchildrentotal_issues
2024-012013
2024-030112
2024-120101
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
book_idint
titlevarchar
genreenum(fiction, non_fiction, children)

Primary key: book_id.

One row per title.

Table: Issue

ColumnType
issue_idint
book_idint
member_idint
issued_ondate

Primary key: issue_id.

One row each time a book is lent out. Every book_id is in Book.

Examples

Example 1

Book

book_idtitlegenre
1Malgudi Daysfiction
2India After Gandhinon_fiction
3Panchatantra Taleschildren
4The Guidefiction

Issue

issue_idbook_idmember_idissued_on
11312023-12-30
21322024-01-05
33332024-01-12
44342024-01-31
52312024-03-01
63352024-03-18
72362024-12-31
84372025-01-02

Output

issue_monthfictionnon_fictionchildrentotal_issues
2024-012013
2024-030112
2024-120101

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_month

Another 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 →