题解 | 分析客户逾期情况

SELECT
    t2.pay_ability,
    CONCAT(ROUND(
        COUNT(
            DISTINCT IF (t1.if_yuqi > 0, t1.customer_id, NULL)
        ) * 100.0 / COUNT(DISTINCT t1.customer_id),1),'%') AS overdue_ratio
FROM
    (
        SELECT
            customer_id,
            SUM(IF (overdue_days > 0, 1, 0)) AS if_yuqi
        FROM
            loan_tb
        GROUP BY
            customer_id
    ) t1
    LEFT JOIN (
        SELECT DISTINCT
            customer_id,
            pay_ability
        FROM
            customer_tb
    ) t2 ON t1.customer_id = t2.customer_id
GROUP BY
    t2.pay_ability
ORDER BY 2 DESC