Loan Portfolio Analysis

1-- 1. Vintage Analysis — Default Rate theo quý giải ngân
2WITH loan_vintage AS (
3  SELECT 
4      application_id,
5      CONCAT(YEAR(application_date), '-Q', DATEPART(QUARTER, application_date)) AS vintage,
6      application_date,
7      default_flag,
8      loan_amount
9  FROM fact_loan_application
10  WHERE status = 'Approved'
11)
12SELECT 
13  vintage,
14  COUNT(*) AS total_loans,
15  SUM(loan_amount) AS total_amount,
16  SUM(default_flag) AS defaults,
17  ROUND(AVG(default_flag) * 100, 2) AS default_rate_pct,
18  ROUND(AVG(loan_amount), 0) AS avg_loan_size
19FROM loan_vintage
20GROUP BY vintage
21ORDER BY vintage;

1-- 2. Vintage curve — theo dõi default theo MOB
2WITH loan_mob AS (
3  SELECT 
4      l.application_id,
5      CONCAT(YEAR(l.application_date), '-Q', DATEPART(QUARTER, l.application_date)) AS vintage,
6      DATEDIFF(MONTH, l.application_date, p.payment_date) AS mob,
7      CASE WHEN p.days_past_due > 90 THEN 1 ELSE 0 END AS is_delinquent
8  FROM fact_loan_application l
9  JOIN fact_loan_payment p ON l.application_id = p.application_id
10  WHERE l.status = 'Approved'
11)
12SELECT 
13  vintage,
14  mob,
15  COUNT(DISTINCT application_id) AS active_loans,
16  SUM(is_delinquent) AS delinquent_count,
17  ROUND(AVG(CAST(is_delinquent AS FLOAT)) * 100, 2) AS delinquency_rate_pct
18FROM loan_mob
19WHERE mob BETWEEN 1 AND 24
20GROUP BY vintage, mob
21ORDER BY vintage, mob;

1-- 3. DPD Aging Distribution
2SELECT 
3  CASE 
4      WHEN p.days_past_due = 0 THEN 'Bucket 0 (Current)'
5      WHEN p.days_past_due <= 30 THEN 'Bucket X (1-30 DPD)'
6      WHEN p.days_past_due <= 60 THEN 'Bucket 1 (31-60 DPD)'
7      WHEN p.days_past_due <= 90 THEN 'Bucket 2 (61-90 DPD)'
8      ELSE 'Bucket 3+ (90+ DPD = NPL)'
9  END AS dpd_bucket,
10  COUNT(*) AS payment_count,
11  ROUND(AVG(p.amount_due), 0) AS avg_amount_due,
12  ROUND(AVG(p.amount_paid), 0) AS avg_amount_paid,
13  ROUND(SUM(p.amount_due - p.amount_paid), 0) AS total_shortfall
14FROM fact_loan_payment p
15GROUP BY 
16  CASE 
17      WHEN p.days_past_due = 0 THEN 'Bucket 0 (Current)'
18      WHEN p.days_past_due <= 30 THEN 'Bucket X (1-30 DPD)'
19      WHEN p.days_past_due <= 60 THEN 'Bucket 1 (31-60 DPD)'
20      WHEN p.days_past_due <= 90 THEN 'Bucket 2 (61-90 DPD)'
21      ELSE 'Bucket 3+ (90+ DPD = NPL)'
22  END
23ORDER BY dpd_bucket;

1-- 4. Roll Rate — % khoản vay xấu đi theo tháng
2-- (Simplified version using payment_status transitions)
3WITH monthly_status AS (
4  SELECT 
5      p.application_id,
6      FORMAT(p.payment_date, 'yyyy-MM') AS payment_month,
7      MAX(p.days_past_due) AS max_dpd,
8      CASE 
9          WHEN MAX(p.days_past_due) = 0 THEN 'Current'
10          WHEN MAX(p.days_past_due) <= 30 THEN 'DPD 1-30'
11          WHEN MAX(p.days_past_due) <= 60 THEN 'DPD 31-60'
12          WHEN MAX(p.days_past_due) <= 90 THEN 'DPD 61-90'
13          ELSE 'DPD 90+'
14      END AS dpd_status
15  FROM fact_loan_payment p
16  GROUP BY p.application_id, FORMAT(p.payment_date, 'yyyy-MM')
17)
18SELECT 
19  dpd_status,
20  COUNT(DISTINCT application_id) AS loan_count,
21  ROUND(COUNT(DISTINCT application_id) * 100.0 / 
22      SUM(COUNT(DISTINCT application_id)) OVER(), 2) AS pct_of_total
23FROM monthly_status
24GROUP BY dpd_status
25ORDER BY dpd_status;

1-- 5. Collection Efficiency tổng thể và theo tháng
2SELECT 
3  FORMAT(payment_date, 'yyyy-MM') AS payment_month,
4  COUNT(*) AS total_payments,
5  SUM(amount_due) AS total_due,
6  SUM(amount_paid) AS total_paid,
7  ROUND(SUM(amount_paid) * 100.0 / NULLIF(SUM(amount_due), 0), 2) AS cer_pct,
8  SUM(CASE WHEN payment_status = 'On-time' THEN 1 ELSE 0 END) AS on_time_count,
9  ROUND(AVG(CASE WHEN payment_status = 'On-time' THEN 1.0 ELSE 0 END) * 100, 1) AS on_time_pct
10FROM fact_loan_payment
11GROUP BY FORMAT(payment_date, 'yyyy-MM')
12ORDER BY payment_month;

1-- 6. Collection Efficiency theo loại sản phẩm
2SELECT 
3  pr.product_type,
4  COUNT(p.payment_id) AS total_payments,
5  ROUND(SUM(p.amount_paid) * 100.0 / NULLIF(SUM(p.amount_due), 0), 2) AS cer_pct,
6  ROUND(AVG(CASE WHEN p.payment_status = 'On-time' THEN 1.0 ELSE 0 END) * 100, 1) AS on_time_pct,
7  ROUND(AVG(p.days_past_due), 1) AS avg_dpd
8FROM fact_loan_payment p
9JOIN fact_loan_application l ON p.application_id = l.application_id
10JOIN dim_product pr ON l.product_id = pr.product_id
11GROUP BY pr.product_type
12ORDER BY cer_pct DESC;

1-- 7. Portfolio mix — cơ cấu danh mục theo product type
2SELECT 
3  pr.product_type,
4  COUNT(*) AS loan_count,
5  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct_count,
6  SUM(l.loan_amount) AS total_amount,
7  ROUND(SUM(l.loan_amount) * 100.0 / SUM(SUM(l.loan_amount)) OVER(), 1) AS pct_amount,
8  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate_pct
9FROM fact_loan_application l
10JOIN dim_product pr ON l.product_id = pr.product_id
11WHERE l.status = 'Approved'
12GROUP BY pr.product_type
13ORDER BY total_amount DESC;