题解 | 分析客户逾期情况

WITH customer_overdue AS (
    SELECT 
        c.customer_id,
        c.pay_ability,
        MAX(CASE WHEN l.overdue_days IS NOT NULL THEN 1 ELSE 0 END) AS has_overdue
    FROM customer_tb c
    LEFT JOIN loan_tb l ON c.customer_id = l.customer_id
    GROUP BY c.customer_id, c.pay_ability
)
SELECT 
    pay_ability,
    CONCAT(ROUND(SUM(has_overdue) * 100.0 / COUNT(*), 1), '%') AS overdue_ratio
FROM customer_overdue
GROUP BY pay_ability
ORDER BY overdue_ratio DESC;