题解 | 贷款情况

with t1 as(
select
b.city,
round(sum(a.loan_amount),2) as total_loan_amount,
round(sum(a.loan_amount)/count(distinct a.customer_id),2) as average_loan_amount,
count(distinct a.customer_id) as total_customers
from 
loan_applications a 
join customers b using(customer_id) 
join loan_application_types c using(application_id) 
join loan_types d using(loan_type_id)
group by b.city),
t2 as(
select
b.city,
d.loan_type_name,
d.loan_type_id,
count(distinct a.customer_id) as total_customers
from 
loan_applications a 
join customers b using(customer_id) 
join loan_application_types c using(application_id) 
join loan_types d using(loan_type_id)
group by b.city,d.loan_type_name,d.loan_type_id),
t3 as(
select 
city,
loan_type_name,
row_number() over(partition by city order by total_customers desc,loan_type_id) as rn
from t2),
t4 as(
select
city,
loan_type_name
from t3
where rn=1)
select 
t1.*,
t4.loan_type_name as most_applied_loan_type
from t1 join t4 using(city)
order by t1.city

仔细观察，最后一列从部分数据得出的，所以要单独筛选后再关联t1。