Capstone: VNCredit Bank Analysis

1-- Capstone Q1: Executive KPIs Summary
2SELECT 
3  '=== VNCREDIT BANK EXECUTIVE SUMMARY ===' AS report_section,
4  COUNT(*) AS total_applications,
5  SUM(CASE WHEN status = 'Approved' THEN 1 ELSE 0 END) AS approved_loans,
6  ROUND(AVG(CASE WHEN status = 'Approved' THEN 1.0 ELSE 0 END) * 100, 1) AS approval_rate,
7  SUM(CASE WHEN status = 'Approved' THEN loan_amount ELSE 0 END) AS total_portfolio,
8  ROUND(AVG(CASE WHEN status = 'Approved' THEN loan_amount END), 0) AS avg_loan_size,
9  ROUND(AVG(CASE WHEN status = 'Approved' THEN credit_score END), 0) AS avg_credit_score,
10  ROUND(AVG(CASE WHEN status = 'Approved' THEN default_flag END) * 100, 2) AS default_rate,
11  COUNT(DISTINCT customer_id) AS unique_customers,
12  COUNT(DISTINCT branch_id) AS active_branches
13FROM fact_loan_application;

1-- Capstone Q2: Quarterly Growth Analysis
2WITH quarterly AS (
3  SELECT 
4      DATEPART(YEAR, application_date) AS yr,
5      DATEPART(QUARTER, application_date) AS qtr,
6      COUNT(*) AS loan_count,
7      SUM(loan_amount) AS loan_amount,
8      ROUND(AVG(default_flag) * 100, 2) AS default_rate
9  FROM fact_loan_application
10  WHERE status = 'Approved'
11  GROUP BY DATEPART(YEAR, application_date), DATEPART(QUARTER, application_date)
12)
13SELECT 
14  CONCAT('Q', qtr, '/', yr) AS quarter,
15  loan_count,
16  loan_amount,
17  default_rate,
18  ROUND(
19      (loan_amount - LAG(loan_amount) OVER(ORDER BY yr, qtr)) * 100.0 
20      / LAG(loan_amount) OVER(ORDER BY yr, qtr), 1
21  ) AS growth_pct_qoq
22FROM quarterly
23ORDER BY yr, qtr;

1-- Capstone Q3: Credit Quality Matrix (Score x DTI)
2SELECT 
3  CASE 
4      WHEN credit_score >= 750 THEN 'Excellent (750+)'
5      WHEN credit_score >= 700 THEN 'Good (700-749)'
6      WHEN credit_score >= 650 THEN 'Fair (650-699)'
7      WHEN credit_score >= 600 THEN 'Poor (600-649)'
8      ELSE 'Very Poor (<600)'
9  END AS score_band,
10  CASE 
11      WHEN dti_ratio <= 0.3 THEN 'Low DTI (≤30%)'
12      WHEN dti_ratio <= 0.5 THEN 'Medium DTI (30-50%)'
13      ELSE 'High DTI (>50%)'
14  END AS dti_band,
15  COUNT(*) AS loan_count,
16  ROUND(AVG(default_flag) * 100, 2) AS default_rate,
17  ROUND(AVG(loan_amount), 0) AS avg_loan,
18  SUM(loan_amount) AS total_exposure
19FROM fact_loan_application
20WHERE status = 'Approved'
21GROUP BY 
22  CASE 
23      WHEN credit_score >= 750 THEN 'Excellent (750+)'
24      WHEN credit_score >= 700 THEN 'Good (700-749)'
25      WHEN credit_score >= 650 THEN 'Fair (650-699)'
26      WHEN credit_score >= 600 THEN 'Poor (600-649)'
27      ELSE 'Very Poor (<600)'
28  END,
29  CASE 
30      WHEN dti_ratio <= 0.3 THEN 'Low DTI (≤30%)'
31      WHEN dti_ratio <= 0.5 THEN 'Medium DTI (30-50%)'
32      ELSE 'High DTI (>50%)'
33  END
34ORDER BY score_band, dti_band;

