题解 | 贷款情况

With base as (
    select a.*,b.city,c.loan_type_name, d.loan_type_id from
    loan_applications a
    left join customers b
    on a.customer_id = b.customer_id
    left join loan_application_types d
    on a.application_id = d.application_id
    left join loan_types c
    on d.loan_type_id = c.loan_type_id
),

base1 as (
    select city
        ,sum(loan_amount) as total_loan_amount
        ,count(distinct customer_id) as total_customers
        from base
        group by city
),

base2 as (
    select city,loan_type_name,loan_type_id,count(*) as nums from base
    group by city,loan_type_name,loan_type_id
),

base3 as (
    select city,row_number() over(partition by city order by nums DESC,loan_type_id) ranking, loan_type_id, loan_type_name from base2 
)

select a.city, round(a.total_loan_amount,2) as total_loan_amount, round(a.total_loan_amount/a.total_customers,2) as average_loan_amount, a.total_customers,
b.loan_type_name as most_applied_loan_type
from base1 a left join base3 b
on a.city = b.city and b.ranking = 1