Lesson 4: Build a Friend's Book Tracker 📚

-- 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)

-- ✅ EFFICIENT: Always JOIN on indexed columns (usually primary/foreign keys)
SELECT b.title, u.name
FROM loans l
INNER JOIN books b ON l.book_id = b.id          -- b.id is primary key (indexed)
INNER JOIN users u ON l.user_id = u.id;         -- u.id is primary key (indexed)

-- ❌ SLOW: JOINing on non-indexed columns
SELECT b.title, u.name
FROM loans l
INNER JOIN books b ON l.book_title = b.title    -- title not indexed
INNER JOIN users u ON l.user_email = u.email;   -- email might not be indexed

-- ✅ OPTIMAL: Start with most selective table (smallest result set)
SELECT b.title, u.name, l.due_date
FROM loans l                                     -- Start with specific loans
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id
WHERE l.returned_at IS NULL                     -- Filter early
  AND l.due_date < CURRENT_DATE;                 -- Most selective condition first

-- ❌ SUBOPTIMAL: Large table first, filter late
SELECT b.title, u.name, l.due_date
FROM books b                                     -- All books (largest table)
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN users u ON l.user_id = u.id
WHERE l.returned_at IS NULL
  AND l.due_date < CURRENT_DATE;

-- ✅ PROFESSIONAL: Clear aliases and formatting
SELECT 
    b.title AS book_title,
    b.author,
    u.name AS borrower_name,
    u.email AS contact_email,
    l.borrowed_at,
    l.due_date
FROM loans l
    INNER JOIN books b ON l.book_id = b.id
    INNER JOIN users u ON l.user_id = u.id
WHERE l.returned_at IS NULL
ORDER BY l.due_date ASC;

-- ❌ AMATEUR: Hard to read and maintain
select books.title,books.author,users.name,users.email,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 order by loans.due_date;

-- ✅ SAFE: Handle potential NULL relationships
SELECT 
    b.title,
    COALESCE(u.name, 'Unknown User') AS borrower,
    COALESCE(l.due_date, 'No due date') AS due_date
FROM books b
    LEFT JOIN loans l ON b.id = l.book_id AND l.returned_at IS NULL
    LEFT JOIN users u ON l.user_id = u.id
WHERE b.genre = 'Fiction'
ORDER BY b.title;

-- ✅ VALIDATE: Check JOIN results make sense
-- Count records before and after JOIN to ensure no unexpected data loss
SELECT COUNT(*) FROM loans WHERE returned_at IS NULL;  -- Should match your active loans

-- ✅ STANDARD: Always use proper foreign key constraints
CREATE TABLE loans (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    borrowed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    due_date DATE NOT NULL,
    returned_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT
);

-- ✅ STANDARD: Create indexes for JOIN performance
CREATE INDEX idx_loans_user_id ON loans(user_id);
CREATE INDEX idx_loans_book_id ON loans(book_id);
CREATE INDEX idx_loans_returned_at ON loans(returned_at);
CREATE INDEX idx_loans_due_date ON loans(due_date);

-- ❌ PROBLEM: Missing or incorrect JOIN condition
SELECT b.title, u.name
FROM books b, users u, loans l;  -- No JOIN conditions!
-- This returns books.count * users.count * loans.count rows!

-- ✅ FIX: Always specify proper JOIN conditions
SELECT b.title, u.name
FROM loans l
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id;

-- ❌ PROBLEM: Using INNER JOIN when you need LEFT JOIN
SELECT b.title, u.name
FROM books b
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN users u ON l.user_id = u.id;
-- This excludes books that are never borrowed!

-- ✅ FIX: Use LEFT JOIN to include all books
SELECT 
    b.title,
    COALESCE(u.name, 'Available') AS status
FROM books b
LEFT JOIN loans l ON b.id = l.book_id AND l.returned_at IS NULL
LEFT JOIN users u ON l.user_id = u.id;

-- ❌ PROBLEM: One-to-many relationship creates duplicates
SELECT b.title, COUNT(*) as loan_count
FROM books b
INNER JOIN loans l ON b.id = l.book_id;
-- This gives wrong count because of duplicate book titles

-- ✅ FIX: Use proper GROUP BY
SELECT b.title, COUNT(l.id) as loan_count
FROM books b
LEFT JOIN loans l ON b.id = l.book_id
GROUP BY b.id, b.title
ORDER BY loan_count DESC;

-- ❌ ERROR: Column exists in multiple tables
SELECT id, title, name
FROM books b
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN users u ON l.user_id = u.id;
-- Error: "id" exists in books, loans, and users tables

-- ✅ FIX: Always use table aliases
SELECT 
    b.id AS book_id,
    b.title,
    u.name
