sample_queries.sql

-- 1. Last 10 loans with book titles, member names and return dates
WITH recent_loans AS (
    SELECT l.id AS loan_id, b.title, m.name AS member_name, l.loan_date, l.return_date,
           ROW_NUMBER() OVER (ORDER BY l.loan_date DESC) AS rn
    FROM loans l
    JOIN copies c ON l.copy_id = c.id
    JOIN books b ON c.book_id = b.id
    JOIN members m ON l.member_id = m.id
)
SELECT * FROM recent_loans WHERE rn <= 10;

-- 2. Top 5 members with the most overdue books
SELECT m.name, COUNT(*) AS overdue_count
FROM loans l
JOIN members m ON m.id = l.member_id
WHERE l.return_date IS NULL AND l.due_date < NOW()
GROUP BY m.name
ORDER BY overdue_count DESC
LIMIT 5;

-- 3. Average rating by genre (only books with >= 3 reviews)
WITH book_ratings AS (
    SELECT b.genre, b.id AS book_id, AVG(r.rating) AS avg_rating, COUNT(*) AS review_count
    FROM books b
    JOIN reviews r ON b.id = r.book_id
    GROUP BY b.genre, b.id
)
SELECT genre, ROUND(AVG(avg_rating),2) AS avg_rating
FROM book_ratings
WHERE review_count >= 3
GROUP BY genre
ORDER BY avg_rating DESC;

-- 4. Top 10 members by total reservations and loans
SELECT m.name,
       COUNT(DISTINCT r.id) AS reservations,
       COUNT(DISTINCT l.id) AS loans,
       (COUNT(DISTINCT r.id) + COUNT(DISTINCT l.id)) AS total_activity
FROM members m
LEFT JOIN reservations r ON m.id = r.member_id
LEFT JOIN loans l ON m.id = l.member_id
GROUP BY m.name
ORDER BY total_activity DESC
LIMIT 10;

-- 5. Books that are reserved but have never been loaned
SELECT b.title
FROM books b
JOIN reservations r ON b.id = r.book_id
LEFT JOIN copies c ON c.book_id = b.id
LEFT JOIN loans l ON l.copy_id = c.id
GROUP BY b.title
HAVING COUNT(l.id) = 0;

-- 6. Number of active reservations per member
SELECT m.name, COUNT(*) AS active_reservations
FROM members m
JOIN reservations r ON r.member_id = m.id
WHERE r.status = 'active'
GROUP BY m.name
ORDER BY active_reservations DESC;

-- 7. Top 5 books by number of reviews and average rating
SELECT b.title, COUNT(r.id) AS reviews_count, ROUND(AVG(r.rating),2) AS avg_rating
FROM books b
JOIN reviews r ON b.id = r.book_id
GROUP BY b.title
HAVING COUNT(r.id) >= 3
ORDER BY avg_rating DESC, reviews_count DESC
LIMIT 5;

-- 8. Members who borrow Sci-Fi books most frequently
SELECT m.name, COUNT(*) AS sci_fi_loans
FROM loans l
JOIN copies c ON l.copy_id = c.id
JOIN books b ON c.book_id = b.id
JOIN members m ON m.id = l.member_id
WHERE b.genre = 'Sci-Fi'
GROUP BY m.name
ORDER BY sci_fi_loans DESC
LIMIT 5;

-- 9. Books that have not been loaned in the last 6 months
SELECT b.title
FROM books b
JOIN copies c ON c.book_id = b.id
LEFT JOIN loans l ON l.copy_id = c.id
GROUP BY b.id, b.title
HAVING MAX(l.loan_date) IS NULL OR MAX(l.loan_date) < NOW() - INTERVAL '6 months';

-- 10. Number of books, reviews and reservations per author
SELECT a.name AS author,
       COUNT(DISTINCT b.id) AS books_count,
       COUNT(DISTINCT r.id) AS reviews_count,
       COUNT(DISTINCT rs.id) AS reservations_count
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
LEFT JOIN reviews r ON r.book_id = b.id
LEFT JOIN reservations rs ON rs.book_id = b.id
GROUP BY a.name
ORDER BY books_count DESC;