题解 | 贷款情况

WITH
dataframe
    as (
        select
            la.*,
            cs.customer_name,
            cs.city,
            cs.age,
            lt.loan_type_name,
            lat.loan_type_id
        from
            loan_applications as la
            inner join customers as cs on la.customer_id = cs.customer_id
            inner join loan_application_types as lat on la.application_id = lat.application_id
            inner join loan_types as lt on lat.loan_type_id = lt.loan_type_id
    ),
rank_table as(
select * from 
(
select city,loan_type_name,
row_number() over(partition by city order by counting desc,loan_type_id asc) as ranking
from 
(
select city,loan_type_name,loan_type_id,count(*) as counting
from dataframe
group by city,loan_type_name,loan_type_id
) as A
) as A
where ranking = 1
)


select
    A.city,
    round(sum(loan_amount), 2) as total_loan_amount,
    round(sum(loan_amount)/count(distinct customer_id),2) as average_loan_amount,
    count(distinct customer_id) as total_customers,
    B.loan_type_name as most_applied_loan_type
from
dataframe as A
left join rank_table as B on A.city = B.city
group by A.city,B.loan_type_name