题解 | 贷款情况

select b.city,round(sum(loan_amount),2) total_loan_amount,round(sum(loan_amount)/count(distinct b.customer_id ),2) average_loan_amount,round(count(distinct b.customer_id ),2) total_customers,max(loan_type_name) most_applied_loan_type
from loan_applications e 
inner join customers b on e.customer_id=b.customer_id
inner join loan_application_types d on e.application_id=d.application_id
inner join (
    select city,loan_type_name,loan_type_id,rank() over(partition by city, loan_type_id order by c desc,loan_type_id) r
    from(select 
        city,loan_type_name,l3.loan_type_id,count(1) c
    from loan_applications l1 
    inner join customers c on l1.customer_id=c.customer_id
    inner join loan_application_types l2 on l2.application_id=l1.application_id
    inner join loan_types l3 on l3.loan_type_id=l2.loan_type_id
    group by city,loan_type_name,l3.loan_type_id
) a1 ) a on a.loan_type_id=d.loan_type_id and a.city=b.city and r=1
group by b.city
order by b.city