题解 | 贷款情况

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