题解 | 分析客户逾期情况--直观方法：一个一个捋

# 组合一个大表，包含所有需要的数据
# id---层级--逾期天数--是否逾期（是1否0）
with a as(
    select distinct ct.customer_id,pay_ability,overdue_days,if(overdue_days is not Null,1,0) as is_overdue
    from customer_tb ct left join loan_tb lt
    on ct.customer_id=lt.customer_id
)
,b as( -- 层级--总人数
    select pay_ability,count(a.customer_id) as ab_count
    from a
    group by pay_ability
)
,c as( -- 层级--逾期人数
    select pay_ability, sum(is_overdue) as ab_overdue_count
    from a
    group by pay_ability
)
,d as(-- 连接b,c
    select b.pay_ability,concat(format(ab_overdue_count/ab_count*100,1),'%') as overdue_ratio
    from b join c
    on b.pay_ability=c.pay_ability
    group by b.pay_ability
    order by overdue_ratio desc
)
select * from d

一个表一个表地捋，对我来说最清楚