题解 | 贷款情况

with t1 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 la 
join loan_application_types lat using(application_id)
join customers c using(customer_id)
join loan_types lt using(loan_type_id)
group by 1),
t2 as -- 统计每个城市申请次数最多的贷款类型
(select
    city,
    loan_type_id,
    loan_type_name,
    rank() over(partition by city order by count(loan_type_id) desc,loan_type_id) as rk
from loan_applications la 
join loan_application_types lat using(application_id)
join customers c using(customer_id)
join loan_types lt using(loan_type_id)
group by 1,2,3)
-- 汇总,通过城市关联
select 
    city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    loan_type_name as most_applied_loan_type
from t1 join t2 using(city)
where rk=1
order by 1;