题解 | 分析客户逾期情况

-- 第一步：连表，获取每个客户的还款能力级别和逾期天数
with t1 as (
    select 
        l.customer_id,
        l.overdue_days,
        c.pay_ability
    from loan_tb l
    left join customer_tb c on l.customer_id = c.customer_id
),

-- 第二步：标记是否违约（逾期天数 > 0 即为违约）
t2 as (
    select 
        customer_id,
        pay_ability,
        case when overdue_days > 0 then 1 else 0 end as is_overdue  -- 1表示违约，0表示未违约
    from t1
),

-- 第三步：按还款能力级别聚类分析
t3 as (
    select 
        pay_ability,
        count(customer_id) as total_customers,           -- 该级别总客户数
        sum(is_overdue) as overdue_customers              -- 该级别违约客户数
    from t2
    group by pay_ability
)

-- 第四步：计算违约率并格式化输出
select 
    pay_ability,
    concat(
        round(overdue_customers * 100.0 / total_customers, 1), 
        '%'
    ) as overdue_ratio
from t3
order by overdue_ratio desc;