题解 | 贷款情况

select
    a.city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    most_applied_loan_type
from
    (
        select
            city,
            sum(loan_amount) total_loan_amount,
            round(
                sum(loan_amount) / count(distinct la.customer_id),
                2
            ) average_loan_amount
        from
            loan_applications la
            join customers c on la.customer_id = c.customer_id
        group by
            1
    ) a
    join (
        select
            city,
            count(customer_id) total_customers
        from
            customers
        group by
            1
    ) b on a.city = b.city
    join (
        select
            city,
            loan_type_name most_applied_loan_type
        from
            (
                select
                    city,
                    loan_type_name,
                    lat.loan_type_id,
                    count(*) applied_loan_type,
                    row_number() over (
                        partition by
                            city
                        order by
                            count(*) desc,
                            lat.loan_type_id asc
                    ) rk
                from
                    loan_types lt
                    join loan_application_types lat on lt.loan_type_id = lat.loan_type_id
                    join loan_applications la on lat.application_id = la.application_id
                    join customers c on la.customer_id = c.customer_id
                group by
                    1,
                    2,
                    3
            ) b
        where
            rk = 1
    ) d on b.city = d.city
order by
    a.city