风控杂学

贷后指标监控

账龄分析(`vintage analysis`)

根据还款计划表按照账期计算 Vintage Legged DPD N+

WITH config AS (
		SELECT 3 dpd, TRUNC(SYSDATE - 1, 'DD') CURRENT_DATE FROM dual
)
, loan AS (
		SELECT DISTINCT C_EXT_ID
					, D_SETTLE_DATE
					, N_LOAN_AMOUNT
					, D_APPLICATION
		FROM DATAMART.DM_MID_LOAN_BASE
		WHERE C_INDUSTRY_ONE = '汽车'
				AND C_INDUSTRY_TWO = '汽车'
				AND C_FINANCE_PRODUCT_TYPE = '无卡-大自付'
				AND C_FINANCE_PRODUCT_ONE != '1'
)
, plan AS (
		SELECT plan.C_EXT_ID
						, loan.N_LOAN_AMOUNT
						, loan.D_APPLICATION
						, plan.D_LOAN_DATE
						, plan.D_PAY_DATE
						, plan.D_FINISH_DATE
						, LAG(plan.D_FINISH_DATE, 1, NULL) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_FINISH_DATE_LAG
						, LEAD(plan.D_FINISH_DATE, 1, NULL) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_FINISH_DATE_LEAD
						, LAG(plan.D_PAY_DATE, 1, NULL) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_PAY_DATE_LAG
						, LEAD(plan.D_PAY_DATE, 1, NULL) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_PAY_DATE_LEAD
						, FIRST_VALUE(plan.D_PAY_DATE) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_PAY_DATE_FIRST
						, LAST_VALUE(plan.D_PAY_DATE) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE) D_PAY_DATE_LAST
						, loan.D_SETTLE_DATE
						, plan.N_TENOR
						, ROUND(MONTHS_BETWEEN(plan.D_PAY_DATE, FIRST_VALUE(plan.D_PAY_DATE) OVER (PARTITION BY plan.C_EXT_ID ORDER BY plan.D_PAY_DATE)), 0) + 1 N_CURR_TENOR_CACL
						, plan.N_CURR_TENOR
						, plan.N_ALL_PAY_AMOUNT
						, SUM(plan.N_ALL_PAY_AMOUNT) OVER (PARTITION BY plan.C_EXT_ID ORDER BY NVL(plan.D_FINISH_DATE, plan.D_PAY_DATE) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) N_ALL_PAY_AMOUNT_FUTURE 
						, plan.N_ALL_PAY_CORPUS
						, SUM(plan.N_ALL_PAY_CORPUS) OVER (PARTITION BY plan.C_EXT_ID ORDER BY NVL(plan.D_FINISH_DATE, plan.D_PAY_DATE) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) N_ALL_PAY_CORPUS_FUTURE 
						, plan.N_ALL_PAID_AMOUNT
						, SUM(plan.N_ALL_PAID_AMOUNT) OVER (PARTITION BY plan.C_EXT_ID ORDER BY NVL(plan.D_FINISH_DATE, plan.D_PAY_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) N_ALL_PAID_AMOUNT_PAST
						, plan.N_ALL_PAID_CORPUS
						, SUM(plan.N_ALL_PAID_CORPUS) OVER (PARTITION BY plan.C_EXT_ID ORDER BY NVL(plan.D_FINISH_DATE, plan.D_PAY_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) N_ALL_PAID_CORPUS_PAST
						, loan.N_LOAN_AMOUNT - SUM(plan.N_ALL_PAID_CORPUS) OVER (PARTITION BY plan.C_EXT_ID ORDER BY NVL(plan.D_FINISH_DATE, plan.D_PAY_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) N_LOAN_AMOUNT_BALANCE
						, plan.N_NEED_PAY
		-- FROM DATAMART.DM_MID_REPAY_PLAN_EXTEND plan
		FROM DATAMART.DM_MID_REPAY_PLAN plan
		INNER JOIN loan ON plan.C_EXT_ID = loan.C_EXT_ID
		WHERE plan.N_LOAN_STATUS != 3
		ORDER BY plan.C_EXT_ID, plan.D_PAY_DATE ASC
)
, vintage AS (
		SELECT N_CURR_TENOR_CACL
					, TO_CHAR(LAST_DAY(D_LOAN_DATE), 'YYYY-MM') D_LOAN_MONTH
-- 					, SUM(N_LOAN_AMOUNT) N_LOAN_AMOUNT_TOTAL
-- 					, SUM(CASE WHEN config.CURRENT_DATE - NVL(D_FINISH_DATE, D_PAY_DATE) > config.dpd THEN N_LOAN_AMOUNT ELSE NULL END) N_LOAN_AMOUNT_TOTAL_LAGGED
-- 					, SUM(CASE WHEN NVL(D_FINISH_DATE, config.CURRENT_DATE) - D_PAY_DATE > config.dpd THEN N_ALL_PAY_CORPUS_FUTURE ELSE NULL END) N_OVERDUE_CORPUS_BALANCE
					, SUM(CASE WHEN NVL(D_FINISH_DATE, config.CURRENT_DATE) - D_PAY_DATE > config.dpd THEN N_ALL_PAY_CORPUS_FUTURE ELSE NULL END) / SUM(CASE WHEN config.CURRENT_DATE - NVL(D_FINISH_DATE, D_PAY_DATE) > config.dpd THEN N_LOAN_AMOUNT ELSE NULL END) OVERDUE_RATE_LAGGED
-- 					, SUM(CASE WHEN NVL(D_FINISH_DATE, config.CURRENT_DATE) - D_PAY_DATE > config.dpd THEN N_ALL_PAY_CORPUS_FUTURE - N_ALL_PAID_CORPUS_PAST ELSE NULL END) / SUM(N_LOAN_AMOUNT) OVERDUE_RATE
		FROM plan, config
		GROUP BY N_CURR_TENOR_CACL, LAST_DAY(D_LOAN_DATE)
		ORDER BY D_LOAN_MONTH, N_CURR_TENOR_CACL
)
-- SELECT * FROM vintage WHERE N_CURR_TENOR_CACL = 1 AND D_LOAN_MONTH = '2023-02'
SELECT D_LOAN_MONTH, TERM1, TERM2, TERM3, TERM4, TERM5, TERM6, TERM7, TERM8, TERM9, TERM10, TERM11, TERM12
FROM vintage
pivot (
		max(ROUND(OVERDUE_RATE_LAGGED, 4)) FOR N_CURR_TENOR_CACL IN (1 TERM1, 2 TERM2, 3 TERM3, 4 TERM4, 5 TERM5, 6 TERM6, 7 TERM7, 8 TERM8, 9 TERM9, 10 TERM10, 11 TERM11, 12 TERM12))
ORDER BY D_LOAN_MONTH