首页 > 试题广场 >

贷款情况

[编程题]贷款情况

热度指数：11206 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

某金融服务公司希望分析其客户的贷款申请情况，以便更好地了解客户的行为模式和风险管理。具体来说，他们希望了解每个城市的客户贷款申请情况，包括每个城市的贷款申请总金额、平均贷款金额、客户数量以及最常申请的贷款类型。

【原始表】
loan_applications 表:

application_id: 贷款申请的唯一 ID，作为主键 (INT)
customer_id: 申请贷款的客户 ID (INT)
loan_amount: 申请的贷款金额 (DECIMAL)
application_date: 申请的日期 (DATE)

customers 表:

customer_id: 客户的唯一 ID，作为主键 (INT)
customer_name: 客户的姓名 (VARCHAR)
city: 客户所在的城市 (VARCHAR)
age: 客户的年龄 (INT)

loan_types 表:

loan_type_id: 贷款类型的唯一 ID，作为主键 (INT)
loan_type_name: 贷款类型的名称 (VARCHAR)

loan_application_types 表:

application_id: 与 loan_applications 表中的 application_id 相关联，表示贷款申请的 ID (INT)
loan_type_id: 与 loan_types 表中的 loan_type_id 相关联，表示贷款类型的 ID (INT)

【要求】
查询每个城市的客户贷款申请情况，包括每个城市的贷款申请总金额、平均贷款金额、客户数量以及最常申请的贷款类型（如果有多个贷款类型申请数量相同，则选择 loan_type_id 最小的那个），查询结果按照城市名称升序排列。
包含下面的字段：

city: 城市名称
total_loan_amount: 该城市所有客户的贷款申请总金额，保留小数点后2位。
average_loan_amount: 该城市所有客户的平均每个人的贷款申请金额，保留小数点后2位。
total_customers: 该城市的客户数量
most_applied_loan_type: 该城市最常申请的贷款类型名称

【示例输入】

loan_applications

customers

loan_types

loan_application_types

【示例输出】

示例1

输入

CREATE TABLE loan_applications (
    application_id INT PRIMARY KEY,
    customer_id INT,
    loan_amount DECIMAL(10, 2),
    application_date DATE
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50),
    age INT
);

CREATE TABLE loan_types (
    loan_type_id INT PRIMARY KEY,
    loan_type_name VARCHAR(50)
);

CREATE TABLE loan_application_types (
    application_id INT,
    loan_type_id INT,
    PRIMARY KEY (application_id, loan_type_id)
);

INSERT INTO loan_applications (application_id, customer_id, loan_amount, application_date) VALUES
(1, 1, 10000.00, '2023-01-01'),
(2, 2, 15000.00, '2023-02-01'),
(3, 3, 20000.00, '2023-03-01'),
(4, 4, 25000.00, '2023-04-01'),
(5, 1, 30000.00, '2023-05-01');

INSERT INTO customers (customer_id, customer_name, city, age) VALUES
(1, 'Alice', 'New York', 30),
(2, 'Bob', 'Los Angeles', 25),
(3, 'Charlie', 'New York', 35),
(4, 'David', 'Chicago', 28);

INSERT INTO loan_types (loan_type_id, loan_type_name) VALUES
(1, 'Personal Loan'),
(2, 'Home Loan'),
(3, 'Auto Loan');

INSERT INTO loan_application_types (application_id, loan_type_id) VALUES
(1, 1),
(2, 2),
(3, 1),
(4, 3),
(5, 2);

输出

city|total_loan_amount|average_loan_amount|total_customers|most_applied_loan_type
Chicago|25000.00|25000.00|1|Auto Loan
Los Angeles|15000.00|15000.00|1|Home Loan
New York|60000.00|30000.00|2|Personal Loan

算法知识视频讲解

Sqlite

新梦想是讲单口相声

大致是需要两步：

第一步，找出每个城市最常见的loan_type，使用窗口函数排序，需要连接四个表，所以建立了一个临时表temp

