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

with t1 as
(select pay_ability,count(customer_id) as all_user_cnt from customer_tb
group by pay_ability)

,t2 as
(select a.pay_ability,a.customer_id,b.overdue_days 
from customer_tb a join loan_tb b
on a.customer_id=b.customer_id)

,t3 as
(select t2.pay_ability,count(customer_id) as overdue_user_cnt
from t2
where t2.overdue_days is not null
group by pay_ability)

select 
    t1.pay_ability, 
    coalesce(concat(round(t3.overdue_user_cnt/t1.all_user_cnt*100,1),'%'),'0.0%') as overdue_ratio
from t1 left join t3
on t1.pay_ability=t3.pay_ability
where t1.all_user_cnt <>0
order by overdue_ratio desc

我的难点在于转换成百分比，以及空值转成0.0%。一个错在round位置放错了，第二个错在0.0%没有加引号