题解 | 贷款情况

with all_customer as (
select
    customer_id,
    sum(loan_amount) as loan_amount
from loan_applications
group by customer_id
),
loan_amount as (
select
    c.city,
    round(sum(a.loan_amount),2) as total_loan_amount,
    round(avg(a.loan_amount),2) as average_loan_amount,
    count(c.customer_id) as total_customers
from 
    customers c 
    left join all_customer a on c.customer_id=a.customer_id
group by c.city
),
loan_type as (
select
    t2.city,
    t3.loan_type_id,
    t3.loan_type_name,
    count(*) as cnt
from
    customers t2
    left join loan_applications t1 on t2.customer_id=t1.customer_id
    left join loan_application_types t4 on t4.application_id=t1.application_id
    left join loan_types t3 on t3.loan_type_id=t4.loan_type_id
group by t2.city, t3.loan_type_id,t3.loan_type_name
),
loan_rk as (
select 
    city,
    loan_type_name as most_applied_loan_type
from
(select
    city,
    loan_type_name,
    row_number() over (partition by city order by cnt desc, loan_type_id asc) as rk
from loan_type) t
where rk=1
)
select
    l1.city,
    l1.total_loan_amount,
    l1.average_loan_amount,
    l1.total_customers,
    l2.most_applied_loan_type
from 
    loan_amount l1
    left join loan_rk l2 on l1.city=l2.city
order by city asc

分成两大部分，统计人数上的amount和loan的类型，分别计算再join