题解 | 分析客户逾期情况

SELECT 
    t2.pay_ability,
    CONCAT(
        ROUND(COALESCE(t1.overdue_cnt, 0) * 100.0 / t2.total_cnt, 1), 
        '%'
    ) AS overdue_ratio
FROM (
    -- 分母：所有还款能力的总贷款数（或客户数）
    SELECT 
        ct.pay_ability,
        COUNT(*) AS total_cnt
    FROM loan_tb lt
    INNER JOIN customer_tb ct ON lt.customer_id = ct.customer_id
    GROUP BY ct.pay_ability
) t2
LEFT JOIN (
    -- 分子：有逾期的（overdue_days > 0）
    SELECT 
        ct.pay_ability,
        COUNT(*) AS overdue_cnt
    FROM loan_tb lt
    INNER JOIN customer_tb ct ON lt.customer_id = ct.customer_id
    WHERE lt.overdue_days > 0
    GROUP BY ct.pay_ability
) t1 ON t2.pay_ability = t1.pay_ability
ORDER BY 
    COALESCE(t1.overdue_cnt, 0) * 100.0 / t2.total_cnt DESC;