-- Declarative SQL: What we want
SELECT branch_name, SUM(transaction_amount) as total_revenue
FROM transactions t
JOIN branches b ON t.branch_id = b.branch_id
WHERE transaction_date >= '2024-01-01'
GROUP BY branch_name
HAVING SUM(transaction_amount) > 1000000
ORDER BY total_revenue DESC;

-- Customer Table: Information about bank customers
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    date_of_birth DATE,
    registration_date DATE,
    customer_segment VARCHAR(20), -- 'Premium', 'Standard', 'Basic'
    credit_score INT,
    annual_income DECIMAL(12,2),
    state VARCHAR(2),
    is_active BOOLEAN
);

-- Accounts Table: Customer accounts
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    customer_id INT,
    account_type VARCHAR(20), -- 'Checking', 'Savings', 'Credit Card', 'Mortgage'
    account_number VARCHAR(20),
    open_date DATE,
    close_date DATE,
    current_balance DECIMAL(12,2),
    interest_rate DECIMAL(5,2),
    credit_limit DECIMAL(12,2), -- For credit cards
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Transactions Table: All financial transactions
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    account_id INT,
    transaction_date DATE,
    transaction_time TIME,
    transaction_type VARCHAR(20), -- 'Deposit', 'Withdrawal', 'Payment', 'Purchase'
    amount DECIMAL(12,2),
    description VARCHAR(200),
    merchant VARCHAR(100), -- For card transactions
    category VARCHAR(50), -- 'Groceries', 'Utilities', 'Dining', etc.
    status VARCHAR(20), -- 'Pending', 'Completed', 'Failed'
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

-- Branches Table: Bank branch locations
CREATE TABLE branches (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(100),
    address VARCHAR(200),
    city VARCHAR(50),
    state VARCHAR(2),
    region VARCHAR(20), -- 'Northeast', 'Southeast', 'Midwest', 'West', 'Southwest'
    manager_name VARCHAR(100),
    open_date DATE
);

-- Employee Table: Bank employees
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    branch_id INT,
    role VARCHAR(50), -- 'Teller', 'Manager', 'Loan Officer', 'Analyst'
    hire_date DATE,
    salary DECIMAL(12,2),
    FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
);

-- Loans Table: Loan products
CREATE TABLE loans (
    loan_id INT PRIMARY KEY,
    customer_id INT,
    loan_type VARCHAR(50), -- 'Mortgage', 'Auto', 'Personal', 'Student'
    original_amount DECIMAL(12,2),
    current_balance DECIMAL(12,2),
    interest_rate DECIMAL(5,2),
    term_months INT,
    origination_date DATE,
    maturity_date DATE,
    status VARCHAR(20), -- 'Current', 'Delinquent', 'Default', 'Paid Off'
    collateral_type VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Loan Payments Table: Individual loan payments
CREATE TABLE loan_payments (
    payment_id INT PRIMARY KEY,
    loan_id INT,
    payment_date DATE,
    payment_amount DECIMAL(12,2),
    principal_amount DECIMAL(12,2),
    interest_amount DECIMAL(12,2),
    late_fee DECIMAL(12,2),
    payment_method VARCHAR(20), -- 'ACH', 'Check', 'Wire', 'Online'
    FOREIGN KEY (loan_id) REFERENCES loans(loan_id)
);

-- Query 1: Find all customers from California
-- Business Context: The marketing team wants to reach customers in California
-- with a new product offering

SELECT 
    customer_id,        -- Unique identifier for the customer
    first_name,         -- Customer's first name
    last_name,          -- Customer's last name
    email,              -- Customer's email address for marketing
    registration_date,  -- When the customer opened their account
    customer_segment,   -- Premium, Standard, or Basic
    annual_income       -- Customer's annual income in USD
FROM customers          -- The table we are querying
WHERE state = 'CA'     -- Filter to only California customers
    AND is_active = TRUE -- Only currently active customers
    AND annual_income > 50000 -- Focus on higher-income customers
ORDER BY annual_income DESC; -- Sort from highest to lowest income

/* 
COMMENTARY: This query is typical of what a marketing analyst would run 
to identify high-value customers in a specific region. The ORDER BY 
DESC ensures the highest-income customers appear first.
*/

-- Query 2: Find all deposits made in the last 30 days
-- Business Context: Treasury needs to understand recent deposit inflows

SELECT 
    transaction_id,     -- Unique transaction identifier
    account_id,         -- Which account was credited
    transaction_date,   -- Date of the deposit
    transaction_time,   -- Time of the deposit
    amount,             -- Amount deposited
    description         -- Description of the deposit source
FROM transactions
WHERE transaction_type = 'Deposit'  -- Only deposit transactions
    AND transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    -- ^^ This filters to the last 30 days
    AND status = 'Completed'  -- Only completed transactions
ORDER BY transaction_date DESC, transaction_time DESC;

-- Query 3: List all unique merchant categories used by customers
-- Business Context: Product team wants to understand spending categories

SELECT DISTINCT category      -- DISTINCT removes duplicates
FROM transactions
WHERE category IS NOT NULL    -- Exclude null values
ORDER BY category;            -- Alphabetical order for readability

-- Query 4: Sample the most recent transactions
-- Business Context: Quick spot-check of transaction activity

SELECT 
    transaction_id,
    account_id,
    transaction_date,
    amount,
    merchant
FROM transactions
ORDER BY transaction_date DESC
LIMIT 100;  -- Only return the 100 most recent transactions

-- Query 5: Find high-risk accounts for compliance review
-- Business Context: Compliance team needs to review accounts with 
-- unusual patterns that might indicate money laundering

SELECT 
    a.account_id,
    a.account_number,
    a.current_balance,
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state,
    COUNT(t.transaction_id) as transaction_count,
    SUM(t.amount) as total_transaction_amount
FROM accounts a
JOIN customers c ON a.customer_id = c.customer_id
JOIN transactions t ON a.account_id = t.account_id
WHERE 
    -- Account criteria
    a.account_type = 'Checking'
    AND a.current_balance > 50000  -- High balance accounts
    
    -- Customer criteria
    AND c.customer_segment = 'Standard'  -- Not premium accounts
    AND c.credit_score < 650  -- Lower credit scores
    
    -- Transaction criteria
    AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    AND t.transaction_type IN ('Withdrawal', 'Payment')
    AND t.amount > 5000  -- Large transactions
    
    -- Exclude certain patterns
    AND t.merchant NOT LIKE '%Internal Transfer%'
    AND t.status = 'Completed'
    
GROUP BY 
    a.account_id,
    a.account_number,
    a.current_balance,
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state
HAVING 
    -- Only accounts with unusual activity
    COUNT(t.transaction_id) > 20  -- High transaction count
    AND SUM(t.amount) > 100000    -- High total amount
ORDER BY total_transaction_amount DESC;

/* 
COMMENTARY: This is a realistic compliance query that combines multiple 
criteria to identify potentially suspicious accounts. The HAVING clause 
allows us to filter based on aggregated results (transaction count and 
total amount) that couldn't be filtered in the WHERE clause.
*/

-- Query 6: Find all customers with checking accounts
-- Business Context: Identify customers who need to be migrated to new banking platform

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    a.account_id,
    a.account_number,
    a.current_balance,
    a.open_date
FROM customers c
INNER JOIN accounts a ON c.customer_id = a.customer_id
WHERE a.account_type = 'Checking'
    AND a.is_active = TRUE
    AND c.is_active = TRUE
ORDER BY c.last_name, c.first_name;

/* 
COMMENTARY: This query uses INNER JOIN to find customers who have checking 
accounts. If a customer exists in the customers table but has no checking 
account, they will not appear in the results. This is useful when we only 
want customers with the specific product we are interested in.
*/

-- Query 7: All customers with their account information
-- Business Context: Customer service needs a complete list of all customers

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.phone,
    c.customer_segment,
    a.account_id,
    a.account_type,
    a.current_balance,
    CASE 
        WHEN a.account_id IS NULL THEN 'No Active Accounts'
        ELSE 'Has Accounts'
    END as account_status
FROM customers c
LEFT JOIN accounts a ON c.customer_id = a.customer_id
    AND a.is_active = TRUE  -- Only active accounts
WHERE c.is_active = TRUE
ORDER BY c.last_name, c.first_name;

/* 
COMMENTARY: This query uses LEFT JOIN to include all customers, even those 
without accounts. The CASE statement creates a flag to distinguish between 
customers with and without accounts. This is valuable for customer service 
or marketing to understand account penetration.
*/

-- Query 8: Complete customer financial picture
-- Business Context: Wealth management team needs a comprehensive view

SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) as full_name,
    c.customer_segment,
    c.annual_income,
    c.credit_score,
    
    -- Account information
    COUNT(DISTINCT a.account_id) as total_accounts,
    SUM(CASE WHEN a.account_type = 'Checking' THEN a.current_balance ELSE 0 END) as total_checking_balance,
    SUM(CASE WHEN a.account_type = 'Savings' THEN a.current_balance ELSE 0 END) as total_savings_balance,
    SUM(CASE WHEN a.account_type = 'Credit Card' THEN a.current_balance ELSE 0 END) as total_credit_balance,
    
    -- Transaction summary (last 90 days)
    COUNT(DISTINCT t.transaction_id) as transaction_count,
    SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE 0 END) as total_deposits,
    SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) as total_withdrawals,
    
    -- Loan information
    COUNT(DISTINCT l.loan_id) as total_loans,
    SUM(l.current_balance) as total_loan_balance,
    
    -- Calculate net worth (simplified)
    (SUM(CASE WHEN a.account_type IN ('Checking', 'Savings') THEN a.current_balance ELSE 0 END) -
     SUM(CASE WHEN a.account_type = 'Credit Card' THEN a.current_balance ELSE 0 END) -
     SUM(l.current_balance)) as estimated_net_worth

