题解 | 贷款情况
贷款情况
https://www.nowcoder.com/practice/2817d353f0634208bcf0de74f56ca8f0
先把4张表连接成一张宽表，然后分别用两张临时表，一张是城市维度，一张是城市—贷款类型维度，计算要求指标。
这里出错的点是用了窗口函数里+distinct，会报错，想要在贷款维度里计算上一级维度的去重计数，发现不行
with total as (
select
la.application_id,la.customer_id,loan_amount,
c.city,
lt.loan_type_id,loan_type_name
from loan_applications la
inner join customers c on la.customer_id=c.customer_id
inner join loan_application_types lat on la.application_id=lat.application_id
inner join loan_types lt on lat.loan_type_id=lt.loan_type_id
),
city_amount as (
select
city,
sum(loan_amount) 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 total
group by city
),
city_loantype_count as (
select
city,loan_type_id,loan_type_name,
count(*) as ct,
row_number() over(partition by city order by count(*) desc,loan_type_id) as rn
from total
group by city,loan_type_id,loan_type_name
)
select
c.city,
total_loan_amount,average_loan_amount,total_customers,
loan_type_name as most_applied_loan_type
from city_amount c
inner join city_loantype_count cl on c.city=cl.city
where rn=1
order by c.city;