第二步，计算总贷款金额，平均贷款金额，人数，这一步仅需要连接两个表，即含有金额的loan_applications和含有city的customers。最后join前面的临时表即可得到结果。

PS：最开始我的思路也是JOIN一张大表然后筛选，但是过程有点复杂，很绕，这是理清思路后改的代码

WITH temp AS
(SELECT
    DISTINCT city,
    loan_type_id,
    loan_type_name,
    COUNT(*) AS cts,
    ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(*) DESC,loan_type_id) AS rk
FROM
    loan_applications
    LEFT JOIN customers USING (customer_id)
    LEFT JOIN loan_application_types USING (application_id)
    LEFT JOIN loan_types USING (loan_type_id)
GROUP BY city,loan_type_id,loan_type_name
)


SELECT
    DISTINCT c.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,
    loan_type_name AS most_applied_loan_type
FROM loan_applications l
LEFT JOIN customers c USING (customer_id)
LEFT JOIN temp ON temp.city = c.city AND rk =1
GROUP BY city,loan_type_name
ORDER BY city

发表于 2025-08-29 14:55:26 回复(0)

牛客484876145号

with
    t1 as 
    #连接成一张大表       
        (select
            customer_id,
            city,
            loan_amount,
            application_id,
            loan_type_id,
            loan_type_name
        from loan_applications la
        join customers c using(customer_id)
        join loan_application_types lat using(application_id)
        join loan_types lt using(loan_type_id)),
    t2 as(
        #排名表
        select
            city,
            loan_type_name,
            count(loan_type_name),
            row_number() over(partition by city order by count(loan_type_name) desc,loan_type_id) rk
        from t1
        group by city,loan_type_name,loan_type_id
    ),
    t3 as(
        #贷款合计/均值表
        select
            city,
            sum(loan_amount) total_loan_amount,
            round(sum(loan_amount) / count(distinct customer_id),2) average_loan_amount,
            count(distinct customer_id) total_customers
        from t1
        group by city
    )
select
#按city链接t2和t3
    city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    loan_type_name most_applied_loan_type
from t2 join t3 using(city)
where rk = 1
order by city

发表于 2025-07-23 09:37:40 回复(0)

有胆量的托尼在迎接offer

WITH a AS (
    select city,
        sum(loan_amount) as total_loan_amount,
        round(sum(loan_amount)/count(distinct customer_id),2) as average_loan_amount,       #平均数不能用avg，因为一个用户可能有多比订单
        count(distinct customer_id) as total_customers
    from customers
        join loan_applications using(customer_id)
    group by city
),
b AS (
    select city,
        loan_type_name,
        loan_type_id,
        count(*) as cnt
    from loan_applications
        join customers using(customer_id)
        join loan_application_types using(application_id)
        join loan_types using(loan_type_id)
    group by city,loan_type_id,loan_type_name
),      #此表为不同城市不同借款种类的数量
c AS (
    select *,
        row_number() over(partition by city order by cnt desc,loan_type_id) as rk
    from b
),      #开窗，排序！
d AS (
    select city,
        loan_type_name,
        loan_type_id,
        cnt
    from c
    where rk = 1
)       #找出每个城市最常申请的贷款类型
select city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    loan_type_name as most_applied_loan_type
from a
    join d using(city)      #最后join即可

发表于 2025-12-18 19:34:03 回复(0)

牛客461283432号

感觉我写的好冗长