FROM customers c
LEFT JOIN accounts a ON c.customer_id = a.customer_id AND a.is_active = TRUE
LEFT JOIN transactions t ON a.account_id = t.account_id 
    AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    AND t.status = 'Completed'
LEFT JOIN loans l ON c.customer_id = l.customer_id 
    AND l.status IN ('Current', 'Delinquent')

WHERE c.is_active = TRUE
GROUP BY 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.customer_segment,
    c.annual_income,
    c.credit_score
HAVING 
    -- Focus on high-value or at-risk customers
    estimated_net_worth > 100000 
    OR total_loan_balance > 100000
    OR c.credit_score < 650
ORDER BY estimated_net_worth DESC;

/* 
COMMENTARY: This is a comprehensive query that uses multiple LEFT JOINs to 
build a complete financial picture of each customer. It combines customer 
information, account balances, transaction history, and loan data into a 
single view. The HAVING clause filters to show only high-value or at-risk 
customers. This type of query would be used by wealth management teams, 
risk analysts, and relationship managers.
*/

-- Query 9: Daily transaction summary by account type
-- Business Context: Treasury needs to understand transaction patterns

SELECT 
    DATE(t.transaction_date) as transaction_day,
    a.account_type,
    COUNT(t.transaction_id) as transaction_count,
    SUM(t.amount) as total_amount,
    AVG(t.amount) as average_amount,
    MIN(t.amount) as min_amount,
    MAX(t.amount) as max_amount,
    SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE 0 END) as total_deposits,
    SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) as total_withdrawals,
    COUNT(CASE WHEN t.status = 'Failed' THEN 1 END) as failed_transactions
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY 
    DATE(t.transaction_date),
    a.account_type
ORDER BY 
    transaction_day DESC,
    a.account_type;

/* 
COMMENTARY: This query groups transactions by day and account type, 
providing a comprehensive view of transaction activity. The CASE statements 
within the SUM() and COUNT() functions allow us to perform conditional 
aggregations, such as only counting deposits or failed transactions.
*/

-- Query 10: Customer segment profitability analysis
-- Business Context: Management wants to understand which segments are most profitable

SELECT 
    c.customer_segment,
    COUNT(DISTINCT c.customer_id) as customer_count,
    AVG(c.annual_income) as avg_income,
    AVG(c.credit_score) as avg_credit_score,
    
    -- Account metrics
    COUNT(DISTINCT a.account_id) as total_accounts,
    AVG(a.current_balance) as avg_account_balance,
    SUM(a.current_balance) as total_balances,
    
    -- Transaction metrics
    COUNT(t.transaction_id) as total_transactions,
    SUM(t.amount) as total_transaction_amount,
    AVG(t.amount) as avg_transaction_amount,
    
    -- Loan metrics
    COUNT(DISTINCT l.loan_id) as total_loans,
    SUM(l.current_balance) as total_loan_balance,
    AVG(l.current_balance) as avg_loan_balance,
    
    -- Calculate a simple profitability proxy
    (SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount * 0.01 ELSE 0 END) +  -- Interest income
     SUM(CASE WHEN a.account_type = 'Credit Card' THEN a.current_balance * 0.12 ELSE 0 END) +  -- Credit card revenue
     SUM(CASE WHEN l.loan_id IS NOT NULL THEN l.current_balance * (l.interest_rate/100) ELSE 0 END)) as estimated_annual_revenue

FROM customers c
LEFT JOIN accounts a ON c.customer_id = a.customer_id AND a.is_active = TRUE
LEFT JOIN transactions t ON a.account_id = t.account_id 
    AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    AND t.status = 'Completed'
LEFT JOIN loans l ON c.customer_id = l.customer_id 
    AND l.status IN ('Current', 'Delinquent')

WHERE c.is_active = TRUE
GROUP BY c.customer_segment
ORDER BY estimated_annual_revenue DESC;

/* 
COMMENTARY: This query demonstrates how grouping by customer segment 
can reveal which segments are most profitable. It uses multiple 
aggregations to build a comprehensive picture of each segment's 
financial contribution. The estimated_annual_revenue calculation is 
simplified but shows how revenue can be derived from different product 
types.
*/

-- Query 11: Find branches with high transaction volume and low failure rate
-- Business Context: Operations team wants to identify high-performing branches

SELECT 
    b.branch_id,
    b.branch_name,
    b.city,
    b.state,
    b.region,
    COUNT(DISTINCT a.account_id) as account_count,
    COUNT(DISTINCT c.customer_id) as customer_count,
    COUNT(t.transaction_id) as transaction_count,
    SUM(t.amount) as total_transaction_amount,
    AVG(t.amount) as average_transaction_amount,
    COUNT(CASE WHEN t.status = 'Failed' THEN 1 END) as failed_transactions,
    (COUNT(CASE WHEN t.status = 'Failed' THEN 1 END) * 100.0 / COUNT(t.transaction_id)) as failure_rate_percent
FROM branches b
JOIN employees e ON b.branch_id = e.branch_id
JOIN customers c ON c.customer_id IN (SELECT customer_id FROM accounts WHERE branch_id = b.branch_id)
JOIN accounts a ON c.customer_id = a.customer_id
JOIN transactions t ON a.account_id = t.account_id
WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    AND b.is_active = TRUE
GROUP BY 
    b.branch_id,
    b.branch_name,
    b.city,
    b.state,
    b.region
HAVING 
    -- Only include branches with significant activity
    transaction_count > 1000
    AND total_transaction_amount > 1000000
    -- Exclude branches with high failure rates
    AND failure_rate_percent < 5.0
ORDER BY failure_rate_percent ASC, total_transaction_amount DESC;

