题解 | 贷款情况

with
    type_rank as (
        select
            city,
            loan_type_name,
            dense_rank() over (
                partition by
                    city
                order by
                    cnt desc,
                    loan_type_id
            ) ranks
        from
            (
                select
                    a.city,
                    b.loan_type_name,
                    b.loan_type_id,
                    count(b.loan_type_name) as cnt
                from
                    (
                        select
                            loan_applications.application_id,
                            customers.city
                        from
                            loan_applications
                            join customers on loan_applications.customer_id = customers.customer_id
                    ) a
                    join (
                        select
                            loan_application_types.application_id,
                            loan_types.loan_type_id,
                            loan_types.loan_type_name
                        from
                            loan_application_types
                            join loan_types on loan_application_types.loan_type_id = loan_types.loan_type_id
                    ) b on a.application_id = b.application_id
                group by
                    a.city,
                    b.loan_type_name,
                    b.loan_type_id
            ) tb1
    )
select
    tb1.city,
    tb1.total_loan_amount,
    tb1.average_loan_amount,
    tb1.total_customers,
    type_rank.loan_type_name as most_applied_loan_type
from
    (
        select
            b.city,
            round(sum(a.loan_amount),2)as total_loan_amount,
            count(distinct a.customer_id) as total_customers,
            round(sum(a.loan_amount) / count(distinct a.customer_id),2) as average_loan_amount
        from
            loan_applications a
            join customers b on a.customer_id = b.customer_id
        group by
            b.city
    ) tb1 
    join type_rank on tb1.city = type_rank.city
where 
    type_rank.ranks = 1

解决思路很简单，解题步骤比较繁琐