题解 | 贷款情况

# 查询每个城市的情况
with
t1 as(
    select
        city,
        sum(loan_amount) as total_loan_amount,
        sum(loan_amount)/count(distinct customer_id) as average_loan_amount,
        count(distinct customer_id)as total_customers
    from
        loan_applications
        left join customers using(customer_id)
        left join loan_application_types using(application_id)
    group by
        city
)
,
t2 as(
    select
        customer_id,
        loan_type_id,
        loan_type_name
    from
        loan_application_types
        left join loan_types using(loan_type_id)
        left join loan_applications using(application_id)
)
,
t3 as(
    select
        city,
        loan_type_name,
        dense_rank()over(partition by city order by count(loan_type_name) desc,loan_type_id) as loan_type_rank
    from
        t2 left join customers using(customer_id)
    group by
        city,
        loan_type_id,
        loan_type_name
)
,
t4 as(
    select
        city,
        loan_type_name as most_applied_loan_type
    from
        t3
    where
        loan_type_rank=1
)
,
t5 as(
    select
        city,
        round(total_loan_amount,2) as total_loan_amount,
        round(average_loan_amount,2) as average_loan_amount,
        total_customers,
        most_applied_loan_type
    from
        t4 left join t1 using(city)
    order by
        city
)

select * from t5