题解 | 贷款情况

with t as(
 select city, c.customer_id, loan_amount, loan_type_name, t.loan_type_id
 from loan_applications a
 join customers c on a.customer_id = c.customer_id
join loan_application_types lat on a.application_id = lat.application_id
join loan_types t on lat.loan_type_id = t.loan_type_id
)


select  c.city, total_loan_amount, average_loan_amount, total_customers,
    loan_type_name 'most_applied_loan_type'

from (
    select city, round(sum(loan_amount), 2)'total_loan_amount', round(sum(loan_amount) / count(distinct customer_id),2)'average_loan_amount', count(distinct customer_id)'total_customers'
    from t
    group by city 
)a
join (

    select
        b.city, loan_type_name, row_number()over(PARTITION by b.city ORDER by cnt desc, loan_type_id asc)'rn'
        from(
            select city, count(*) 'cnt',loan_type_name, loan_type_id
            from t
            group by city, loan_type_name, loan_type_id
        )b
)c
on a.city = c.city
where rn = 1
ORDER by city asc;

第一步是join连接所有的表。

第二步：其次按照city进行分组，并依次计算total_loan_amount、average_loan_amount、total_customers等数据，值得注意的是，因为存在一个顾客有多次交易的情况，所有total_customers和average_loan_amount都需要 count(distinct customer_id)获取。

第三步：对于第一步获得的表，分组计算每个loan_type_name的数量，使用row_number()进行排序（相同的时候loan_type_id 取最小）

最后将第二步和第三步的表进行拼接，取rn=1即可获取要求的数据