最近做了蚂蚁的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

近似题目练习推荐

查询出每个品牌在不同月份的总销售额以及购买该品牌商品的用户的平均年龄

  • 知识点：分组聚合、sum、group by

电商平台想要了解不同商品在不同月份的销售趋势

  • 知识点：子查询与分组聚合、分组统计、JOIN

用户订单信息查询

  • 知识点：分组聚合、SQL连接、聚合函数、排序

更多题解与练习推荐可参考牛客网讨论区