with t1 as(
    select
        b.city,
        sum(a.loan_amount) as total_loan_amount,
        count(distinct a.customer_id) as total_customers
    from loan_applications a left join customers b on a.customer_id = b.customer_id
    group by b.city
)
,t2 as(
    select
        b.city,
        b.customer_id,
        sum(a.loan_amount) as sum_loan_amount
    from loan_applications a left join customers b on a.customer_id = b.customer_id
    group by b.city,b.customer_id
)
, t3 as(
    select
        city,
        avg(sum_loan_amount) as average_loan_amount
    from t2
    group by city
)
, t4 as(
    select
        b.city,
        d.loan_type_id,
        d.loan_type_name,
        count(a.application_id) as total_applications
    from loan_applications a left join customers b on a.customer_id = b.customer_id 
        left join loan_application_types c on a.application_id = c.application_id
        left join loan_types d on c.loan_type_id = d.loan_type_id
    group by b.city,d.loan_type_id,d.loan_type_name
)
, t5 as(
    select
        city,
        loan_type_id,
        loan_type_name,
        row_number() over(partition by city order by total_applications desc,loan_type_id asc) as rk
    from t4
)
select distinct
    a.city,
    round(coalesce(t1.total_loan_amount, 0), 2) as total_loan_amount,
    round(coalesce(t3.average_loan_amount, 0), 2) as average_loan_amount,
    coalesce(t1.total_customers, 0) as total_customers,
    t5.loan_type_name as most_applied_loan_type
from customers a left join t1 on a.city = t1.city left join t3 on a.city = t3.city left join t5 on a.city = t5.city
where rk = 1
order by a.city asc;

发表于 2025-12-11 15:02:46 回复(0)

MiserT

难点就是找出各个城市中每个贷款类型的人数

with #找出每个城市中的各贷款类型的人数排名
    city_logan_rank as (
        select
            c.city, 
            # lt.loan_type_id,
            lt.loan_type_name, 
            # count(*) cnt
            row_number() over (
                partition by
                    c.city
                order by
                    count(*) desc,
                    lt.loan_type_id
            ) rk
        from
            loan_applications l
            join loan_application_types la on la.application_id = l.application_id
            join loan_types lt on lt.loan_type_id = la.loan_type_id
            join customers c on c.customer_id = l.customer_id
        group by
            c.city,
            lt.loan_type_id,
            lt.loan_type_name
    )
select
    c.city,
    sum(loan_amount) total_loan_amount,
    round(
        sum(loan_amount) / count(distinct l.customer_id),
        2
    ) average_loan_amount,
    count(distinct l.customer_id) total_customers,
    (
        select
            loan_type_name
        from
            city_logan_rank
        where
            city = c.city
            and rk = 1
    ) most_applied_loan_type
from
    loan_applications l
    join customers c using (customer_id)
group by
    c.city
order by 
    c.city

发表于 2025-12-02 18:36:18 回复(0)

风流倜傥三金老师

①关联4个表，展示每一个贷款的相关信息

②以城市和贷款类型分组求出每个城市最常申请的贷款类型名称

③以城市分组求出其他指标

④关联两个表求出结果

WITH

