题解 | 贷款情况

with full_tb as(
    select 
        city,
        lt.loan_type_name,
        lt.loan_type_id,
        loan_amount,
        c.customer_name
    from 
        customers as c 
        left join loan_applications as la on c.customer_id = la.customer_id
        left join loan_application_types as lat on la.application_id = lat.application_id
        inner join loan_types as lt on lat.loan_type_id = lt.loan_type_id
),
right_part as(
    select
        city,
        loan_type_name as most_applied_loan_type
    from
        (
            select
                city,
                loan_type_name,
                type_total_customers,
                row_number() over(partition by city order by type_total_customers desc, loan_type_id) as ranking
            from 
                (
                    select 
                        city,
                        loan_type_id,
                        loan_type_name,
                        sum(loan_amount) as type_total_loan_amount,
                        count(customer_name) as type_total_customers
                    from full_tb
                    group by city, loan_type_id, loan_type_name
                ) as tmp
        ) as ranking_tb
    where ranking = 1
),
left_part as(
    select
        city,
        sum(loan_amount) as total_loan_amount,
        round(sum(loan_amount) / count(distinct customer_name), 2) as average_loan_amount,
        count(distinct(customer_name)) as total_customers
    from full_tb
    group by city
)


select 
    left_part.city as city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    most_applied_loan_type
from 
    right_part
    inner join left_part on right_part.city = left_part.city
order by city;