题解 | 贷款情况

with 
table1 as(
    select
        c.city,
        round(sum(a.loan_amount),2) as total_loan_amount,
        round(sum(a.loan_amount)/count(distinct a.customer_id),2) as average_loan_amount,
        count(distinct a.customer_id) as total_customers
    from loan_applications a
    left join
    (select
        t1.loan_type_id,
        t2.application_id,
        t1.loan_type_name
    from loan_types t1 
    left join loan_application_types t2 on t1.loan_type_id=t2.loan_type_id
    ) t 
    on a.application_id=t.application_id
    left join customers c
    on a.customer_id=c.customer_id
    group by c.city),
table2 as(
    select
        c.city,
        t.loan_type_name,
        row_number() over(partition by c.city order by count(a.application_id) desc,t.loan_type_id) as rn
    from loan_applications a
    left join
    (select
        t1.loan_type_id,
        t2.application_id,
        t1.loan_type_name
    from loan_types t1
    left join loan_application_types t2 on t1.loan_type_id=t2.loan_type_id
    ) t 
    on a.application_id=t.application_id
    left join customers c
    on a.customer_id=c.customer_id
    group by c.city,t.loan_type_id,t.loan_type_name)

select 
    table1.*,
    table2.loan_type_name as most_applied_loan_type
from table1 
left join (select city,loan_type_name from table2 where rn=1) table2
on table1.city=table2.city