题解 | 贷款情况
贷款情况
https://www.nowcoder.com/practice/2817d353f0634208bcf0de74f56ca8f0
with a 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 loan_applications
join customers using(customer_id)
join loan_application_types using(application_id)
join loan_types using(loan_type_id)
group by city
),
b as (
select
city,
loan_type_name as most_applied_loan_type,
loan_type_id,
rank() over(partition by city order by count(*) desc,loan_type_id ) as rk
from loan_application_types
join loan_applications using(application_id)
join customers using(customer_id)
join loan_types using(loan_type_id)
group by city,loan_type_name,loan_type_id
)
select
city,
total_loan_amount,
average_loan_amount,
total_customers,
most_applied_loan_type
from a join (
select *
from b
where rk=1
)temp using(city)
order by city;
查看5道真题和解析