SQL大神带你飞 | 24春招蚂蚁SQL真题解析-贷款情况

贷款情况

https://www.nowcoder.com/practice/2817d353f0634208bcf0de74f56ca8f0

最近做了蚂蚁的24年春招题，题目如下： alt alt

题目分析

本题要求统计每个城市的贷款申请情况，输出每个城市的总贷款金额、平均每位客户的贷款金额、客户总数，以及申请次数最多的贷款类型名称。输出字段包括 city、total_loan_amount、average_loan_amount、total_customers、most_applied_loan_type。输出顺序以 city 分组，未指定排序方式，通常按 city 升序输出。涉及的知识点有：多表连接、分组聚合、窗口函数（rank）、子查询、分组统计、字段重命名。

解答步骤

1. 统计每个城市的总贷款金额、平均每位客户的贷款金额、客户总数

通过 loan_applications、customers、loan_application_types、loan_types 四表连接，按 city 分组，分别统计总贷款金额、客户数、平均贷款金额。
代码如下：

select c.city,
       round(sum(la.loan_amount),2) as total_loan_amount,
       round(sum(la.loan_amount)/count(distinct c.customer_id),2) as average_loan_amount,
       count(distinct c.customer_id) as total_customers
from loan_applications la
join customers c on c.customer_id = la.customer_id
join loan_application_types lat on lat.application_id = la.application_id
join loan_types lt on lat.loan_type_id = lt.loan_type_id
group by c.city

2. 统计每个城市申请次数最多的贷款类型

通过窗口函数 rank()，对每个城市的贷款类型按申请次数降序排名，取排名为1的类型。
代码如下：

select c.city, lt.loan_type_id, lt.loan_type_name,
       rank() over(partition by c.city order by count(lat.loan_type_id) desc, lat.loan_type_id asc) as rk
from loan_applications la
join customers c on c.customer_id = la.customer_id
join loan_application_types lat on lat.application_id = la.application_id
join loan_types lt on lat.loan_type_id = lt.loan_type_id
group by c.city, lt.loan_type_id, lt.loan_type_name

3. 合并两个子查询，输出最终结果

将上述两个子查询按 city 连接，筛选出 rk=1 的记录，得到每个城市申请次数最多的贷款类型名称。
代码如下：

select s1.city, s1.total_loan_amount, s1.average_loan_amount, s1.total_customers,
       s2.loan_type_name as most_applied_loan_type
from (
    -- 子查询1
) s1
join (
    -- 子查询2
) s2 on s2.city = s1.city
where rk = 1

完整代码

select s1.city,s1.total_loan_amount,s1.average_loan_amount,s1.total_customers,
s2.loan_type_name as most_applied_loan_type
from (
    select c.city,
    round(sum(la.loan_amount),2) as total_loan_amount,
    round(sum(la.loan_amount)/count(distinct c.customer_id),2) as average_loan_amount,
    count(distinct c.customer_id) as total_customers
    from loan_applications la
    join customers c on c.customer_id = la.customer_id
    join loan_application_types lat on lat.application_id = la.application_id
    join loan_types lt on lat.loan_type_id = lt.loan_type_id
    group by c.city
)s1
join (
        select c.city,lt.loan_type_id,lt.loan_type_name,
        rank() over(partition by c.city order by count(lat.loan_type_id) desc,lat.loan_type_id asc) as rk
        from loan_applications la
        join customers c on c.customer_id = la.customer_id
        join loan_application_types lat on lat.application_id = la.application_id
        join loan_types lt on lat.loan_type_id = lt.loan_type_id
        group by c.city,lt.loan_type_id,lt.loan_type_name
    )s2 on s2.city = s1.city
where rk = 1