t1 AS ( -- 关联4个表，展示每一个贷款的相关信息

SELECT

kehu.customer_id,

kehu.city,

daikuan.loan_amount,

leixing.loan_type_id,

leixing.loan_type_name

FROM customers AS kehu

LEFT JOIN loan_applications AS daikuan ON kehu.customer_id = daikuan.customer_id

LEFT JOIN loan_application_types AS fenlei ON daikuan.application_id = fenlei.application_id

LEFT JOIN loan_types AS leixing ON fenlei.loan_type_id = leixing.loan_type_id

t2 AS ( -- 以城市和贷款类型分组,使用窗口函数对各产品类型的销量进行排名

SELECT

city,

loan_type_id,

loan_type_name,

ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(*) DESC,loan_type_id) AS rk

FROM t1

GROUP BY city,loan_type_name,loan_type_id

t3 AS ( -- 以城市分组求出其他指标

SELECT

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

FROM t1

GROUP BY city

)

SELECT -- 主查询，关联清洗后的数据，依题意输出结果

t3.city,

t3.total_loan_amount,

t3.average_loan_amount,

t3.total_customers,

t2.loan_type_name AS most_applied_loan_type

FROM t3

LEFT JOIN t2 ON t3.city = t2.city AND t2.rk = 1

ORDER BY t3.city;

发表于 2025-11-30 18:06:37 回复(0)

z202303051459871

# 算出最多的贷款类型
with t as (
select 
    t2.city,
    t2.most_applied_loan_type
from (
        select
            t1.city,
            t1.loan_type_name as most_applied_loan_type ,
            row_number() over(partition by t1.city order by t1.num_app desc, t1.loan_type_id) as rk
        from 
            (
                select
                c.city,
                lt.loan_type_name,
                lt.loan_type_id,
                count(1) as num_app
            from loan_applications la
                join customers c
                on c.customer_id = la.customer_id 
                join loan_application_types lat 
                on la.application_id = lat.application_id
                join loan_types lt
                on lt.loan_type_id = lat.loan_type_id
            group by c.city,lt.loan_type_id
            ) t1
)t2
where t2.rk = 1
),
# 按照城市粒度，算出平均数（注意用户数量不是贷款次数 要distinct）
t3 as (
 select
        c.city,
        sum(la.loan_amount) as total_loan_amount,
        sum(la.loan_amount)/count(distinct c.customer_id) 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 la.application_id = lat.application_id
        join loan_types lt
        on lt.loan_type_id = lat.loan_type_id
    group by c.city
)
# 简单join得出结果
select
t.city,
t3.total_loan_amount,
round(t3.average_loan_amount,2) as average_loan_amount,
t3.total_customers,
t.most_applied_loan_type
from t3
join t
on t.city = t3.city

发表于 2025-11-13 11:17:13 回复(0)

忙碌的伊泽瑞尔准备进厂

select 
t1.city,
total_loan_amount,
average_loan_amount,
total_customers,
loan_type_name as most_applied_loan_type
from 
(
select 
city,
round(sum(loan_amount),2)total_loan_amount,
round(sum(loan_amount)/count(distinct a.customer_id),2) average_loan_amount,
count(distinct a.customer_id)total_customers
from loan_applications a
left join customers b on a.customer_id = b.customer_id
left join loan_application_types  c on a.application_id = c.application_id
left join loan_types d on c.loan_type_id = d.loan_type_id
group by city
) t1
left join 
(
select 
city,
loan_type_name
from 
    (
        select 
        city,
        loan_type_id,
        loan_type_name,
        row_number() over(partition by city order by cnt desc, loan_type_id asc) rn
        from 
        (
        select 
        city,
        c.loan_type_id,
        loan_type_name,
        count(1) cnt
        from loan_applications a
        left join customers b on a.customer_id = b.customer_id
        left join loan_application_types  c on a.application_id = c.application_id
        left join loan_types d on c.loan_type_id = d.loan_type_id
        group by city,c.loan_type_id,loan_type_name
        ) a 
    )b
where rn = 1
) t2
on t1.city = t2.city

发表于 2025-11-07 22:43:46 回复(0)

Mysql

牛客812823537号

with t1 as (
select city,loan_applications.application_id,loan_amount,loan_type_id,loan_applications.customer_id
from loan_applications left join customers using(customer_id)
left join loan_application_types using(application_id)
)

select t4.city,total_loan_amount,average_loan_amount,total_customers,most_applied_loan_type
from (
select city,sum(loan_amount) as total_loan_amount,round(sum(loan_amount)/count(distinct customer_id),2) as average_loan_amount,count(distinct customer_id) as total_customers
from t1 
group by city) t3 left join (select city,loan_type_name as  most_applied_loan_type
from (select city,loan_type_id,count(*) as loan_type_count,row_number() over (partition by city order by count(*) desc,loan_type_id) as ranked
from t1 
group by city,loan_type_id)t2 left join loan_types using(loan_type_id)
where ranked=1
) t4 using(city)
order by t4.city

整体思路分为两块，一个是每个城市的贷款金额、人数，这一部分简单的聚合函数就能实现，注意平均贷款金额是人均；另一部分是每个城市的最多贷款销售类型，通过窗口函数得到。

发表于 2025-11-05 16:35:24 回复(0)

牛客55480282号

#由于聚合操作不能再一张表内进行
#则第一张表通过简单group by统计各个城市得成交金额，平均成交金额和客户数
#第二张表则group by city ，loan_type_name，loan_type_id 对城市中贷款名称按照数量排序，取第一个就好
#根据city进行链接合并
with t1 as(
    select city,sum(a.loan_amount) as total_loan_amount,round(sum(a.loan_amount)/count(distinct a.customer_id),2) as average_loan_amount,count(distinct a.customer_id) as total_customers
    from loan_applications a
    left join customers b on a.customer_id=b.customer_id
    left join loan_application_types c on a.application_id=c.application_id
    left join loan_types d on d.loan_type_id =c.loan_type_id
    group by city
),
t2 as(
    select  city,loan_type_name,row_number()over(partition by city order by count(*) desc,c.loan_type_id asc) as rk
    from loan_applications a
    left join customers b on a.customer_id=b.customer_id
    left join loan_application_types c on a.application_id=c.application_id
    left join loan_types d on d.loan_type_id =c.loan_type_id
    group by city,loan_type_name,c.loan_type_id
)

select t1.*,loan_type_name as  most_applied_loan_type
from t1 
left join t2 on t1.city=t2.city
where rk=1
order by city asc

发表于 2025-11-04 20:50:07 回复(0)

牛客286059704号

#筛选得到前四列

with t1 as (

select b.city

,round(sum(a.loan_amount),2) as total_loan_amount

,round(sum(a.loan_amount)/count(distinct a.customer_id),2) as average_loan_amount

,round(count(distinct a.customer_id),2) as total_customers

from loan_applications a

left join customers b on b.customer_id=a.customer_id

group by b.city

#筛选得到最后一列

t2 as (

select b.city

,c.loan_type_name as most_applied_loan_type

,row_number() over(partition by b.city order by count(a.application_id) desc,d.loan_type_id asc) as rn

from loan_application_types d

left join loan_types c on c.loan_type_id=d.loan_type_id

left join loan_applications a on a.application_id=d.application_id

left join customers b on b.customer_id=a.customer_id

group by b.city,c.loan_type_name,d.loan_type_id

)

select t1.city

,t1.total_loan_amount

,t1.average_loan_amount

,t1.total_customers

,t2.most_applied_loan_type

from t1

left join t2 on t2.city=t1.city

where t2.rn=1

order by t1.city;

真麻烦啊这题

发表于 2025-10-24 15:26:21 回复(0)

破烂儿_

with a as(
    select 
        city,
        round(sum(loan_amount),2) as total_loan_amount,
        round(sum(loan_amount)/count(distinct l.customer_id),2) as average_loan_amount,
        count(distinct l.customer_id) as total_customers
    from loan_applications l
    join customers c on l.customer_id = c.customer_id 
    join loan_application_types t on l.application_id = t.application_id
    join loan_types lt on t.loan_type_id = lt.loan_type_id
    group by city 
),
b as (
   select 
        city,
        loan_type_name,
        lt.loan_type_id,
        count(distinct l.customer_id) as count_loan
    from loan_applications l
    join customers c on l.customer_id = c.customer_id 
    join loan_application_types t on l.application_id = t.application_id
    join loan_types lt on t.loan_type_id = lt.loan_type_id
    group by city,loan_type_name,lt.loan_type_id 
),
c as (
    select 
        city,
        loan_type_name,
        row_number() over(partition by city order by count_loan desc,loan_type_id ) as rk 
    from b 
    
),
d as (
    select 
        city,
        loan_type_name 
    from c
    where rk = 1
)
select 
    a.*,
    d.loan_type_name as most_applied_loan_type
from a 
join d on a.city = d.city
order by city

发表于 2025-10-23 16:36:50 回复(0)

Mysql

像初雪一样遇见你

with t1 as (
select city,sum(loan_amount) as total_loan_amount,
round(sum(loan_amount)/count(distinct x.customer_id),2) as average_loan_amount,
count(distinct x.customer_id) as total_customers
from customers x 
join loan_applications y 
using(customer_id)
group by 1
),t2 as (
select x.application_id,x.customer_id,z.loan_type_id,z.loan_type_name,city
from loan_applications x 
join loan_application_types y 
using(application_id)
join loan_types z 
on y.loan_type_id=z.loan_type_id
join customers a 
on x.customer_id=a.customer_id
),t3 as (
select city,loan_type_name
from
(select city,loan_type_name,row_number() over(partition by city order by count(*) desc,loan_type_id) as rk
from t2
group by city,loan_type_name,loan_type_id) e
where rk=1
)
select t1.*,t3.loan_type_name as most_applied_loan_type
from t1
join t3
using(city)
order by 1

发表于 2025-10-17 14:40:11 回复(0)

牛客765007087号

#一步步无脑联合

with t1 as (
select  
    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
from customers
join loan_applications using(customer_id)
join loan_application_types USING (application_id)
join loan_types USING (loan_type_id)
group by city
),
t2 as (
     select
            city,
            loan_type_name ,
            count(loan_type_name),
            row_number() over(partition by city order by count(loan_type_name) desc,loan_type_id) as rn
        from loan_applications 
        join customers using(customer_id)
        join loan_application_types using(application_id)
        join loan_types  using(loan_type_id)
        group by city,loan_type_name,loan_type_id
)
select
    city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    loan_type_name as most_applied_loan_type
from t1
join t2 using(city)
where rn =1

发表于 2025-10-15 15:50:18 回复(0)

布莱恩本狗

with a as (select city,
sum(loan_amount)  as total_loan_amount,
sum(loan_amount)/count(distinct la.customer_id)  as average_loan_amount,
count(distinct la.customer_id) as total_customers
from loan_applications as la
inner join customers as c on la.customer_id = c.customer_id
group by city),
     b as (select city,
     loan_type_name,
     lt.loan_type_id,
     row_number() over(partition by city order by count(lt.loan_type_id) desc,lt.loan_type_id) as paiming
           from loan_types as lt
           inner join loan_application_types as lat on lt.loan_type_id = lat.loan_type_id
           inner join loan_applications as la on la.application_id = lat.application_id
           inner join customers as c on la.customer_id = c.customer_id
           group by city,lt.loan_type_id,loan_type_name)
select  a.city,total_loan_amount,round(average_loan_amount,2) as average_loan_amount,total_customers,loan_type_name as most_applied_loan_type
from a 
inner join b on a.city = b.city
where paiming =1;

发表于 2025-10-13 17:24:54 回复(0)

牛客735871882号

select 
t8.city,
t8.total_loan_amount,
t8.average_loan_amount,
t8.total_customers,
t7.loan_type_name most_applied_loan_type
from
        (select 
        c.city,
        round((sum(l.loan_amount)),2) total_loan_amount,
        round((sum(l.loan_amount)/count(distinct c.customer_name)),2) average_loan_amount,
        count(distinct c.customer_name) total_customers
        from loan_applications l 
        left join customers c on l.customer_id = c.customer_id
        left join loan_application_types a on l.application_id = a.application_id
        left join loan_types t on a.loan_type_id = t.loan_type_id
        group by c.city) t8
left join 
        (select 
        t6.city,
        t6.loan_type_id,
        t6.loan_type_name
        from 
            (select 
            t5.city,
            t5.loan_type_id,
            t5.loan_type_name,
            row_number()over(partition by t5.city order by t5.count_num desc, t5.loan_type_id) num_rank
            from
                (select 
                t2.city,
                t4.loan_type_id,
                t4.loan_type_name,
                count(distinct t2.customer_id) count_num
                from loan_applications t1 
                left join customers t2 on t1.customer_id = t2.customer_id
                left join loan_application_types t3 on t1.application_id = t3.application_id
                left join loan_types t4 on t3.loan_type_id = t4.loan_type_id
                group by t2.city, t4.loan_type_id) t5) t6
        where t6.num_rank = 1) t7
on t7.city = t8.city
order by t8.city

发表于 2025-10-10 14:10:29 回复(0)

牛客475559997号

WITH t1 AS

(

SELECT

loan_applications.application_id,

loan_applications.customer_id,

loan_applications.loan_amount,

loan_application_types.loan_type_id,

loan_types.loan_type_name

FROM loan_application_types

RIGHT JOIN loan_applications USING(application_id)

JOIN loan_types USING(loan_type_id)

)

,t2 AS (

SELECT

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

FROM t1

JOIN customers USING(customer_id)

GROUP BY

city )

,t3 AS (

SELECT

city,

loan_type_name,

COUNT(loan_type_id) AS count_tpye,

ROW_NUMBER ()OVER (PARTITION BY city ORDER BY COUNT(loan_type_name) DESC,loan_type_id DESC) AS rank_type

FROM t1

JOIN customers USING(customer_id)

GROUP BY

city,loan_type_id

)

SELECT

city,

total_loan_amount,

average_loan_amount,

total_customers,

t3.loan_type_name AS most_applied_loan_type

FROM t2

JOIN t3 USING(city)

WHERE t3.rank_type=1

ORDER BY

city

发表于 2025-10-10 10:31:26 回复(0)

77今天学习了没

select city,total_loan_amount,average_loan_amount,total_customers,loan_type_name as most_applied_loan_type

from

(select 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

from loan_applications

left join customers using(customer_id)

group by city

) as t1

left join(

select city,loan_type_name,loan_type_id,

row_number()over(partition by city order by count(loan_type_name) desc,loan_type_id) as rk

from loan_applications

left join customers using(customer_id)

left join loan_application_types using(application_id)

left join loan_types using(loan_type_id)

group by city,loan_type_name,loan_type_id

) as t2

using(city)

where rk=1

order by city

发表于 2025-10-02 11:24:47 回复(0)

在研究求职打法的他很想和你交朋友

这一串代码出现的问题是，我在子查询里使用了count但是没有用groupby，所以我应该再添加一步子查询。

#每个地区计算最常申请的贷款名单,申请次数最多的，count loan type的id个数
select
    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,
    loan_type_name as most_applied_loan_type
from
    (
        select
            c.city,loan_amount,c.customer_id,
            row_number() over (
                partition by
                    c.city,lat.loan_type_id
                order by
                    count(lat.loan_type_id) desc
            ) as rk,
            loan_type_name
        from
            loan_applications la
            join customers c on la.customer_id = c.customer_id
            join loan_application_types lat on lat.application_id = la.application_id
            join loan_types lt on lt.loan_type_id = lat.loan_type_id
        
    ) as new
    where rk = 1
    group by city,most_applied_loan_type

发表于 2025-09-30 19:56:10 回复(0)

已注销

select
t1.city,
t1.total_loan_amount,
t1.average_loan_amount,
t1.total_customers,
t2.most_applied_loan_type
from
(
    select
    d.city,
    round(sum(a.loan_amount),2) total_loan_amount,
    round(sum(a.loan_amount) / count(distinct a.customer_id),2) average_loan_amount,
    count(distinct a.customer_id) total_customers
    from loan_applications a
    join loan_application_types b on a.application_id=b.application_id
    join loan_types c on b.loan_type_id=c.loan_type_id
    join customers d on a.customer_id=d.customer_id
    group by d.city
) t1
join
(
    select
    city,
    loan_type_name most_applied_loan_type
    from
    (
        select
        city,
        loan_type_name,
        row_number() over(partition by city order by cnt desc, loan_type_id) rn
        from
        (
            select
            d.city,c.loan_type_name,c.loan_type_id,
            count(*) cnt
            from loan_applications a
            join loan_application_types b on a.application_id=b.application_id
            join loan_types c on b.loan_type_id=c.loan_type_id
            join customers d on a.customer_id=d.customer_id
            group by d.city,c.loan_type_name,c.loan_type_id
        ) t
    ) t
    where rn=1
) t2
on t1.city=t2.city
order by city

发表于 2025-09-17 21:53:49 回复(0)

提交观点

问题信息

来自：2024年秋招-蚂蚁集...

难度：

46条回答 216收藏 581浏览

贷款情况

输入

输出

问题信息

热门推荐

相关试题