/* 
COMMENTARY: The HAVING clause is crucial here because we need to filter 
based on aggregations (transaction_count, total_transaction_amount, 
failure_rate_percent) that can only be calculated after the data is 
grouped. This type of analysis helps operations teams identify which 
branches are performing well and which need intervention.
*/

-- Query 12: Identify customers with above-average transaction activity
-- Business Context: Marketing wants to target high-activity customers

SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.customer_segment,
    c.annual_income,
    customer_activity.activity_count,
    customer_activity.total_amount
FROM customers c
JOIN (
    -- Subquery: Calculate customer activity
    SELECT 
        a.customer_id,
        COUNT(t.transaction_id) as activity_count,
        SUM(t.amount) as total_amount
    FROM accounts a
    JOIN transactions t ON a.account_id = t.account_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
        AND t.status = 'Completed'
    GROUP BY a.customer_id
) customer_activity ON c.customer_id = customer_activity.customer_id
WHERE 
    c.is_active = TRUE
    -- Compare to overall average
    AND customer_activity.activity_count > (
        SELECT AVG(activity_count)
        FROM (
            SELECT COUNT(t.transaction_id) as activity_count
            FROM accounts a
            JOIN transactions t ON a.account_id = t.account_id
            WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
                AND t.status = 'Completed'
            GROUP BY a.customer_id
        ) avg_activity
    )
    -- Compare to overall average amount
    AND customer_activity.total_amount > (
        SELECT AVG(total_amount)
        FROM (
            SELECT SUM(t.amount) as total_amount
            FROM accounts a
            JOIN transactions t ON a.account_id = t.account_id
            WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
                AND t.status = 'Completed'
            GROUP BY a.customer_id
        ) avg_amount
    )
ORDER BY customer_activity.total_amount DESC;

/* 
COMMENTARY: This query uses two subqueries to identify customers whose 
activity is above average. The first subquery (customer_activity) 
calculates activity for each customer. The second and third subqueries 
(in the WHERE clause) calculate the overall averages for comparison. 
This type of analysis helps identify top-performing customers for 
targeted marketing or retention efforts.
*/

-- Query 13: Find unusual transaction patterns (potential fraud)
-- Business Context: Fraud detection team needs to investigate unusual patterns

SELECT 
    t1.transaction_id,
    t1.account_id,
    t1.transaction_date,
    t1.amount,
    t1.merchant,
    t1.category,
    avg_amount.avg_transaction_amount as account_average,
    (t1.amount / avg_amount.avg_transaction_amount) as deviation_ratio
FROM transactions t1
JOIN (
    -- Correlated subquery to calculate average transaction for each account
    SELECT 
        account_id,
        AVG(amount) as avg_transaction_amount,
        STDDEV(amount) as stddev_transaction_amount
    FROM transactions
    WHERE transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
        AND status = 'Completed'
    GROUP BY account_id
) avg_amount ON t1.account_id = avg_amount.account_id
WHERE 
    t1.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)  -- Last 7 days
    AND t1.status = 'Completed'
    -- Flag transactions significantly above average
    AND t1.amount > (avg_amount.avg_transaction_amount + 3 * avg_amount.stddev_transaction_amount)
    -- Exclude known legitimate patterns
    AND t1.category NOT IN ('Utilities', 'Rent', 'Insurance')
ORDER BY deviation_ratio DESC;

/* 
COMMENTARY: This query uses a correlated subquery to calculate the 
average transaction amount and standard deviation for each account. It 
then flags transactions that are more than 3 standard deviations above 
the account's average, which could indicate fraud or unusual activity. 
The deviation_ratio quantifies how unusual the transaction is, helping 
investigators prioritize their work.
*/

-- Query 14: Analyze customer financial health and risk
-- Business Context: Risk management needs to identify financially stressed customers

WITH 
-- CTE 1: Calculate customer transaction patterns
customer_transactions AS (
    SELECT 
        c.customer_id,
        COUNT(t.transaction_id) as total_transactions,
        SUM(t.amount) as total_amount,
        AVG(t.amount) as avg_amount,
        COUNT(CASE WHEN t.transaction_type = 'Withdrawal' THEN 1 END) as withdrawal_count,
        SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) as total_withdrawals,
        COUNT(CASE WHEN t.transaction_type = 'Deposit' THEN 1 END) as deposit_count,
        SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE 0 END) as total_deposits,
        -- Calculate net cash flow
        SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE -t.amount END) as net_cash_flow,
        -- Calculate spending to income ratio
        SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) / 
            NULLIF(c.annual_income / 12, 0) as monthly_spending_to_income
    FROM customers c
    JOIN accounts a ON c.customer_id = a.customer_id
    JOIN transactions t ON a.account_id = t.account_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
        AND t.status = 'Completed'
        AND c.is_active = TRUE
    GROUP BY c.customer_id, c.annual_income
),

-- CTE 2: Calculate loan burden
customer_loan_burden AS (
    SELECT 
        customer_id,
        COUNT(loan_id) as total_loans,
        SUM(current_balance) as total_loan_balance,
        AVG(interest_rate) as avg_interest_rate,
        SUM(current_balance) / NULLIF(annual_income, 0) as debt_to_income_ratio,
        COUNT(CASE WHEN status = 'Delinquent' THEN 1 END) as delinquent_loans
    FROM loans
    GROUP BY customer_id, annual_income
),

-- CTE 3: Calculate account utilization (for credit cards)
account_utilization AS (
    SELECT 
        customer_id,
        SUM(CASE WHEN account_type = 'Credit Card' THEN current_balance ELSE 0 END) as total_credit_balance,
        SUM(CASE WHEN account_type = 'Credit Card' THEN credit_limit ELSE 0 END) as total_credit_limit,
        SUM(CASE WHEN account_type = 'Credit Card' THEN current_balance ELSE 0 END) / 
            NULLIF(SUM(CASE WHEN account_type = 'Credit Card' THEN credit_limit ELSE 0 END), 0) as credit_utilization_rate
    FROM accounts
    WHERE is_active = TRUE
    GROUP BY customer_id
)

-- Final query: Combine all CTEs
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.customer_segment,
    c.annual_income,
    c.credit_score,
    
    -- Transaction metrics
    ct.total_transactions,
    ct.total_amount,
    ct.avg_amount,
    ct.net_cash_flow,
    ct.monthly_spending_to_income,
    
    -- Loan metrics
    COALESCE(clb.total_loans, 0) as total_loans,
    COALESCE(clb.total_loan_balance, 0) as total_loan_balance,
    COALESCE(clb.debt_to_income_ratio, 0) as debt_to_income_ratio,
    COALESCE(clb.delinquent_loans, 0) as delinquent_loans,
    
    -- Credit metrics
    COALESCE(au.total_credit_limit, 0) as total_credit_limit,
    COALESCE(au.credit_utilization_rate, 0) as credit_utilization_rate,
    
    -- Risk flags
    CASE 
        WHEN COALESCE(clb.debt_to_income_ratio, 0) > 0.43 THEN 'High Debt Burden'
        WHEN COALESCE(clb.delinquent_loans, 0) > 0 THEN 'Delinquent Loans'
        WHEN COALESCE(au.credit_utilization_rate, 0) > 0.8 THEN 'High Credit Utilization'
        WHEN c.credit_score < 650 THEN 'Low Credit Score'
        WHEN ct.monthly_spending_to_income > 0.5 THEN 'High Spending'
        ELSE 'Healthy'
    END as risk_level

FROM customers c
LEFT JOIN customer_transactions ct ON c.customer_id = ct.customer_id
LEFT JOIN customer_loan_burden clb ON c.customer_id = clb.customer_id
LEFT JOIN account_utilization au ON c.customer_id = au.customer_id
WHERE c.is_active = TRUE
ORDER BY risk_level DESC, debt_to_income_ratio DESC;

