题解 | 分析客户逾期情况

select
    pay_ability,
    concat(round(sum_od/count_od*100,1),'%') as overdue_ratio
from
(
select
    distinct pay_ability,
    sum(od) over(partition by pay_ability) as sum_od,
    count(*) over(partition by pay_ability) as count_od
from
(
    select
        pay_ability,
        lt.customer_id,
        case
        when overdue_days is null
        then 0
        else 1
        end od
    from
        loan_tb as lt
        left join customer_tb as ct
        on lt.customer_id=ct.customer_id
    group by pay_ability,lt.customer_id,overdue_days
    order by pay_ability) a
) b
order by overdue_ratio desc
;