题解 | #分析客户逾期情况#

-- 使用 WITH 子句创建一个临时表，计算每个客户的逾期天数和总贷款次数
WITH customer_loan_stats AS (
    SELECT
        c.pay_ability,  -- 客户的支付能力
        COUNT(1) AS total_loans,  -- 每个客户的总贷款次数
        SUM(CASE WHEN l.overdue_days > 0 THEN 1 ELSE 0 END) AS overdue_loans  -- 每个客户的逾期贷款次数
    FROM
        loan_tb l
    LEFT JOIN
        customer_tb c ON l.customer_id = c.customer_id  -- 左连接客户表
    GROUP BY
        c.pay_ability  -- 按支付能力分组
)

-- 从临时表中选择数据，计算逾期比例并按逾期比例降序排列
SELECT
    pay_ability,  -- 支付能力
    CONCAT(ROUND((overdue_loans / total_loans) * 100, 1), '%') AS overdue_ratio  -- 逾期比例
FROM
    customer_loan_stats
ORDER BY
    overdue_ratio DESC;  -- 按逾期比例降序排列