FROM books b
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN users u ON l.user_id = u.id;

-- ✅ DIAGNOSIS: Use EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
SELECT b.title, u.name
FROM loans l
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id
WHERE l.due_date < CURRENT_DATE;

-- Look for "SCAN" (bad) vs "SEARCH" (good)
-- If you see SCAN, create indexes:
CREATE INDEX idx_loans_due_date ON loans(due_date);

-- Start with a simple 2-table JOIN
SELECT COUNT(*) FROM loans l
INNER JOIN books b ON l.book_id = b.id;

-- Then add complexity gradually
SELECT COUNT(*) FROM loans l
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id;

-- Check record counts make sense
-- Before JOIN:
SELECT COUNT(*) FROM loans WHERE returned_at IS NULL;  -- e.g., 15 active loans

-- After JOIN:
SELECT COUNT(*) FROM loans l
INNER JOIN books b ON l.book_id = b.id
WHERE l.returned_at IS NULL;  -- Should still be 15

-- ✅ GOOD: Consistent table aliases
SELECT 
    b.title,     -- books always "b"
    u.name,      -- users always "u"
    l.due_date   -- loans always "l"
FROM loans l
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id;

-- Customer order analysis (similar to book lending)
SELECT 
    c.name AS customer_name,
    p.product_name,
    o.order_date,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01';

-- Friend connections (similar to book recommendations)
SELECT 
    u1.username AS user,
    u2.username AS friend,
    f.created_at AS friendship_date
FROM friendships f
INNER JOIN users u1 ON f.user_id = u1.id
INNER JOIN users u2 ON f.friend_id = u2.id
WHERE u1.id = 123;

-- Task assignments (similar to book loans)
SELECT 
    p.project_name,
    t.task_name,
    u.name AS assigned_to,
    t.due_date
FROM tasks t
INNER JOIN projects p ON t.project_id = p.id
INNER JOIN users u ON t.assigned_to = u.id
WHERE t.status = 'In Progress';

-- Basic INNER JOIN (only matching records)
SELECT t1.column, t2.column
FROM table1 t1
INNER JOIN table2 t2 ON t1.foreign_key = t2.primary_key;

-- LEFT JOIN (all from left, matching from right)
SELECT t1.column, t2.column
FROM table1 t1
LEFT JOIN table2 t2 ON t1.foreign_key = t2.primary_key;

-- Multi-table JOIN
SELECT t1.column, t2.column, t3.column
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
INNER JOIN table3 t3 ON t2.id = t3.table2_id;

-- Standard foreign key relationship
ON child_table.parent_id = parent_table.id

-- Multiple conditions
ON table1.id = table2.foreign_id 
   AND table2.status = 'active'
   AND table2.created_at >= '2024-01-01'

-- Self-join (same table)
ON e1.manager_id = e2.employee_id  -- employees and their managers

-- Date-based conditions
ON loan.book_id = book.id 
   AND loan.returned_at IS NULL    -- only active loans

-- Count related records
SELECT 
    parent.name,
    COUNT(child.id) as child_count
FROM parent_table parent
LEFT JOIN child_table child ON parent.id = child.parent_id
GROUP BY parent.id, parent.name;

-- Sum with conditions
SELECT 
    customer.name,
    SUM(CASE WHEN order.status = 'completed' THEN order.total ELSE 0 END) as total_spent
FROM customers customer
LEFT JOIN orders order ON customer.id = order.customer_id
GROUP BY customer.id, customer.name;

-- Create indexes for JOIN columns
CREATE INDEX idx_foreign_key ON child_table(parent_id);
CREATE INDEX idx_status_date ON child_table(status, created_at);

-- Use EXPLAIN to check performance
EXPLAIN QUERY PLAN
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id;

-- Filter early for better performance
SELECT t1.name, t2.value
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.status = 'active'      -- Filter on indexed column
  AND t2.created_at >= '2024-01-01'  -- Filter on joined table
LIMIT 100;

-- Find records without matches (orphaned records)
SELECT parent.*
FROM parent_table parent
LEFT JOIN child_table child ON parent.id = child.parent_id
WHERE child.parent_id IS NULL;

-- Top N per group
SELECT *
FROM (
    SELECT 
        category.name,
        product.name,
        product.price,
        ROW_NUMBER() OVER (PARTITION BY category.id ORDER BY product.price DESC) as rank
    FROM categories category
    INNER JOIN products product ON category.id = product.category_id
) ranked
WHERE rank <= 3;  -- Top 3 products per category

-- Hierarchical data (self-join)
SELECT 
    emp.name as employee,
    mgr.name as manager
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;