/* 
COMMENTARY: This comprehensive query demonstrates the power of CTEs for 
organizing complex analytical logic. Each CTE calculates a different 
aspect of the customer's financial health, and then the final query 
combines them into a single view. The CASE statement at the end classifies 
customers into risk levels, enabling risk management teams to prioritize 
their attention.
*/

-- Query 15: Calculate running balance and moving averages
-- Business Context: Treasury needs to track account trends

SELECT 
    transaction_id,
    account_id,
    transaction_date,
    transaction_type,
    amount,
    -- Running balance (using window function)
    SUM(CASE 
        WHEN transaction_type IN ('Deposit', 'Payment') THEN amount
        WHEN transaction_type IN ('Withdrawal', 'Purchase') THEN -amount
        ELSE 0
    END) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_date, transaction_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_balance,
    
    -- 7-day moving average (using window function)
    AVG(amount) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_moving_avg,
    
    -- Rank transactions by amount within each account
    RANK() OVER (
        PARTITION BY account_id 
        ORDER BY amount DESC
    ) as amount_rank_within_account,
    
    -- Percentage of total per account
    amount / NULLIF(SUM(amount) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ), 0) * 100 as pct_of_account_total

FROM transactions
WHERE transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    AND status = 'Completed'
ORDER BY account_id, transaction_date DESC;

/* 
COMMENTARY: This query demonstrates the power of window functions for 
banking analytics. The running_balance calculates the account balance 
after each transaction. The seven_day_moving_avg identifies trends 
by smoothing out daily volatility. The amount_rank_within_account shows 
which transactions are largest for each account. The pct_of_account_total 
shows each transaction's contribution to the account's total activity.
*/

-- Query 16: Customer profitability ranking and segmentation
-- Business Context: Wealth management uses this for client tiering

WITH customer_metrics AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.customer_segment,
        c.annual_income,
        c.credit_score,
        SUM(a.current_balance) as total_balance,
        COUNT(DISTINCT a.account_id) as account_count,
        COUNT(t.transaction_id) as transaction_count,
        SUM(t.amount) as total_transaction_amount,
        -- Calculate revenue proxy
        SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount * 0.01 ELSE 0 END) +
        SUM(CASE WHEN a.account_type = 'Credit Card' THEN a.current_balance * 0.12 ELSE 0 END) +
        SUM(CASE WHEN l.loan_id IS NOT NULL THEN l.current_balance * (l.interest_rate/100) ELSE 0 END) as estimated_revenue
    FROM customers c
    LEFT JOIN accounts a ON c.customer_id = a.customer_id AND a.is_active = TRUE
    LEFT JOIN transactions t ON a.account_id = t.account_id 
        AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
        AND t.status = 'Completed'
    LEFT JOIN loans l ON c.customer_id = l.customer_id 
        AND l.status IN ('Current', 'Delinquent')
    WHERE c.is_active = TRUE
    GROUP BY 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.customer_segment,
        c.annual_income,
        c.credit_score
)

SELECT 
    customer_id,
    first_name,
    last_name,
    customer_segment,
    annual_income,
    credit_score,
    total_balance,
    account_count,
    transaction_count,
    total_transaction_amount,
    estimated_revenue,
    
    -- Overall rankings
    RANK() OVER (ORDER BY estimated_revenue DESC) as revenue_rank,
    RANK() OVER (ORDER BY total_balance DESC) as balance_rank,
    RANK() OVER (ORDER BY transaction_count DESC) as activity_rank,
    
    -- Segment-specific rankings
    RANK() OVER (
        PARTITION BY customer_segment 
        ORDER BY estimated_revenue DESC
    ) as revenue_rank_within_segment,
    
    -- Percentile calculations
    PERCENT_RANK() OVER (ORDER BY estimated_revenue DESC) as revenue_percentile,
    NTILE(10) OVER (ORDER BY estimated_revenue DESC) as revenue_decile,
    
    -- Tier assignment based on multiple factors
    CASE 
        WHEN estimated_revenue > 10000 AND credit_score > 750 THEN 'Platinum'
        WHEN estimated_revenue > 5000 AND credit_score > 700 THEN 'Gold'
        WHEN estimated_revenue > 2000 THEN 'Silver'
        WHEN transaction_count > 50 THEN 'Active Standard'
        ELSE 'Standard'
    END as wealth_tier

FROM customer_metrics
ORDER BY revenue_rank;

/* 
COMMENTARY: This query uses multiple window functions to rank customers 
on various dimensions (revenue, balance, activity) and assign tiers. 
The NTILE function divides customers into 10 equal groups (deciles) 
based on revenue, which is useful for segmentation. The wealth_tier 
column combines multiple factors to create a holistic customer tiering 
system. This type of analysis is fundamental for wealth management and 
relationship banking.
*/

-- Query 17: Loan aging and delinquency analysis
-- Business Context: Collections team needs to prioritize delinquent loans

SELECT 
    l.loan_id,
    l.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) as customer_name,
    l.loan_type,
    l.original_amount,
    l.current_balance,
    l.interest_rate,
    l.origination_date,
    l.maturity_date,
    l.status,
    
    -- Calculate loan age
    DATEDIFF(CURRENT_DATE, l.origination_date) as days_since_origination,
    TIMESTAMPDIFF(MONTH, l.origination_date, CURRENT_DATE) as months_since_origination,
    
    -- Calculate days since last payment
    (SELECT DATEDIFF(CURRENT_DATE, MAX(payment_date))
     FROM loan_payments lp
     WHERE lp.loan_id = l.loan_id) as days_since_last_payment,
    
    -- Calculate payment status
    CASE 
        WHEN l.status = 'Current' THEN 'Current'
        WHEN DATEDIFF(CURRENT_DATE, 
            (SELECT MAX(payment_date) FROM loan_payments lp WHERE lp.loan_id = l.loan_id)) > 90 THEN '90+ Days Delinquent'
        WHEN DATEDIFF(CURRENT_DATE, 
            (SELECT MAX(payment_date) FROM loan_payments lp WHERE lp.loan_id = l.loan_id)) > 60 THEN '60-89 Days Delinquent'
        WHEN DATEDIFF(CURRENT_DATE, 
            (SELECT MAX(payment_date) FROM loan_payments lp WHERE lp.loan_id = l.loan_id)) > 30 THEN '30-59 Days Delinquent'
        ELSE 'Current'
    END as delinquency_status,
    
    -- Calculate days until maturity
    DATEDIFF(l.maturity_date, CURRENT_DATE) as days_until_maturity,
    
    -- Calculate payment history (last 12 months)
    (SELECT COUNT(payment_id) 
     FROM loan_payments lp 
     WHERE lp.loan_id = l.loan_id 
         AND payment_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)) as payments_last_12_months,
    
    (SELECT AVG(payment_amount) 
     FROM loan_payments lp 
     WHERE lp.loan_id = l.loan_id 
         AND payment_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)) as avg_payment_last_12_months

FROM loans l
JOIN customers c ON l.customer_id = c.customer_id
WHERE l.status IN ('Current', 'Delinquent')
    AND c.is_active = TRUE
ORDER BY 
    CASE 
        WHEN l.status = 'Delinquent' THEN 0
        ELSE 1
    END,
    days_since_last_payment DESC,
    current_balance DESC;

/* 
COMMENTARY: This query uses multiple date functions to analyze loan 
performance and delinquency. The DATEDIFF function calculates the age 
of loans and time since last payment. The CASE statement based on 
DATEDIFF results categorizes delinquency status. The subqueries in 
the SELECT clause calculate payment history metrics for each loan. 
This is exactly the type of analysis that collections teams use to 
prioritize their work and compliance teams use for regulatory reporting.
*/

-- Query 18: Analyze transaction patterns by time of day and day of week
-- Business Context: Operations planning for staffing and system capacity

