Branch Performance

1-- 1. Branch distribution
2SELECT 
3  b.region,
4  b.branch_type,
5  COUNT(*) AS branch_count,
6  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
7FROM dim_branch b
8GROUP BY b.region, b.branch_type
9ORDER BY b.region, branch_count DESC;

1-- 2. Loan performance theo region
2SELECT 
3  b.region,
4  COUNT(DISTINCT b.branch_id) AS branches,
5  COUNT(l.application_id) AS total_applications,
6  ROUND(COUNT(l.application_id) * 1.0 / COUNT(DISTINCT b.branch_id), 0) AS apps_per_branch,
7  SUM(l.loan_amount) AS total_loan_amount,
8  ROUND(SUM(l.loan_amount) / COUNT(DISTINCT b.branch_id), 0) AS amount_per_branch,
9  ROUND(AVG(CASE WHEN l.status = 'Approved' THEN 1.0 ELSE 0 END) * 100, 1) AS approval_rate,
10  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate
11FROM dim_branch b
12JOIN fact_loan_application l ON b.branch_id = l.branch_id
13GROUP BY b.region
14ORDER BY total_loan_amount DESC;

1-- 3. Top 10 branches theo loan volume
2SELECT TOP 10
3  b.branch_id,
4  b.branch_name,
5  b.region,
6  b.city,
7  b.branch_type,
8  COUNT(l.application_id) AS total_loans,
9  SUM(l.loan_amount) AS total_amount,
10  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate
11FROM dim_branch b
12JOIN fact_loan_application l ON b.branch_id = l.branch_id
13WHERE l.status = 'Approved'
14GROUP BY b.branch_id, b.branch_name, b.region, b.city, b.branch_type
15ORDER BY total_amount DESC;

1-- 4. Bottom 10 branches — under-performers
2SELECT TOP 10
3  b.branch_id,
4  b.branch_name,
5  b.region,
6  b.city,
7  COUNT(l.application_id) AS total_loans,
8  SUM(l.loan_amount) AS total_amount,
9  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate
10FROM dim_branch b
11JOIN fact_loan_application l ON b.branch_id = l.branch_id
12WHERE l.status = 'Approved'
13GROUP BY b.branch_id, b.branch_name, b.region, b.city
14ORDER BY total_amount ASC;

1-- 5. Branch 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      -- Loan metrics
10      COUNT(l.application_id) AS total_loans,
11      SUM(l.loan_amount) AS total_amount,
12      ROUND(AVG(CASE WHEN l.status = 'Approved' THEN 1.0 ELSE 0 END) * 100, 1) AS approval_rate,
13      ROUND(AVG(l.default_flag) * 100, 2) AS default_rate,
14      -- Product mix
15      COUNT(DISTINCT l.product_id) AS product_diversity,
16      -- Customer mix
17      COUNT(DISTINCT l.customer_id) AS unique_customers
18  FROM dim_branch b
19  LEFT JOIN fact_loan_application l ON b.branch_id = l.branch_id
20  GROUP BY b.branch_id, b.branch_name, b.region, b.city, b.branch_type
21),
22ranked AS (
23  SELECT 
24      *,
25      -- Ranking
26      RANK() OVER (ORDER BY total_amount DESC) AS rank_amount,
27      RANK() OVER (ORDER BY default_rate ASC) AS rank_quality,
28      RANK() OVER (ORDER BY unique_customers DESC) AS rank_customers,
29      -- Percentiles within region
30      PERCENT_RANK() OVER (PARTITION BY region ORDER BY total_amount) AS pctile_amount_region
31  FROM branch_metrics
32)
33SELECT 
34  branch_id,
35  branch_name,
36  region,
37  city,
38  branch_type,
39  total_loans,
40  total_amount,
41  approval_rate,
42  default_rate,
43  unique_customers,
44  rank_amount,
45  rank_quality,
46  rank_customers,
47  -- Overall score (simple weighted average of ranks)
48  ROUND(
49      (rank_amount * 0.4 + rank_quality * 0.3 + rank_customers * 0.3) 
50  , 1) AS composite_score,
51  CASE 
52      WHEN pctile_amount_region >= 0.75 THEN 'Top Performer'
53      WHEN pctile_amount_region >= 0.25 THEN 'Average'
54      ELSE 'Under-performer'
55  END AS performance_tier
56FROM ranked
57ORDER BY composite_score ASC;

1-- 6. Performance theo branch type
2SELECT 
3  b.branch_type,
4  COUNT(DISTINCT b.branch_id) AS branches,
5  ROUND(AVG(loan_count), 0) AS avg_loans_per_branch,
6  ROUND(AVG(loan_amount), 0) AS avg_amount_per_branch,
7  ROUND(AVG(default_rate), 2) AS avg_default_rate
8FROM dim_branch b
9JOIN (
10  SELECT 
11      branch_id,
12      COUNT(*) AS loan_count,
13      SUM(loan_amount) AS loan_amount,
14      AVG(default_flag) * 100 AS default_rate
15  FROM fact_loan_application
16  WHERE status = 'Approved'
17  GROUP BY branch_id
18) stats ON b.branch_id = stats.branch_id
19GROUP BY b.branch_type
20ORDER BY avg_amount_per_branch DESC;