题解 | 分析客户逾期情况

with 
lsb as
(select ct.pay_ability, count(*) as total
from loan_tb lt
join customer_tb ct 
on lt.customer_id = ct.customer_id
group by ct.pay_ability),
lsb1 as
(select ct.pay_ability, count(*) as yqtotal
from loan_tb lt
join customer_tb ct 
on lt.customer_id = ct.customer_id
where lt.overdue_days != 'NULL'
group by ct.pay_ability)
select ls.pay_ability,
concat(round((case when yqtotal/total is NULL then 0 else yqtotal/total end)*100,1),'%') 
as overdue_ratio
from lsb ls
left join lsb1 l1 
on ls.pay_ability = l1.pay_ability
order by overdue_ratio desc