题解 | 贷款情况

with
    t1 as (
        select
            city,
            count(distinct customer_id) as total_customers,
            round(sum(loan_amount),2) as total_loan_amount,
            round(sum(loan_amount)/count(distinct customer_id),2) as average_loan_amount
        from
            loan_applications l
            join customers c using (customer_id)
        group by
            city
    ),
    t2 as (
        select
            distinct city,
            loan_type_id,
            loan_type_name,
            /*按城市+类型分类*/
            count(*) over (
                partition by
                    city,
                    loan_type_id
            ) as cnt
        from
            loan_applications l
            join customers c using (customer_id)
            join loan_application_types using (application_id)
            join loan_types using (loan_type_id)
        group by
            city,
            loan_type_id,
            loan_type_name
    ),
    t4 as (
        select
            city
            ,loan_type_id
            ,loan_type_name as most_applied_loan_type
        from
            (
                select
                    city
                    ,loan_type_id
                    ,loan_type_name
                    ,row_number() over (
                        partition by
                            city
                        order by
                            cnt desc,
                            loan_type_id
                    ) as rk
                from
                    t2
            ) t3
        where rk = 1
    )

select distinct city
,total_loan_amount
,average_loan_amount
,total_customers
,most_applied_loan_type
from t1
join t4 using(city)
order by city