SELECT 
    -- Time dimension breakdowns
    CASE DAYOFWEEK(transaction_date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END as day_of_week,
    
    CASE 
        WHEN HOUR(transaction_time) BETWEEN 6 AND 8 THEN 'Early Morning (6-8a)'
        WHEN HOUR(transaction_time) BETWEEN 9 AND 11 THEN 'Morning (9-11a)'
        WHEN HOUR(transaction_time) BETWEEN 12 AND 14 THEN 'Midday (12-2p)'
        WHEN HOUR(transaction_time) BETWEEN 15 AND 17 THEN 'Afternoon (3-5p)'
        WHEN HOUR(transaction_time) BETWEEN 18 AND 20 THEN 'Evening (6-8p)'
        WHEN HOUR(transaction_time) BETWEEN 21 AND 23 THEN 'Night (9-11p)'
        ELSE 'Overnight (12-5a)'
    END as time_block,
    
    -- Transaction statistics
    COUNT(transaction_id) as transaction_count,
    SUM(amount) as total_amount,
    AVG(amount) as average_amount,
    COUNT(CASE WHEN transaction_type = 'Deposit' THEN 1 END) as deposit_count,
    COUNT(CASE WHEN transaction_type = 'Withdrawal' THEN 1 END) as withdrawal_count,
    COUNT(CASE WHEN transaction_type = 'Purchase' THEN 1 END) as purchase_count,
    
    -- Compare to average
    COUNT(transaction_id) * 100.0 / (
        SELECT COUNT(transaction_id) 
        FROM transactions 
        WHERE transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
            AND status = 'Completed'
    ) as pct_of_total_volume

FROM transactions
WHERE transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    AND status = 'Completed'
GROUP BY 
    DAYOFWEEK(transaction_date),
    HOUR(transaction_time)
ORDER BY 
    DAYOFWEEK(transaction_date),
    HOUR(transaction_time);

/* 
COMMENTARY: This query analyzes transaction patterns by time of day and 
day of week. The DAYOFWEEK function extracts the day of week number 
(1-7), which is then converted to a readable string. The HOUR function 
extracts the hour from the transaction_time, which is then grouped into 
time blocks. This analysis helps operations teams schedule staff, 
maintain systems, and predict peak transaction times.
*/

-- Example: Creating indexes for common banking queries

-- Index on frequently filtered columns
CREATE INDEX idx_transactions_date ON transactions(transaction_date);
CREATE INDEX idx_transactions_account_id ON transactions(account_id);
CREATE INDEX idx_transactions_type ON transactions(transaction_type);

-- Compound index for common combinations
CREATE INDEX idx_transactions_account_date ON transactions(account_id, transaction_date);

-- Index on foreign keys
CREATE INDEX idx_accounts_customer_id ON accounts(customer_id);
CREATE INDEX idx_loans_customer_id ON loans(customer_id);

-- Consider this: The database can use idx_transactions_account_date to quickly find
-- all transactions for a specific account in a specific date range, without scanning
-- the entire table.

-- Inefficient: Join first, then filter
SELECT *
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
WHERE a.account_type = 'Checking'
    AND t.transaction_date >= '2024-01-01';

-- More efficient: Filter before join
SELECT *
FROM accounts a
JOIN (SELECT * FROM transactions 
      WHERE transaction_date >= '2024-01-01') t 
    ON a.account_id = t.account_id
WHERE a.account_type = 'Checking';

-- Even more efficient: Filter in the WHERE clause
-- The database optimizer often does this automatically
-- But it's good practice to be explicit

-- Use EXPLAIN to see how the database will execute your query
EXPLAIN SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(t.amount) as total_transactions
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
JOIN transactions t ON a.account_id = t.account_id
WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY c.customer_id, c.first_name, c.last_name;

/*
EXPLAIN output shows:
- Which indexes are being used (or not)
- How many rows are being scanned
- What join methods are being used (Nested Loop, Hash Join, Merge Join)
- Estimated query cost

This information helps you identify performance bottlenecks.
*/

-- Inefficient: Retrieves all columns, many not needed
SELECT * FROM customers WHERE state = 'CA';

-- Efficient: Only retrieve needed columns
SELECT customer_id, first_name, last_name, email 
FROM customers 
WHERE state = 'CA';

-- Inefficient: Using VARCHAR for numeric data
CREATE TABLE transactions_bad (
    amount VARCHAR(20),  -- Bad: Should be DECIMAL
    transaction_date VARCHAR(20)  -- Bad: Should be DATE
);

-- Efficient: Using appropriate data types
CREATE TABLE transactions_good (
    amount DECIMAL(12,2),  -- Good: Proper numeric type
    transaction_date DATE  -- Good: Proper date type
);

-- Create the database
CREATE DATABASE bank_analytics;
USE bank_analytics;

-- Create the tables (as defined earlier in this lesson)
-- We'll create a simplified version for the lab

-- Customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    phone TEXT,
    date_of_birth DATE,
    registration_date DATE,
    customer_segment TEXT,
    credit_score INTEGER,
    annual_income DECIMAL(12,2),
    state TEXT,
    is_active INTEGER
);

-- Accounts table
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    account_type TEXT,
    account_number TEXT,
    open_date DATE,
    close_date DATE,
    current_balance DECIMAL(12,2),
    interest_rate DECIMAL(5,2),
    credit_limit DECIMAL(12,2),
    is_active INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Transactions table
CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    account_id INTEGER,
    transaction_date DATE,
    transaction_time TIME,
    transaction_type TEXT,
    amount DECIMAL(12,2),
    description TEXT,
    merchant TEXT,
    category TEXT,
    status TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

-- Branches table
CREATE TABLE branches (
    branch_id INTEGER PRIMARY KEY,
    branch_name TEXT,
    address TEXT,
    city TEXT,
    state TEXT,
    region TEXT,
    manager_name TEXT,
    open_date DATE,
    is_active INTEGER
);

-- Insert sample customers
INSERT INTO customers VALUES 
(1, 'John', 'Smith', 'john.smith@email.com', '555-0101', '1980-05-15', '2015-01-01', 'Premium', 780, 150000, 'CA', 1),
(2, 'Jane', 'Doe', 'jane.doe@email.com', '555-0102', '1985-08-22', '2016-06-01', 'Standard', 720, 85000, 'NY', 1),
(3, 'Bob', 'Johnson', 'bob.johnson@email.com', '555-0103', '1990-12-01', '2018-03-15', 'Standard', 680, 65000, 'TX', 1),
(4, 'Alice', 'Williams', 'alice.williams@email.com', '555-0104', '1975-03-10', '2012-07-01', 'Premium', 820, 200000, 'CA', 1),
(5, 'Charlie', 'Brown', 'charlie.brown@email.com', '555-0105', '1995-07-20', '2020-01-01', 'Basic', 620, 45000, 'FL', 1);

-- Insert sample accounts
INSERT INTO accounts VALUES 
(101, 1, 'Checking', 'CHK1001', '2015-01-01', NULL, 25000, 0.00, NULL, 1),
(102, 1, 'Savings', 'SAV1001', '2015-01-01', NULL, 75000, 2.50, NULL, 1),
(103, 1, 'Credit Card', 'CC1001', '2015-06-01', NULL, -5000, 15.99, 15000, 1),
(104, 2, 'Checking', 'CHK2001', '2016-06-01', NULL, 15000, 0.00, NULL, 1),
(105, 2, 'Credit Card', 'CC2001', '2016-07-01', NULL, -3000, 18.99, 10000, 1),
(106, 3, 'Checking', 'CHK3001', '2018-03-15', NULL, 8000, 0.00, NULL, 1),
(107, 3, 'Savings', 'SAV3001', '2018-03-15', NULL, 12000, 2.00, NULL, 1),
(108, 4, 'Checking', 'CHK4001', '2012-07-01', NULL, 45000, 0.00, NULL, 1),
(109, 4, 'Savings', 'SAV4001', '2012-07-01', NULL, 150000, 3.00, NULL, 1),
(110, 4, 'Credit Card', 'CC4001', '2012-08-01', NULL, -8000, 14.99, 25000, 1),
(111, 5, 'Checking', 'CHK5001', '2020-01-01', NULL, 5000, 0.00, NULL, 1);

