题解 | 贷款情况

with
    max_type as (--统计每个城市每种类型贷款的数量
        select
            city,
            lat.loan_type_id,
            loan_type_name,
            row_number() over (
                partition by
                    city
                order by
                    count(lat.loan_type_id) desc,
                    loan_type_id
            ) ranking
        from
            loan_applications la
            left join customers c on (la.customer_id = c.customer_id)
            left join loan_application_types lat on (la.application_id = lat.application_id)
            left join loan_types lt on (lat.loan_type_id = lt.loan_type_id)
        group by
            city,
            loan_type_id,
            loan_type_name
    ),
    max_ as (--选出每个城市贷款数量第一的贷款名称
        select
            city,
            loan_type_name
        from
            max_type
        where
            ranking = 1
    ),
    loan_sum as (--统计每个城市贷款总金额、平均金额、人数
        select
            city,
            sum(loan_amount) total_loan_amount,
            round(sum(loan_amount)/count(distinct la.customer_id),2) average_loan_amount,
            count(distinct la.customer_id) total_customers
        from
            loan_applications la
            left join customers c on (la.customer_id = c.customer_id)
            left join loan_application_types lat on (la.application_id = lat.application_id)
            left join loan_types lt on (lat.loan_type_id = lt.loan_type_id)
        group by
            city
    )
select
    l.city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    m.loan_type_name as most_applied_loan_type
from
    loan_sum l
    left join max_ m on (l.city = m.city)
order by city