Solution

To identify each book's first year of checkout, along with the total number of checkouts and the price per checkout for that year, we can follow a systematic approach using SQL's aggregation and join capabilities. This ensures accurate retrieval of the desired information by leveraging Common Table Expressions (CTEs) and appropriate filtering.

• Determine the First Year of Checkout for Each Book: Identify the earliest year each book was checked out by finding the minimum year associated with each book_id.
• Retrieve Checkout Details for the First Year: Obtain the number of checkouts and the price per checkout for each book in its first year of checkout.
• Order the Results: Sort the final output by book_id to present the data in an organized manner.

SQL Query

WITH FirstYear AS (
    SELECT book_id, MIN(year) AS first_year
    FROM Checkouts
    GROUP BY book_id
)
SELECT f.book_id, f.first_year, c.checkouts, c.price
FROM FirstYear f
JOIN Checkouts c ON f.book_id = c.book_id AND f.first_year = c.year
ORDER BY book_id;

Step-by-Step Approach

Step 1: Determine the First Year of Checkout for Each Book

Identify the earliest year each book was checked out by finding the minimum year associated with each book_id.

SQL Query:

WITH FirstYear AS (
    SELECT book_id, MIN(year) AS first_year
    FROM Checkouts
    GROUP BY book_id
)

Explanation:

• WITH FirstYear AS (...):
  • Creates a Common Table Expression (CTE) named FirstYear that stores the earliest checkout year for each book.
• SELECT book_id, MIN(year) AS first_year:
  • Selects each book_id and calculates the minimum year it was checked out.
• FROM Checkouts:
  • Uses the Checkouts table as the data source.
• GROUP BY book_id:
  • Groups the records by book_id to perform the aggregation, ensuring that the minimum year is calculated for each individual book.

Output After Step 1:

+---------+------------+
| book_id | first_year |
+---------+------------+
| 100     | 2008       |
| 200     | 2011       |
+---------+------------+

Step 2: Retrieve Checkout Details for the First Year

For each book, obtain the number of checkouts and the price per checkout corresponding to its first year of checkout.

SQL Query:

SELECT f.book_id, f.first_year, c.checkouts, c.price
FROM FirstYear f
JOIN Checkouts c ON f.book_id = c.book_id AND f.first_year = c.year

Explanation:

• SELECT f.book_id, f.first_year, c.checkouts, c.price:
  • Selects the book_id, the determined first_year, the number of checkouts, and the price per checkout.
• FROM FirstYear f:
  • Uses the FirstYear CTE as the primary table to access each book's first checkout year.
• JOIN Checkouts c ON f.book_id = c.book_id AND f.first_year = c.year:

  • Joins the Checkouts table to fetch the corresponding checkout details for the first year of each book.

  • f.book_id = c.book_id AND f.first_year = c.year:

    • Ensures that only the records matching the first year are retrieved, effectively linking each book to its first checkout record.

Output After Step 2:

+---------+------------+----------+-------+
| book_id | first_year | checkouts| price |
+---------+------------+----------+-------+
| 100     | 2008       | 40       | 2     |
| 200     | 2011       | 60       | 3     |
+---------+------------+----------+-------+

Step 3: Order the Results by Book ID

Sort the final output in ascending order based on book_id to ensure organized presentation.

SQL Query:

ORDER BY book_id;

Explanation:

• ORDER BY book_id:
  • Sorts the results by book_id in ascending order, making the output orderly and easy to interpret.

Final Output:

+---------+------------+----------+-------+
| book_id | first_year | checkouts| price |
+---------+------------+----------+-------+
| 100     | 2008       | 40       | 2     |
| 200     | 2011       | 60       | 3     |
+---------+------------+----------+-------+