题解 | 贷款情况

with a as
(select city 
,round(sum(loan_amount),2) 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)
group by city )
,
b as 
(select city 
,loan_type_name
,row_number() over (partition by city order by count(loan_type_name)desc,loan_type_id asc) as rk
from 
loan_applications join loan_application_types using(application_id)
join customers using(customer_id)
join loan_types using(loan_type_id)
group by city,loan_type_id,loan_type_name)

select city,total_loan_amount,average_loan_amount,total_customers,loan_type_name as most_applied_loan_type
from a join b using(city)
where rk=1