题解 | 分析客户逾期情况

--先用一个case when给是否逾期打上标记，逾期记为1，非逾期记为0
with customer_info as (
    select agreement_id,
           loan_tb.customer_id,
           loan_amount,
           pay_amount,
           overdue_days,
           pay_ability,
           case when overdue_days <>'null' then 1
                when overdue_days ='null' then 0
                else 0
                end as 是否逾期
    from loan_tb
    left join customer_tb
    on loan_tb.customer_id = customer_tb.customer_id
)
--用等级聚合，逾期率=逾期数加起来/总用户数
select pay_ability,
       CONCAT(ROUND(SUM(是否逾期) / COUNT(customer_id) * 100, 1), '%') AS overdue_ratio
       from customer_info
       group by pay_ability
       order by overdue_ratio desc