题解 | 贷款情况

-- 做这种题最笨的方法但是最通用的就是，一个指标一个子查询，注意，首列是每一个子查询最终结果的group by 条件


with tla as(
select city,sum(loan_amount) as total_loan_amount
from 
(
select
    la.application_id,
    la.customer_id,
    la.loan_amount,
    cr.city
from
    loan_applications as la 
left join
    customers as cr on la.customer_id = cr.customer_id
) as A
group by city)
,

ala as(
select city,round(sum(loan_amount)/count(distinct customer_id),2) as average_loan_amount
from 
(
select
    la.application_id,
    la.customer_id,
    la.loan_amount,
    cr.city
from
    loan_applications as la 
left join
    customers as cr on la.customer_id = cr.customer_id
) as A
group by city
),

tc as (
select city,count(distinct customer_id) as total_customers
from 
(
select
    la.application_id,
    la.customer_id,
    la.loan_amount,
    cr.city
from
    loan_applications as la 
left join
    customers as cr on la.customer_id = cr.customer_id
) as A
group by city)
,

malt 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 counting desc,loan_type_id asc) 
as ranking
from 
(
select city,loan_type_name,loan_type_id,count(loan_type_name) as counting
from 
(
select
    la.application_id,
    la.customer_id,
    la.loan_amount,
    cr.city,
    lt.loan_type_name,
    lt.loan_type_id
from
    loan_applications as la 
left join
    customers as cr on la.customer_id = cr.customer_id
left join 
    loan_application_types as lat on la.application_id = lat.application_id
left join 
    loan_types as lt on lat.loan_type_id = lt.loan_type_id
) as A
group by city,loan_type_name,loan_type_id
) as A
) as A 
where ranking = 1)

select 
tla.city,
tla.total_loan_amount,
ala.average_loan_amount,
tc.total_customers,
malt.most_applied_loan_type
from tla 
left join ala on tla.city = ala.city
left join tc on tla.city = tc.city
left join malt on tla.city = malt.city
order by tla.city