题解 | 贷款情况

-- 做这种题最笨的方法但是最通用的就是，一个指标一个子查询，注意，首列是每一个子查询最终结果的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


作者：人来疯的向宇同桌在找内推
链接：https://www.nowcoder.com/discuss/842776171409338368
来源：牛客网