题解 | 贷款情况

with tab as(
# 筛出排名为1的个城市贷款类型
select city,loan_type_name as most_applied_loan_type,ranking
from(
# 筛选每个城市频数最高的贷款种类并分配降序排名，最高的排名为1，隐藏条件为同频次情况下id升序
select city,loan_type_name,loan_type_id,row_number() over(partition by city order by counting desc,loan_type_id asc) as ranking
from(
# 建立二级分组确定每个城市每种贷款类型各自的交易频数
select city,loan_type_name,loan_type_id,count(distinct application_id)as counting
from(
# 连接所有表
select l.application_id,l.customer_id,c.city,lat.loan_type_id,lt.loan_type_name,l.loan_amount
from loan_applications as l
left join customers as c on l.customer_id = c.customer_id
left join loan_application_types as lat on l.application_id = lat.application_id
left join loan_types as lt on lat.loan_type_id = lt.loan_type_id
) as temp
group by city,loan_type_id,loan_type_name
) as temp2
) as temp3
where ranking <= 1
)
# 创建with临时表与金额统计表的连接,用round(,2)和cast(object as decimal(10,2))等价
select l1.city,l1.total_loan_amount,cast(l1.total_loan_amount/l1.total_customers as decimal(10,2)) as average_loan_amount,l1.total_customers,t.most_applied_loan_type
from(
# 统计各城市总贷款金额和贷款客户数
select city,sum(loan_amount) as total_loan_amount,count(distinct customer_id) as total_customers
from(
# 连接所有表
select l.application_id,l.customer_id,c.city,lat.loan_type_id,lt.loan_type_name,l.loan_amount
from loan_applications as l
left join customers as c on l.customer_id = c.customer_id
left join loan_application_types as lat on l.application_id = lat.application_id
left join loan_types as lt on lat.loan_type_id = lt.loan_type_id
) as temp
group by city
) as l1
inner join tab as t on l1.city = t.city