Lesson 4: Build a Friend's Book Tracker :books:

-- You have 4 tables ready to explore:
-- users (id, name, email, phone)
-- books (id, title, author, isbn, genre)
-- loans (id, user_id, book_id, borrowed_at, due_date, returned_at)
-- fines (id, loan_id, amount, paid)

-- Quick exploration - see what's in each table:
SELECT * FROM users LIMIT 3;
SELECT * FROM books LIMIT 3;
SELECT * FROM loans LIMIT 3;

-- Try to find loans for Harry Potter books
SELECT * FROM loans WHERE returned_at IS NULL;

loan_id | user_id | book_id | borrowed_at | due_date | returned_at
1       | 3       | 5       | 2024-01-10  | 2024-01-24 | NULL
2       | 7       | 12      | 2024-01-12  | 2024-01-26 | NULL

-- Magic happens with INNER JOIN!
SELECT 
    books.title,
    users.name AS borrower,
    loans.borrowed_at,
    loans.due_date
FROM loans
INNER JOIN books ON loans.book_id = books.id
INNER JOIN users ON loans.user_id = users.id
WHERE loans.returned_at IS NULL;

title                           | borrower    | borrowed_at | due_date
Harry Potter and the Sorcerer's Stone | Mike Chen | 2024-01-10  | 2024-01-24
The Great Gatsby               | Emma Wilson | 2024-01-12  | 2024-01-26

-- INNER JOIN: Only users who have borrowed books
SELECT users.name, COUNT(loans.id) as books_borrowed
FROM users
INNER JOIN loans ON users.id = loans.user_id
GROUP BY users.id, users.name;

-- LEFT JOIN: ALL users, even if they never borrowed
SELECT users.name, COUNT(loans.id) as books_borrowed
FROM users
LEFT JOIN loans ON users.id = loans.user_id
GROUP BY users.id, users.name;

-- Find overdue books with contact info and auto-calculated fines
SELECT 
    books.title,
    users.name AS borrower,
    users.email,
    users.phone,
    loans.due_date,
    JULIANDAY('now') - JULIANDAY(loans.due_date) AS days_overdue,
    CAST((JULIANDAY('now') - JULIANDAY(loans.due_date)) * 0.50 AS DECIMAL(10,2)) AS fine_amount,
    CASE 
        WHEN JULIANDAY('now') - JULIANDAY(loans.due_date) > 7 THEN '🚨 URGENT'
        WHEN JULIANDAY('now') - JULIANDAY(loans.due_date) > 0 THEN '⚠️ Overdue'
        WHEN JULIANDAY(loans.due_date) - JULIANDAY('now') <= 2 THEN '📅 Due Soon'
        ELSE '✅ On Time'
    END AS status
FROM loans
INNER JOIN books ON loans.book_id = books.id
INNER JOIN users ON loans.user_id = users.id
WHERE loans.returned_at IS NULL
  AND loans.due_date < DATE('now')
ORDER BY days_overdue DESC;

-- Which books should we buy more copies of?
SELECT 
    books.title,
    books.author,
    COUNT(loans.id) AS times_borrowed,
    COUNT(CASE WHEN loans.returned_at IS NULL THEN 1 END) AS currently_out,
    ROUND(AVG(JULIANDAY(COALESCE(loans.returned_at, 'now')) - JULIANDAY(loans.borrowed_at)), 1) AS avg_days_kept
FROM books
LEFT JOIN loans ON books.id = loans.book_id
GROUP BY books.id, books.title, books.author
HAVING COUNT(loans.id) > 0
ORDER BY times_borrowed DESC
LIMIT 10;

-- Hint: Self-join the loans table
-- Find users who borrowed the same books

-- Hint: Use LEFT JOIN and check for NULL
-- Books with no active loans are available

-- Same pattern for e-commerce:
SELECT 
    products.name,
    customers.email,
    orders.order_date,
    order_items.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

-- Track medications with schedules:
SELECT 
    medications.name,
    schedules.time,
    taken_logs.taken_at
FROM medication_schedules schedules
INNER JOIN medications ON schedules.medication_id = medications.id
LEFT JOIN taken_logs ON schedules.id = taken_logs.schedule_id
    AND DATE(taken_logs.taken_at) = DATE('now');

-- INNER JOIN: Both tables must match
FROM table1 INNER JOIN table2 ON table1.id = table2.foreign_id

-- LEFT JOIN: All from left table, matches from right
FROM table1 LEFT JOIN table2 ON table1.id = table2.foreign_id

-- Multiple JOINs: Chain them together
FROM table1 
INNER JOIN table2 ON table1.id = table2.foreign_id
INNER JOIN table3 ON table2.id = table3.foreign_id

-- Find records with no match
LEFT JOIN ... WHERE right_table.id IS NULL

-- Count related records
LEFT JOIN ... GROUP BY ... COUNT(right_table.id)

-- Conditional aggregation
COUNT(CASE WHEN condition THEN 1 END)