Assumptions

<aside> 💡

• Return period = 30 days
• Fine = $0.50 per overdue day
• return_status may have NULL rows for non-returned books
• “Current date” for analysis = 2024-08-31 </aside>

Build a Unified Issue-Level Analytical Base Table

The raw data is spread across multiple normalized tables (issued_status, return_status, books, members) and cannot be directly used for analytics.

<aside> 📌

Analytics should not be performed on fragmented transactional tables. When data is spread across multiple normalized tables, it increases the risk of inconsistent joins, repeated calculations, and logic errors across queries.

</aside>

So I created a consolidated analytical table ‘books_analysis_base’ where each row represents a single book issue, along with derived metrics required for downstream analysis. It captures borrowing behavior, return delays, and overdue risk in a structured format.

-- set analysis date explicitly 
SET @analysis_date = '2024-08-31';

-- Consolidated Analytical Table: books_analysis_base
CREATE TABLE books_analysis_base AS
SELECT
	ist.issued_id,
    ist.issued_member_id AS member_id,
    ist.issued_book_isbn AS isbn,
    b.category,
    b.rental_price,
    m.reg_date,
    ist.issued_date,
	rst.return_date,
	DATEDIFF(COALESCE(rst.return_date,@analysis_date),ist.issued_date) AS days_held,
    
    CASE
		WHEN DATEDIFF(COALESCE(rst.return_date,@analysis_date),ist.issued_date) <= 30 THEN 0 
        ELSE DATEDIFF(COALESCE(rst.return_date,@analysis_date),ist.issued_date) - 30
	END 
    AS overdue_days,
    
    CASE
		WHEN DATEDIFF(COALESCE(rst.return_date,@analysis_date),ist.issued_date) > 30 THEN 1 
        ELSE 0
	END 
    AS is_overdue
    
FROM issued_status ist
LEFT JOIN return_status rst
ON ist.issued_id = rst.issued_id
LEFT JOIN books b
ON ist.issued_book_isbn = b.isbn
LEFT JOIN members m
ON ist.issued_member_id = m.member_id;

SELECT * FROM books_analysis_base;

books_analysis_base

The unified book issued-level dataset shows:

• 15 total book issued
• A mix of:
  • On-time returns
  • Late returns
  • Non-returned books
• Spread of holding durations (18–169 days)

This view becomes the foundation for all further analysis

Task 1: Identify Member-Level Risk and Fine Exposure

Task 2: Identify High-Risk Members