题解 | 贷款情况

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