题解 | 贷款情况

with l_type_temp as (
select city,loan_type_name,
count(la.customer_id)  as count_type,lat.loan_type_id
from loan_application_types lat
join loan_types lt on lt.loan_type_id=lat.loan_type_id
join loan_applications la on la.application_id=lat.application_id
join  customers c on c.customer_id=la.customer_id
group by city,lat.loan_type_id,loan_type_name
),
get_rn_temp as (
select  city,loan_type_name, count_type,loan_type_id,
rank() over(partition by city order  by  count_type DESC,loan_type_id) as rn 
from l_type_temp
),
cal_temp as (
SELECT c.city,sum(loan_amount) as total_loan_amount,
round( sum(loan_amount)/count(distinct la.customer_id) ,2) as average_loan_amount,
count(distinct la.customer_id)  as total_customers 
FROM  customers c
join loan_applications la on la.customer_id=c.customer_id
group by c.city 
)
select ct.city,total_loan_amount,average_loan_amount,total_customers,
loan_type_name  as most_applied_loan_type
from cal_temp ct 
join get_rn_temp  gr on gr.city=ct.city  and rn=1
order by ct.city;