Regulatory & Compliance Reporting

1-- Phân loại nợ theo 5 nhóm NHNN
2WITH loan_classification AS (
3  SELECT 
4      l.application_id,
5      l.customer_id,
6      l.loan_amount,
7      l.product_id,
8      -- Tính DPD từ payment data
9      COALESCE(
10          MAX(p.days_past_due), 0
11      ) AS max_dpd,
12      -- Phân loại nhóm nợ
13      CASE 
14          WHEN COALESCE(MAX(p.days_past_due), 0) = 0 THEN 'Group 1'
15          WHEN MAX(p.days_past_due) <= 90 THEN 'Group 2'
16          WHEN MAX(p.days_past_due) <= 180 THEN 'Group 3'
17          WHEN MAX(p.days_past_due) <= 360 THEN 'Group 4'
18          ELSE 'Group 5'
19      END AS nhom_no,
20      -- Tỷ lệ trích lập
21      CASE 
22          WHEN COALESCE(MAX(p.days_past_due), 0) = 0 THEN 0.00
23          WHEN MAX(p.days_past_due) <= 90 THEN 0.05
24          WHEN MAX(p.days_past_due) <= 180 THEN 0.20
25          WHEN MAX(p.days_past_due) <= 360 THEN 0.50
26          ELSE 1.00
27      END AS provision_rate
28  FROM fact_loan_application l
29  LEFT JOIN fact_loan_payment p ON l.application_id = p.application_id
30  WHERE l.status = 'Approved'
31  GROUP BY l.application_id, l.customer_id, l.loan_amount, l.product_id
32)
33SELECT 
34  nhom_no,
35  COUNT(*) AS so_khoan_vay,
36  SUM(loan_amount) AS tong_du_no,
37  ROUND(SUM(loan_amount) * 100.0 / SUM(SUM(loan_amount)) OVER(), 2) AS pct_portfolio,
38  ROUND(AVG(provision_rate) * 100, 0) AS ty_le_trich_lap,
39  SUM(loan_amount * provision_rate) AS du_phong_can_trich
40FROM loan_classification
41GROUP BY nhom_no
42ORDER BY nhom_no;

1-- NPL = (Nhóm 3 + Nhóm 4 + Nhóm 5) / Tổng dư nợ
2WITH classified_loans AS (
3  SELECT 
4      l.application_id,
5      l.loan_amount,
6      CASE 
7          WHEN COALESCE(MAX(p.days_past_due), 0) > 90 
8          THEN 1 ELSE 0 
9      END AS is_npl
10  FROM fact_loan_application l
11  LEFT JOIN fact_loan_payment p ON l.application_id = p.application_id
12  WHERE l.status = 'Approved'
13  GROUP BY l.application_id, l.loan_amount
14)
15SELECT 
16  SUM(loan_amount) AS total_outstanding,
17  SUM(CASE WHEN is_npl = 1 THEN loan_amount ELSE 0 END) AS npl_outstanding,
18  ROUND(
19      SUM(CASE WHEN is_npl = 1 THEN loan_amount ELSE 0 END) * 100.0 
20      / SUM(loan_amount), 2
21  ) AS npl_ratio_pct
22FROM classified_loans;

1-- Báo cáo trích lập dự phòng theo sản phẩm
2WITH loan_provision AS (
3  SELECT 
4      pr.product_type,
5      pr.product_name,
6      l.loan_amount,
7      CASE 
8          WHEN COALESCE(MAX(p.days_past_due), 0) = 0 THEN 0.00
9          WHEN MAX(p.days_past_due) <= 90 THEN 0.05
10          WHEN MAX(p.days_past_due) <= 180 THEN 0.20
11          WHEN MAX(p.days_past_due) <= 360 THEN 0.50
12          ELSE 1.00
13      END AS provision_rate
14  FROM fact_loan_application l
15  JOIN dim_product pr ON l.product_id = pr.product_id
16  LEFT JOIN fact_loan_payment p ON l.application_id = p.application_id
17  WHERE l.status = 'Approved'
18  GROUP BY pr.product_type, pr.product_name, l.application_id, l.loan_amount
19)
20SELECT 
21  product_type,
22  product_name,
23  COUNT(*) AS total_loans,
24  SUM(loan_amount) AS total_outstanding,
25  SUM(loan_amount * provision_rate) AS required_provision,
26  ROUND(
27      SUM(loan_amount * provision_rate) * 100.0 / SUM(loan_amount), 2
28  ) AS provision_coverage_pct
29FROM loan_provision
30GROUP BY product_type, product_name
31ORDER BY required_provision DESC;