题解 | 贷款情况

做的有点麻烦

with t1 as (
    select  city,loan_type_name,count(l2.application_id) num,
    row_number()over(partition by city order by count(l2.application_id) desc,l2.loan_type_id) rk 
    from loan_applications l1 join customers c on l1.customer_id = c.customer_id
    join loan_application_types l2 on l1.application_id = l2.application_id 
    join loan_types l3 on l2.loan_type_id = l3.loan_type_id
    group by city,loan_type_name,l2.loan_type_id
),
t2 as (
    select city,sum(loan_amount) total_loan_amount,
    round(sum(loan_amount)/count(distinct c.customer_id),2) average_loan_amount,
    count(distinct c.customer_id) total_customers
    from loan_applications l1 join customers c on l1.customer_id = c.customer_id
    join loan_application_types l2 on l1.application_id = l2.application_id 
    join loan_types l3 on l2.loan_type_id = l3.loan_type_id
    group by city
)
select t2.city,total_loan_amount,average_loan_amount,total_customers,
loan_type_name most_applied_loan_type
from t1 join t2 on t1.city = t2.city
where t1.rk = 1