Banking Dashboard Design

1-- 1. KPI Summary cho dashboard
2SELECT 
3  -- Total Portfolio
4  COUNT(*) AS total_approved_loans,
5  SUM(loan_amount) AS total_portfolio_amount,
6  -- Quality
7  ROUND(AVG(default_flag) * 100, 2) AS npl_rate_pct,
8  -- Approval
9  (SELECT ROUND(AVG(CASE WHEN status = 'Approved' THEN 1.0 ELSE 0 END) * 100, 1)
10   FROM fact_loan_application) AS approval_rate,
11  -- Average metrics
12  ROUND(AVG(loan_amount), 0) AS avg_loan_size,
13  ROUND(AVG(credit_score), 0) AS avg_credit_score,
14  ROUND(AVG(dti_ratio), 1) AS avg_dti_ratio,
15  -- Unique customers
16  COUNT(DISTINCT customer_id) AS unique_borrowers
17FROM fact_loan_application
18WHERE status = 'Approved';

1-- 2. Monthly trend cho line charts
2SELECT 
3  FORMAT(application_date, 'yyyy-MM') AS month,
4  COUNT(*) AS loan_count,
5  SUM(loan_amount) AS loan_amount,
6  ROUND(AVG(default_flag) * 100, 2) AS default_rate,
7  ROUND(AVG(credit_score), 0) AS avg_score,
8  COUNT(DISTINCT customer_id) AS new_borrowers
9FROM fact_loan_application
10WHERE status = 'Approved'
11GROUP BY FORMAT(application_date, 'yyyy-MM')
12ORDER BY month;

1-- 3. Product mix cho treemap/donut chart
2SELECT 
3  p.product_type,
4  p.product_name,
5  COUNT(*) AS loan_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_portfolio,
8  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate,
9  ROUND(AVG(l.interest_rate), 2) AS avg_rate
10FROM fact_loan_application l
11JOIN dim_product p ON l.product_id = p.product_id
12WHERE l.status = 'Approved'
13GROUP BY p.product_type, p.product_name
14ORDER BY total_amount DESC;

1-- 4. Regional data cho map & drill-down
2SELECT 
3  b.region,
4  b.city,
5  COUNT(DISTINCT b.branch_id) AS branches,
6  COUNT(l.application_id) AS total_loans,
7  SUM(l.loan_amount) AS total_amount,
8  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate,
9  COUNT(DISTINCT l.customer_id) AS unique_customers
10FROM dim_branch b
11JOIN fact_loan_application l ON b.branch_id = l.branch_id
12WHERE l.status = 'Approved'
13GROUP BY b.region, b.city
14ORDER BY b.region, total_amount DESC;