题解 | 贷款情况

with a as(
    select 
        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 loan_applications 
        join customers using(customer_id)
        join loan_application_types using(application_id)
        join loan_types using(loan_type_id)
    group by city
),
b as (
    select 
        city,
        loan_type_name as most_applied_loan_type,
        loan_type_id,
        rank() over(partition by city order by count(*) desc,loan_type_id ) as rk
    from loan_application_types
        join loan_applications using(application_id)
        join customers using(customer_id)
        join loan_types using(loan_type_id)
    group by city,loan_type_name,loan_type_id 
)
select 
    city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    most_applied_loan_type
from a join (
    select *
    from b
    where rk=1
)temp using(city)
order by city;