Credit Risk Assessment

Project Overview

Dataset

SQL Analysis

Query 1: Default Rates by Credit Score Range

Segment applicants into credit score buckets and calculate the default rate for each range. This is the foundation of any credit risk model.

SELECT
    CASE
        WHEN ch.credit_score >= 750 THEN 'Excellent (750+)'
        WHEN ch.credit_score >= 700 THEN 'Good (700-749)'
        WHEN ch.credit_score >= 650 THEN 'Fair (650-699)'
        WHEN ch.credit_score >= 600 THEN 'Poor (600-649)'
        ELSE 'Very Poor (<600)'
    END AS score_range,
    COUNT(*) AS total_applications,
    SUM(CASE WHEN lo.status = 'Default' THEN 1 ELSE 0 END) AS defaults,
    ROUND(SUM(CASE WHEN lo.status = 'Default' THEN 1 ELSE 0 END) * 100.0
        / COUNT(*), 2) AS default_rate_pct,
    ROUND(AVG(la.loan_amount), 2) AS avg_loan_amount
FROM loan_applications la
JOIN credit_history ch ON la.applicant_id = ch.applicant_id
JOIN loan_outcomes lo ON la.application_id = lo.application_id
GROUP BY score_range
ORDER BY default_rate_pct DESC;

Query 2: Loan Amount Distribution and Risk

Analyze how loan size correlates with default probability. Larger loans may carry higher risk depending on the borrower profile.

SELECT
    CASE
        WHEN la.loan_amount < 5000 THEN 'Under $5K'
        WHEN la.loan_amount < 15000 THEN '$5K - $15K'
        WHEN la.loan_amount < 30000 THEN '$15K - $30K'
        ELSE 'Over $30K'
    END AS loan_bucket,
    COUNT(*) AS num_loans,
    ROUND(AVG(la.loan_amount), 2) AS avg_amount,
    ROUND(AVG(la.interest_rate), 2) AS avg_rate,
    ROUND(SUM(CASE WHEN lo.status = 'Default' THEN 1 ELSE 0 END) * 100.0
        / COUNT(*), 2) AS default_rate_pct
FROM loan_applications la
JOIN loan_outcomes lo ON la.application_id = lo.application_id
GROUP BY loan_bucket
ORDER BY avg_amount;

Query 3: Multi-Factor Risk Segmentation

Combine credit score, debt-to-income ratio, and employment type to create granular risk segments. This identifies the riskiest applicant profiles.

SELECT
    CASE WHEN ch.credit_score >= 700 THEN 'High Score' ELSE 'Low Score' END AS score_tier,
    CASE WHEN e.debt_to_income <= 0.35 THEN 'Low DTI' ELSE 'High DTI' END AS dti_tier,
    e.employment_type,
    COUNT(*) AS applicants,
    ROUND(SUM(CASE WHEN lo.status = 'Default' THEN 1 ELSE 0 END) * 100.0
        / COUNT(*), 2) AS default_rate_pct,
    ROUND(AVG(la.loan_amount), 0) AS avg_loan
FROM loan_applications la
JOIN credit_history ch ON la.applicant_id = ch.applicant_id
JOIN employment e ON la.applicant_id = e.applicant_id
JOIN loan_outcomes lo ON la.application_id = lo.application_id
GROUP BY score_tier, dti_tier, e.employment_type
HAVING COUNT(*) >= 20
ORDER BY default_rate_pct DESC;

Python Analysis

Data Preparation and Feature Importance

Load the joined dataset, engineer features, and identify which variables most strongly predict default.

import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler, LabelEncoder

# Load data (joined from SQL)
df = pd.read_csv('credit_data.csv')

# Create target variable: 1 = Default, 0 = Not Default
df['is_default'] = (df['status'] == 'Default').astype(int)

# Feature engineering
df['loan_to_income'] = df['loan_amount'] / df['annual_income']
df['monthly_payment'] = df['loan_amount'] * (df['interest_rate']/100/12) \
    / (1 - (1 + df['interest_rate']/100/12) ** -df['term_months'])
df['payment_to_income'] = df['monthly_payment'] / (df['annual_income'] / 12)

# Encode categorical variables
le = LabelEncoder()
df['employment_encoded'] = le.fit_transform(df['employment_type'])
df['purpose_encoded'] = le.fit_transform(df['loan_purpose'])

features = ['credit_score', 'credit_utilization', 'num_late_payments',
            'debt_to_income', 'loan_to_income', 'payment_to_income',
            'annual_income', 'employment_length', 'credit_history_years',
            'employment_encoded', 'purpose_encoded']

X = df[features]
y = df['is_default']

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.25, random_state=42, stratify=y
)

scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

Logistic Regression Credit Scoring Model

Train a logistic regression model, which is the industry standard for credit scoring because its coefficients are directly interpretable as risk weights.

from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report, confusion_matrix

model = LogisticRegression(max_iter=1000, class_weight='balanced', random_state=42)
model.fit(X_train_scaled, y_train)

y_pred = model.predict(X_test_scaled)
y_prob = model.predict_proba(X_test_scaled)[:, 1]

print("Classification Report:")
print(classification_report(y_test, y_pred, target_names=['No Default', 'Default']))

# Feature importance (coefficient magnitudes)
importance = pd.DataFrame({
    'feature': features,
    'coefficient': model.coef_[0]
}).sort_values('coefficient', key=abs, ascending=False)

print("\nFeature Importance (by coefficient magnitude):")
print(importance.to_string(index=False))

Confusion Matrix Visualization

Visualize model performance. In credit risk, false negatives (approving loans that default) are more costly than false positives (rejecting good borrowers).

import matplotlib.pyplot as plt
import seaborn as sns

cm = confusion_matrix(y_test, y_pred)
plt.figure(figsize=(8, 6))
sns.heatmap(cm, annot=True, fmt='d', cmap='Blues',
            xticklabels=['No Default', 'Default'],
            yticklabels=['No Default', 'Default'])
plt.title('Credit Risk Model - Confusion Matrix')
plt.ylabel('Actual')
plt.xlabel('Predicted')
plt.tight_layout()
plt.savefig('confusion_matrix.png', dpi=150)
plt.show()

ROC Curve

The ROC curve shows the tradeoff between catching defaults (True Positive Rate) and incorrectly flagging good borrowers (False Positive Rate). An AUC above 0.75 is considered acceptable for credit models.

from sklearn.metrics import roc_curve, auc

fpr, tpr, thresholds = roc_curve(y_test, y_prob)
roc_auc = auc(fpr, tpr)

plt.figure(figsize=(8, 6))
plt.plot(fpr, tpr, color='darkorange', lw=2, label=f'ROC Curve (AUC = {roc_auc:.3f})')
plt.plot([0, 1], [0, 1], color='gray', lw=1, linestyle='--', label='Random Classifier')
plt.xlabel('False Positive Rate')
plt.ylabel('True Positive Rate')
plt.title('ROC Curve - Credit Default Prediction')
plt.legend(loc='lower right')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig('roc_curve.png', dpi=150)
plt.show()

# Find optimal threshold (maximizes Youden's J statistic)
j_scores = tpr - fpr
optimal_idx = np.argmax(j_scores)
optimal_threshold = thresholds[optimal_idx]
print(f"Optimal probability threshold: {optimal_threshold:.3f}")

Excel Analysis

Key Insights

Quiz