题解 | 分析客户逾期情况

WITH
    customer_overdue AS (
        -- 计算每个客户是否有逾期记录（overdue_days > 0）
        SELECT
            customer_id,
            MAX(
                CASE
                    WHEN overdue_days > 0 THEN 1
                    ELSE 0
                END
            ) AS is_overdue
        FROM
            loan_tb
        GROUP BY
            customer_id
    )
SELECT
    c.pay_ability,
    CONCAT (
        ROUND(SUM(co.is_overdue) * 100.0 / COUNT(*), 1),
        '%'
    ) AS overdue_ratio
FROM
    customer_tb c
    LEFT JOIN customer_overdue co ON c.customer_id = co.customer_id
GROUP BY
    c.pay_ability
ORDER BY
    ROUND(SUM(co.is_overdue) * 100.0 / COUNT(*), 1) DESC;