1-- Capstone Q4: Loan Vintage Analysis
2WITH vintage AS (
3  SELECT 
4      FORMAT(l.application_date, 'yyyy-MM') AS vintage_month,
5      l.application_id,
6      l.loan_amount,
7      l.default_flag,
8      DATEDIFF(MONTH, l.application_date, p.payment_date) AS months_on_book,
9      p.days_past_due
10  FROM fact_loan_application l
11  JOIN fact_loan_payment p ON l.application_id = p.application_id
12  WHERE l.status = 'Approved'
13)
14SELECT 
15  vintage_month,
16  months_on_book AS mob,
17  COUNT(DISTINCT application_id) AS active_loans,
18  ROUND(AVG(CASE WHEN days_past_due > 90 THEN 1.0 ELSE 0 END) * 100, 2) AS npl_rate
19FROM vintage
20WHERE months_on_book BETWEEN 1 AND 24
21GROUP BY vintage_month, months_on_book
22ORDER BY vintage_month, months_on_book;

1-- Capstone Q5: Customer Segmentation Summary
2WITH customer_profile AS (
3  SELECT 
4      c.customer_id,
5      c.age,
6      c.income_level,
7      c.occupation,
8      -- Loan metrics
9      COUNT(DISTINCT l.application_id) AS total_loans,
10      SUM(l.loan_amount) AS total_borrowed,
11      AVG(l.default_flag) AS default_rate,
12      -- Transaction metrics
13      COUNT(DISTINCT t.transaction_id) AS total_txns,
14      SUM(t.amount) AS total_txn_amount,
15      -- Segment
16      CASE 
17          WHEN c.income_level IN ('Very High') THEN 'Priority'
18          WHEN c.income_level IN ('High') THEN 'Affluent'
19          ELSE 'Mass'
20      END AS segment
21  FROM dim_customer c
22  LEFT JOIN fact_loan_application l ON c.customer_id = l.customer_id AND l.status = 'Approved'
23  LEFT JOIN fact_transaction t ON c.customer_id = t.customer_id
24  GROUP BY c.customer_id, c.age, c.income_level, c.occupation
25)
26SELECT 
27  segment,
28  COUNT(*) AS customers,
29  ROUND(AVG(total_loans), 1) AS avg_loans,
30  ROUND(AVG(total_borrowed), 0) AS avg_borrowed,
31  ROUND(AVG(default_rate) * 100, 2) AS avg_default_rate,
32  ROUND(AVG(total_txns), 0) AS avg_txns,
33  ROUND(AVG(total_txn_amount), 0) AS avg_txn_value
34FROM customer_profile
35GROUP BY segment
36ORDER BY avg_txn_value DESC;

1-- Capstone Q6: Branch Performance Scorecard
2WITH branch_metrics AS (
3  SELECT 
4      b.branch_id,
5      b.branch_name,
6      b.region,
7      b.city,
8      b.branch_type,
9      COUNT(l.application_id) AS total_loans,
10      SUM(l.loan_amount) AS total_amount,
11      ROUND(AVG(l.default_flag) * 100, 2) AS default_rate,
12      COUNT(DISTINCT l.customer_id) AS unique_customers,
13      COUNT(DISTINCT t.transaction_id) AS total_txns,
14      SUM(t.amount) AS txn_volume
15  FROM dim_branch b
16  LEFT JOIN fact_loan_application l ON b.branch_id = l.branch_id AND l.status = 'Approved'
17  LEFT JOIN fact_transaction t ON b.branch_id = t.branch_id
18  GROUP BY b.branch_id, b.branch_name, b.region, b.city, b.branch_type
19),
20scored AS (
21  SELECT *,
22      NTILE(5) OVER(ORDER BY total_amount DESC) AS volume_tier,
23      NTILE(5) OVER(ORDER BY default_rate ASC) AS quality_tier,
24      NTILE(5) OVER(ORDER BY unique_customers DESC) AS customer_tier,
25      ROUND(
26          NTILE(5) OVER(ORDER BY total_amount DESC) * 0.4 +
27          NTILE(5) OVER(ORDER BY default_rate ASC) * 0.35 +
28          NTILE(5) OVER(ORDER BY unique_customers DESC) * 0.25
29      , 2) AS composite_score
30  FROM branch_metrics
31)
32SELECT 
33  branch_name, region, city, branch_type,
34  total_loans, total_amount, default_rate,
35  unique_customers, composite_score,
36  CASE 
37      WHEN composite_score >= 4.0 THEN '⭐ Star'
38      WHEN composite_score >= 3.0 THEN '✅ Good'
39      WHEN composite_score >= 2.0 THEN '⚠️ Average'
40      ELSE '🔴 Underperform'
41  END AS performance_tier
42FROM scored
43ORDER BY composite_score DESC;