题解 | 分析客户逾期情况

WITH temp AS (
    SELECT 
        total.pay_ability AS pay_ability, 
        total.countall AS countall, 
        payre.count AS count
    FROM (
        SELECT 
            ct.pay_ability AS pay_ability, 
            COUNT(ct.pay_ability) AS countall
        FROM 
            loan_tb lt
        LEFT JOIN 
            customer_tb ct ON lt.customer_id = ct.customer_id
        GROUP BY 
            ct.pay_ability
    ) AS total
    LEFT JOIN (
        SELECT 
            ct.pay_ability AS pay_ability, 
            COUNT(ct.pay_ability) AS count
        FROM 
            loan_tb lt
        LEFT JOIN 
            customer_tb ct ON lt.customer_id = ct.customer_id
        WHERE 
            lt.overdue_days IS NOT NULL
        GROUP BY 
            ct.pay_ability
    ) AS payre ON total.pay_ability = payre.pay_ability
)
SELECT pay_ability, CONCAT(ROUND(IFNULL((count / NULLIF(countall, 0)) * 100, 0.0), 1), '%') AS overdue_ratio
FROM temp
ORDER BY overdue_ratio DESC