library_analytics_queries.sql

-- Useful analytics queries for the Library Management project
USE library_db;

-- 1. Reservable books
SELECT b.book_id, b.title, b.copies_available
FROM books b
WHERE CAST(b.copies_available AS SIGNED) > 0
ORDER BY b.title;

-- 2. Low stock books
SELECT b.book_id, b.title, b.copies_available
FROM books b
WHERE CAST(b.copies_available AS SIGNED) <= 2
ORDER BY CAST(b.copies_available AS SIGNED), b.title;

-- 3. Inventory by category
SELECT c.name, COUNT(b.book_id) AS book_count, SUM(CAST(b.copies_available AS SIGNED)) AS total_copies
FROM categories c
LEFT JOIN books b ON b.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY c.name;

-- 4. Pending reservation queue
SELECT r.reservation_id, b.title, m.first_name, m.last_name, r.reservation_date
FROM reservations r
JOIN books b ON b.book_id = r.book_id
JOIN members m ON m.member_id = r.member_id
WHERE r.status = 0
ORDER BY r.reservation_date ASC;

-- 5. Most reserved books
SELECT b.book_id, b.title, COUNT(r.reservation_id) AS reservation_count
FROM books b
JOIN reservations r ON r.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY reservation_count DESC, b.title ASC
LIMIT 10;

-- 6. Books never loaned
SELECT b.book_id, b.title
FROM books b
LEFT JOIN loans l ON b.book_id = l.book_id
WHERE l.book_id IS NULL;

-- 7. Publication decade trends
SELECT CONCAT(YEAR(b.publication_year) - (YEAR(b.publication_year) % 10), 's') AS decade,
       COUNT(*) AS book_count
FROM books b
GROUP BY decade
ORDER BY decade;

-- 8. Peak loaning days
SELECT l.loan_date AS borrow_day, COUNT(*) AS borrow_count
FROM loans l
GROUP BY l.loan_date
ORDER BY borrow_count DESC;

-- 9. Fine-generating books
SELECT b.book_id, b.title, SUM(CAST(f.amount AS DECIMAL(10,2))) AS total_fines
FROM fines f
JOIN loans l ON f.loan_id = l.loan_id
JOIN books b ON l.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY total_fines DESC
LIMIT 10;

-- 10. Member activity summary
SELECT m.member_id,
       CONCAT(m.first_name, ' ', m.last_name) AS member_name,
       COUNT(DISTINCT l.loan_id) AS loans_count,
       COUNT(DISTINCT r.reservation_id) AS reservations_count,
       COALESCE(lp.points, 0) AS loyalty_points,
       COALESCE(lp.stamps, 0) AS stamps
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
LEFT JOIN reservations r ON r.member_id = m.member_id
LEFT JOIN loyalty_profiles lp ON lp.member_id = m.member_id
GROUP BY m.member_id, m.first_name, m.last_name, lp.points, lp.stamps
ORDER BY member_name;