题解 | 贷款情况

with t1 as(
    select 
        city,
        round(sum(loan_amount),2) total_loan_amount,
        round(sum(loan_amount)/count(distinct customer_id),2) average_loan_amount,
        count(distinct customer_id) total_customers
    from
        loan_applications s1 
    left join
        customers s2
    using(customer_id)
    left join
        loan_application_types s3
    using(application_id)
    left join
        loan_types s4
    using(loan_type_id)
    group by 1
)
,t2 as(
    select
        city,
        loan_type_id,loan_type_name,
        rank() over (partition by city order by count(1) desc,loan_type_id asc) rk

    from
        loan_applications s1 
    left join
        customers s2
    using(customer_id)
    left join
        loan_application_types s3
    using(application_id)
    left join
        loan_types s4
    using(loan_type_id)
    group by 1,2,3

)
,t3 as(
    select
        city,
        loan_type_name
    from
        t2
    where rk = 1
)
select
    city,total_loan_amount,average_loan_amount,total_customers,loan_type_name most_applied_loan_type
from
    t1 
join
    t3 
using(city)
order by 1

优雅永不过时