analysis.sql

-- All books with availability
SELECT * FROM v_book_availability ORDER BY book_series, book_title;

-- Current loans
SELECT loan.loan_id, book.book_title, patron.patron_first_name, patron.patron_last_name, loan.loan_date, loan.loan_due_date
FROM loan
JOIN book   ON book.book_id = loan.loan_book_id
JOIN patron ON patron.patron_id = loan.loan_patron_id
WHERE loan.loan_return_date IS NULL
ORDER BY loan.loan_due_date;

-- Overdue loans
SELECT book.book_title, patron.patron_first_name, patron.patron_last_name, loan.loan_due_date
FROM loan
JOIN book   ON book.book_id = loan.loan_book_id
JOIN patron ON patron.patron_id = loan.loan_patron_id
WHERE loan.loan_return_date IS NULL AND loan.loan_due_date < CURDATE();

-- Most-borrowed books
SELECT book.book_title, book.book_author, COUNT(loan.loan_id) AS times_loaned
FROM book
LEFT JOIN loan ON loan.loan_book_id = book.book_id
GROUP BY book.book_id, book.book_title, book.book_author
ORDER BY times_loaned DESC, book.book_title
LIMIT 5;


-- Books never loaned 
SELECT book.book_id, book.book_title, book.book_series
FROM book
LEFT JOIN loan ON loan.loan_book_id = book.book_id
WHERE loan.loan_id IS NULL;

-- Loans per series
SELECT book.book_series, COUNT(loan.loan_id) AS total_loans
FROM book
LEFT JOIN loan ON loan.loan_book_id = book.book_id
GROUP BY book.book_series;

-- Average loan duration
WITH returned AS (
  SELECT loan.loan_id, DATEDIFF(loan.loan_return_date, loan.loan_date) AS duration_days
  FROM loan
  WHERE loan.loan_return_date IS NOT NULL
)
SELECT AVG(duration_days) AS avg_loan_duration_days FROM returned;

-- Rank patrons by total loans
SELECT v_patron_activity.patron_first_name, v_patron_activity.patron_last_name,
       v_patron_activity.total_loans,
       DENSE_RANK() OVER (ORDER BY v_patron_activity.total_loans DESC) AS loan_rank
FROM v_patron_activity;

-- Next available date 
SELECT book.book_title,
  CASE
    WHEN EXISTS (SELECT 1 FROM loan WHERE loan.loan_book_id = book.book_id AND loan.loan_return_date IS NULL)
    THEN (SELECT loan.loan_due_date FROM loan WHERE loan.loan_book_id = book.book_id AND loan.loan_return_date IS NULL ORDER BY loan.loan_due_date DESC LIMIT 1)
    ELSE 'Available now'
  END AS next_available
FROM book
ORDER BY book.book_title;