题解 | 贷款情况

with a1 as (
select 
city ,
loan_type_id,
loan_type_name,
rank() over(partition by city order by count(*) desc ,loan_type_id) as rk
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,loan_type_id,loan_type_name
),

a2 as(
    select city ,loan_type_name
    from a1 
    where rk =1
),

a3 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 )

select city,total_loan_amount,average_loan_amount,total_customers,
loan_type_name as most_applied_loan_type
from a2 
join a3 
using(city)
order by city