-- Insert sample transactions (last 30 days)
INSERT INTO transactions VALUES 
-- Customer 1 (John Smith) - Premium customer
(1, 101, '2024-01-15', '09:30:00', 'Deposit', 5000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(2, 101, '2024-01-16', '14:22:00', 'Withdrawal', 500, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(3, 103, '2024-01-16', '19:45:00', 'Purchase', 150.50, 'Amazon.com', 'Amazon', 'Online Shopping', 'Completed'),
(4, 103, '2024-01-17', '12:10:00', 'Purchase', 85.30, 'Whole Foods', 'Whole Foods', 'Groceries', 'Completed'),
(5, 103, '2024-01-18', '08:20:00', 'Payment', 2000, 'Credit card payment', NULL, 'Payment', 'Completed'),
(6, 102, '2024-01-20', '09:15:00', 'Deposit', 10000, 'Transfer from checking', NULL, 'Transfer', 'Completed'),
(7, 101, '2024-01-22', '11:30:00', 'Deposit', 5000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(8, 103, '2024-01-23', '16:45:00', 'Purchase', 120.75, 'Shell Gas', 'Shell', 'Transportation', 'Completed'),
(9, 103, '2024-01-24', '13:00:00', 'Purchase', 45.20, 'Starbucks', 'Starbucks', 'Dining', 'Completed'),
(10, 101, '2024-01-25', '08:00:00', 'Withdrawal', 200, 'ATM withdrawal', NULL, 'Cash', 'Completed'),

-- Customer 2 (Jane Doe) - Standard customer
(11, 104, '2024-01-15', '10:00:00', 'Deposit', 3000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(12, 104, '2024-01-16', '15:30:00', 'Withdrawal', 200, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(13, 105, '2024-01-17', '09:45:00', 'Purchase', 200.00, 'Target', 'Target', 'Shopping', 'Completed'),
(14, 105, '2024-01-18', '11:20:00', 'Purchase', 60.00, 'Walmart', 'Walmart', 'Groceries', 'Completed'),
(15, 104, '2024-01-19', '09:00:00', 'Deposit', 3000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(16, 105, '2024-01-20', '14:15:00', 'Purchase', 45.50, 'Starbucks', 'Starbucks', 'Dining', 'Completed'),
(17, 104, '2024-01-22', '08:30:00', 'Withdrawal', 300, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(18, 105, '2024-01-23', '19:00:00', 'Purchase', 89.99, 'Amazon.com', 'Amazon', 'Online Shopping', 'Completed'),
(19, 105, '2024-01-24', '12:30:00', 'Purchase', 25.00, 'CVS', 'CVS', 'Health', 'Completed'),
(20, 104, '2024-01-25', '11:00:00', 'Deposit', 3000, 'Payroll deposit', NULL, 'Income', 'Completed'),

-- Customer 3 (Bob Johnson) - Standard customer
(21, 106, '2024-01-15', '08:30:00', 'Deposit', 2000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(22, 106, '2024-01-16', '17:30:00', 'Withdrawal', 100, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(23, 107, '2024-01-17', '09:00:00', 'Deposit', 500, 'Transfer from checking', NULL, 'Transfer', 'Completed'),
(24, 106, '2024-01-18', '12:00:00', 'Purchase', 120.00, 'Publix', 'Publix', 'Groceries', 'Completed'),
(25, 106, '2024-01-19', '19:00:00', 'Purchase', 40.00, 'McDonalds', 'McDonalds', 'Dining', 'Completed'),
(26, 106, '2024-01-22', '08:30:00', 'Deposit', 2000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(27, 106, '2024-01-23', '16:00:00', 'Withdrawal', 150, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(28, 107, '2024-01-24', '10:00:00', 'Deposit', 300, 'Transfer from checking', NULL, 'Transfer', 'Completed'),
(29, 106, '2024-01-25', '13:30:00', 'Purchase', 75.00, 'Home Depot', 'Home Depot', 'Home Improvement', 'Completed'),

-- Customer 4 (Alice Williams) - Premium customer
(30, 108, '2024-01-15', '09:00:00', 'Deposit', 8000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(31, 108, '2024-01-16', '14:00:00', 'Withdrawal', 500, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(32, 110, '2024-01-17', '11:30:00', 'Purchase', 300.00, 'Neiman Marcus', 'Neiman Marcus', 'Shopping', 'Completed'),
(33, 110, '2024-01-18', '18:00:00', 'Purchase', 150.00, 'The Capital Grille', 'The Capital Grille', 'Dining', 'Completed'),
(34, 108, '2024-01-19', '09:00:00', 'Deposit', 8000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(35, 110, '2024-01-20', '10:00:00', 'Purchase', 200.00, 'Amazon.com', 'Amazon', 'Online Shopping', 'Completed'),
(36, 108, '2024-01-22', '08:30:00', 'Deposit', 8000, 'Payroll deposit', NULL, 'Income', 'Completed'),
(37, 110, '2024-01-23', '12:00:00', 'Purchase', 80.00, 'Whole Foods', 'Whole Foods', 'Groceries', 'Completed'),
(38, 109, '2024-01-24', '15:00:00', 'Deposit', 15000, 'Bonus deposit', NULL, 'Income', 'Completed'),
(39, 108, '2024-01-25', '16:30:00', 'Withdrawal', 300, 'ATM withdrawal', NULL, 'Cash', 'Completed'),

-- Customer 5 (Charlie Brown) - Basic customer
(40, 111, '2024-01-15', '10:30:00', 'Deposit', 1500, 'Payroll deposit', NULL, 'Income', 'Completed'),
(41, 111, '2024-01-16', '20:00:00', 'Withdrawal', 50, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(42, 111, '2024-01-17', '12:30:00', 'Purchase', 30.00, 'Walmart', 'Walmart', 'Groceries', 'Completed'),
(43, 111, '2024-01-18', '18:00:00', 'Purchase', 25.00, 'McDonalds', 'McDonalds', 'Dining', 'Completed'),
(44, 111, '2024-01-22', '10:30:00', 'Deposit', 1500, 'Payroll deposit', NULL, 'Income', 'Completed'),
(45, 111, '2024-01-23', '14:00:00', 'Purchase', 15.00, 'CVS', 'CVS', 'Health', 'Completed'),
(46, 111, '2024-01-24', '11:00:00', 'Withdrawal', 50, 'ATM withdrawal', NULL, 'Cash', 'Completed'),
(47, 111, '2024-01-25', '16:00:00', 'Purchase', 20.00, 'Pizza Hut', 'Pizza Hut', 'Dining', 'Failed');

-- Insert sample branches
INSERT INTO branches VALUES
(1, 'Main Street Branch', '100 Main St', 'New York', 'NY', 'Northeast', 'Sarah Johnson', '2010-01-01', 1),
(2, 'Park Avenue Branch', '500 Park Ave', 'Los Angeles', 'CA', 'West', 'Michael Chen', '2011-03-15', 1),
(3, 'Market Street Branch', '200 Market St', 'Austin', 'TX', 'Southwest', 'David Rodriguez', '2012-06-01', 1),
(4, 'Ocean Drive Branch', '100 Ocean Dr', 'Miami', 'FL', 'Southeast', 'Maria Garcia', '2013-09-01', 1);

-- Lab Query: Customer Profitability Analysis
-- This query calculates profitability metrics for each customer

WITH 
-- CTE 1: Calculate total transaction activity by customer
customer_transactions AS (
    SELECT 
        a.customer_id,
        COUNT(t.transaction_id) as transaction_count,
        SUM(CASE WHEN t.transaction_type IN ('Deposit', 'Payment') AND t.amount > 0 THEN t.amount ELSE 0 END) as total_inflows,
        SUM(CASE WHEN t.transaction_type IN ('Withdrawal', 'Purchase') AND t.amount > 0 THEN t.amount ELSE 0 END) as total_outflows,
        SUM(CASE WHEN t.status = 'Failed' THEN 1 ELSE 0 END) as failed_transactions,
        AVG(t.amount) as avg_transaction_amount
    FROM transactions t
    JOIN accounts a ON t.account_id = a.account_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
        AND a.is_active = 1
    GROUP BY a.customer_id
),

-- CTE 2: Calculate account balances by customer
customer_balances AS (
    SELECT 
        customer_id,
        SUM(CASE WHEN account_type = 'Checking' AND is_active = 1 THEN current_balance ELSE 0 END) as total_checking_balance,
        SUM(CASE WHEN account_type = 'Savings' AND is_active = 1 THEN current_balance ELSE 0 END) as total_savings_balance,
        SUM(CASE WHEN account_type = 'Credit Card' AND is_active = 1 THEN current_balance ELSE 0 END) as total_credit_balance,
        SUM(CASE WHEN account_type IN ('Checking', 'Savings') AND is_active = 1 THEN current_balance ELSE 0 END) as total_deposit_balance,
        COUNT(CASE WHEN account_type = 'Checking' AND is_active = 1 THEN 1 END) as checking_account_count,
        COUNT(CASE WHEN account_type = 'Savings' AND is_active = 1 THEN 1 END) as savings_account_count,
        COUNT(CASE WHEN account_type = 'Credit Card' AND is_active = 1 THEN 1 END) as credit_card_count
    FROM accounts
    GROUP BY customer_id
),

-- CTE 3: Calculate revenue proxies by customer
customer_revenue AS (
    SELECT 
        a.customer_id,
        -- Interest income from deposit accounts (simplified: assuming 2% interest on average balance)
        (SUM(CASE WHEN a.account_type IN ('Checking', 'Savings') AND a.is_active = 1 
             THEN a.current_balance * 0.02 / 12 
             ELSE 0 END)) as monthly_interest_income,
        
        -- Credit card revenue (fees + interest, simplified: assuming 12% annual interest on credit balances)
        (SUM(CASE WHEN a.account_type = 'Credit Card' AND a.is_active = 1 AND a.current_balance < 0
             THEN ABS(a.current_balance) * 0.12 / 12
             ELSE 0 END)) as monthly_credit_card_revenue,
        
        -- Transaction fees (simplified: 1% of transactions)
        (SUM(CASE WHEN t.transaction_type = 'Purchase' AND t.status = 'Completed'
             THEN t.amount * 0.01
             ELSE 0 END)) as monthly_transaction_fees
    FROM accounts a
    LEFT JOIN transactions t ON a.account_id = t.account_id
        AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
        AND t.status = 'Completed'
    WHERE a.is_active = 1
    GROUP BY a.customer_id
)

-- Final query: Combine all metrics
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.customer_segment,
    c.annual_income,
    c.credit_score,
    c.state,
    
    -- Transaction metrics
    COALESCE(ct.transaction_count, 0) as transaction_count,
    COALESCE(ct.total_inflows, 0) as total_inflows,
    COALESCE(ct.total_outflows, 0) as total_outflows,
    COALESCE(ct.avg_transaction_amount, 0) as avg_transaction_amount,
    COALESCE(ct.failed_transactions, 0) as failed_transactions,
    COALESCE(ct.failed_transactions * 100.0 / NULLIF(ct.transaction_count, 0), 0) as failure_rate,
    
    -- Balance metrics
    COALESCE(cb.total_deposit_balance, 0) as total_deposit_balance,
    COALESCE(cb.total_credit_balance, 0) as total_credit_balance,
    COALESCE(cb.total_deposit_balance + cb.total_credit_balance, 0) as net_balance,
    cb.checking_account_count,
    cb.savings_account_count,
    cb.credit_card_count,
    
    -- Revenue metrics
    COALESCE(cr.monthly_interest_income, 0) as monthly_interest_income,
    COALESCE(cr.monthly_credit_card_revenue, 0) as monthly_credit_card_revenue,
    COALESCE(cr.monthly_transaction_fees, 0) as monthly_transaction_fees,
    COALESCE(cr.monthly_interest_income + cr.monthly_credit_card_revenue + cr.monthly_transaction_fees, 0) as total_monthly_revenue,
    
    -- Derived metrics
    CASE 
        WHEN c.customer_segment = 'Premium' AND COALESCE(ct.transaction_count, 0) > 10 THEN 'High-Value Premium'
        WHEN c.customer_segment = 'Premium' AND COALESCE(ct.transaction_count, 0) <= 10 THEN 'Low-Activity Premium'
        WHEN c.customer_segment = 'Standard' AND COALESCE(ct.transaction_count, 0) > 10 THEN 'High-Activity Standard'
        WHEN c.customer_segment = 'Standard' AND COALESCE(ct.transaction_count, 0) <= 10 THEN 'Standard'
        WHEN c.customer_segment = 'Basic' AND COALESCE(cb.total_deposit_balance, 0) > 10000 THEN 'High-Value Basic'
        ELSE c.customer_segment
    END as customer_subsegment,
    
    -- Ranking
    RANK() OVER (ORDER BY COALESCE(cr.monthly_interest_income + cr.monthly_credit_card_revenue + cr.monthly_transaction_fees, 0) DESC) as revenue_rank

FROM customers c
LEFT JOIN customer_transactions ct ON c.customer_id = ct.customer_id
LEFT JOIN customer_balances cb ON c.customer_id = cb.customer_id
LEFT JOIN customer_revenue cr ON c.customer_id = cr.customer_id
WHERE c.is_active = 1
ORDER BY revenue_rank;

-- Lab Query: Branch Performance Analysis
-- This query analyzes branch-level metrics

WITH 
-- CTE 1: Customer counts by branch
branch_customers AS (
    SELECT 
        b.branch_id,
        COUNT(DISTINCT c.customer_id) as customer_count,
        COUNT(DISTINCT a.account_id) as account_count
    FROM branches b
    LEFT JOIN employees e ON b.branch_id = e.branch_id
    LEFT JOIN customers c ON c.state = b.state  -- Simplified: assumes customers in same state
    LEFT JOIN accounts a ON c.customer_id = a.customer_id
    WHERE b.is_active = 1
    GROUP BY b.branch_id
),

-- CTE 2: Transaction volume by branch
branch_transactions AS (
    SELECT 
        b.branch_id,
        COUNT(t.transaction_id) as transaction_count,
        SUM(t.amount) as total_transaction_amount,
        AVG(t.amount) as avg_transaction_amount,
        COUNT(CASE WHEN t.status = 'Failed' THEN 1 END) as failed_transactions
    FROM branches b
    LEFT JOIN employees e ON b.branch_id = e.branch_id
    LEFT JOIN accounts a ON a.customer_id IN (SELECT customer_id FROM customers WHERE state = b.state)
    LEFT JOIN transactions t ON a.account_id = t.account_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
        AND b.is_active = 1
    GROUP BY b.branch_id
)

SELECT 
    b.branch_id,
    b.branch_name,
    b.city,
    b.state,
    b.region,
    b.manager_name,
    
    -- Customer metrics
    COALESCE(bc.customer_count, 0) as customer_count,
    COALESCE(bc.account_count, 0) as account_count,
    COALESCE(bc.account_count * 1.0 / NULLIF(bc.customer_count, 0), 0) as accounts_per_customer,
    
    -- Transaction metrics
    COALESCE(bt.transaction_count, 0) as transaction_count,
    COALESCE(bt.total_transaction_amount, 0) as total_transaction_amount,
    COALESCE(bt.avg_transaction_amount, 0) as avg_transaction_amount,
    COALESCE(bt.failed_transactions, 0) as failed_transactions,
    COALESCE(bt.failed_transactions * 100.0 / NULLIF(bt.transaction_count, 0), 0) as failure_rate,
    COALESCE(bt.total_transaction_amount / NULLIF(bt.transaction_count, 0), 0) as revenue_per_transaction,
    
    -- Performance indicators
    CASE 
        WHEN COALESCE(bt.failed_transactions * 100.0 / NULLIF(bt.transaction_count, 0), 100) < 2.0 
            AND COALESCE(bt.transaction_count, 0) > 100 
            AND COALESCE(bt.total_transaction_amount, 0) > 100000 THEN 'Excellent'
        WHEN COALESCE(bt.failed_transactions * 100.0 / NULLIF(bt.transaction_count, 0), 100) < 5.0 
            AND COALESCE(bt.transaction_count, 0) > 50 THEN 'Good'
        WHEN COALESCE(bt.failed_transactions * 100.0 / NULLIF(bt.transaction_count, 0), 100) < 10.0 THEN 'Satisfactory'
        ELSE 'Needs Improvement'
    END as performance_rating

FROM branches b
LEFT JOIN branch_customers bc ON b.branch_id = bc.branch_id
LEFT JOIN branch_transactions bt ON b.branch_id = bt.branch_id
WHERE b.is_active = 1
ORDER BY performance_rating, total_transaction_amount DESC;

-- Lab Query: Transaction Pattern Analysis
-- This query identifies unusual transaction patterns

WITH 
-- CTE 1: Calculate transaction statistics by customer
customer_stats AS (
    SELECT 
        a.customer_id,
        COUNT(t.transaction_id) as total_transactions,
        AVG(t.amount) as avg_amount,
        STDDEV(t.amount) as stddev_amount,
        MAX(t.amount) as max_amount,
        MIN(t.amount) as min_amount
    FROM accounts a
    JOIN transactions t ON a.account_id = t.account_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
        AND t.status = 'Completed'
    GROUP BY a.customer_id
),

-- CTE 2: Flag unusual transactions
unusual_transactions AS (
    SELECT 
        t.transaction_id,
        t.account_id,
        t.transaction_date,
        t.transaction_time,
        t.transaction_type,
        t.amount,
        t.merchant,
        t.category,
        cs.avg_amount as customer_avg_amount,
        cs.stddev_amount as customer_stddev_amount,
        -- Flag transactions more than 3 standard deviations above average
        CASE 
            WHEN cs.stddev_amount > 0 AND t.amount > (cs.avg_amount + 3 * cs.stddev_amount) THEN 'High-Value Outlier'
            WHEN t.transaction_type = 'Withdrawal' AND t.amount > 1000 THEN 'Large Withdrawal'
            WHEN t.transaction_type = 'Purchase' AND t.category IN ('Online Shopping', 'Electronics') AND t.amount > 500 THEN 'Large Online Purchase'
            WHEN t.merchant IN ('Amazon.com', 'Walmart', 'Target') AND t.amount > 300 THEN 'Large Retail Purchase'
            ELSE 'Normal'
        END as anomaly_flag
    FROM transactions t
    JOIN accounts a ON t.account_id = a.account_id
    JOIN customer_stats cs ON a.customer_id = cs.customer_id
    WHERE t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
        AND t.status = 'Completed'
)

SELECT 
    t.transaction_id,
    t.account_id,
    CONCAT(c.first_name, ' ', c.last_name) as customer_name,
    t.transaction_date,
    t.transaction_time,
    t.transaction_type,
    t.amount,
    t.merchant,
    t.category,
    u.anomaly_flag,
    u.customer_avg_amount,
    u.customer_stddev_amount,
    (t.amount - u.customer_avg_amount) / NULLIF(u.customer_stddev_amount, 0) as z_score
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
JOIN customers c ON a.customer_id = c.customer_id
JOIN unusual_transactions u ON t.transaction_id = u.transaction_id
WHERE u.anomaly_flag != 'Normal'
    AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY z_score DESC;

-- Query: Generate regulatory report for BASEL III capital adequacy
-- This query would be run quarterly by the risk management team

SELECT 
    -- Total exposure
    SUM(l.current_balance) as total_exposure,
    
    -- Risk-weighted assets (simplified)
    SUM(CASE 
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value < 0.6 THEN l.current_balance * 0.35
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value < 0.8 THEN l.current_balance * 0.50
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value >= 0.8 THEN l.current_balance * 0.75
        WHEN l.loan_type = 'Personal' AND c.credit_score > 700 THEN l.current_balance * 0.75
        WHEN l.loan_type = 'Personal' AND c.credit_score <= 700 THEN l.current_balance * 1.00
        ELSE l.current_balance * 1.00
    END) as risk_weighted_assets,
    
    -- Capital requirement (at 8% of risk-weighted assets)
    SUM(CASE 
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value < 0.6 THEN l.current_balance * 0.35 * 0.08
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value < 0.8 THEN l.current_balance * 0.50 * 0.08
        WHEN l.loan_type = 'Mortgage' AND l.loan_to_value >= 0.8 THEN l.current_balance * 0.75 * 0.08
        WHEN l.loan_type = 'Personal' AND c.credit_score > 700 THEN l.current_balance * 0.75 * 0.08
        WHEN l.loan_type = 'Personal' AND c.credit_score <= 700 THEN l.current_balance * 1.00 * 0.08
        ELSE l.current_balance * 1.00 * 0.08
    END) as capital_requirement

FROM loans l
JOIN customers c ON l.customer_id = c.customer_id
WHERE l.status IN ('Current', 'Delinquent')
    AND c.is_active = 1;

/* 
COMMENTARY: This query calculates the capital requirement under BASEL III 
for the bank's loan portfolio. The risk weights are applied based on loan 
type and credit quality. This type of query must be absolutely accurate 
because errors could lead to incorrect capital reporting and regulatory 
penalties.
*/

-- Bad: Vulnerable to SQL injection
SELECT * FROM customers WHERE email = '" + user_input + "';

-- Good: Uses parameterized queries
-- In Python with SQLAlchemy:
session.query(Customer).filter(Customer.email == user_input)

-- In raw SQL with parameters:
SELECT * FROM customers WHERE email = ?;

-- Example: Error handling in stored procedure
DELIMITER $$
CREATE PROCEDURE get_customer_data(IN customer_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Log error
        INSERT INTO error_log(error_time, error_message) 
        VALUES (NOW(), 'Error retrieving customer data');
        -- Return empty result
        SELECT NULL as customer_id, 'Error' as error_message;
    END;
    
    SELECT * FROM customers WHERE customer_id = customer_id;
END$$
DELIMITER ;

-- Example: Audit logging trigger
CREATE TRIGGER audit_customer_access
BEFORE SELECT ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(user_name, accessed_table, access_time, filter_criteria)
    VALUES (CURRENT_USER(), 'customers', NOW(), 'Accessing customer data');
END;

-- Example: Transaction for account transfer
START TRANSACTION;

UPDATE accounts SET current_balance = current_balance - 100 
WHERE account_id = 101 AND current_balance >= 100;

UPDATE accounts SET current_balance = current_balance + 100 
WHERE account_id = 104;

INSERT INTO transactions (account_id, transaction_date, transaction_type, amount, description)
VALUES (101, CURRENT_DATE, 'Withdrawal', 100, 'Transfer to account 104');

INSERT INTO transactions (account_id, transaction_date, transaction_type, amount, description)
VALUES (104, CURRENT_DATE, 'Deposit', 100, 'Transfer from account 101');

COMMIT;