首页 > 试题广场 >

贷款情况

[编程题]贷款情况

热度指数：14241 时间限制：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)

牛客929857392号

SELECT 
    c.city,
    SUM(la.loan_amount) as total_loan_amount,
    ROUND(SUM(la.loan_amount) / COUNT(DISTINCT la.customer_id), 2) as average_loan_amount,
    COUNT(DISTINCT la.customer_id) as total_customers,
    (SELECT lt2.loan_type_name
     FROM loan_applications la2
     JOIN customers c2 ON la2.customer_id = c2.customer_id
     JOIN loan_application_types lap2 ON la2.application_id = lap2.application_id
     JOIN loan_types lt2 ON lap2.loan_type_id = lt2.loan_type_id
     WHERE c2.city = c.city
     GROUP BY lt2.loan_type_id, lt2.loan_type_name
     ORDER BY COUNT(*) DESC, lt2.loan_type_id ASC  
     LIMIT 1
    ) as most_applied_loan_type
FROM loan_applications la
JOIN customers c ON la.customer_id = c.customer_id
GROUP BY c.city
ORDER BY c.city;

发表于 2026-02-06 12:11:21 回复(0)

牛客178163380号

with t1 as (
    select 
        c.city,
        lat.loan_type_id,
        lt.loan_type_name,
        count(lat.application_id) as cts,
        row_number() over(partition by c.city 
            order by count(lat.application_id) desc, lat.loan_type_id) as rk
    from customers c
    inner join loan_applications la on c.customer_id = la.customer_id
    inner join loan_application_types lat on lat.application_id = la.application_id
    inner join loan_types lt on lat.loan_type_id = lt.loan_type_id 
    group by c.city, lat.loan_type_id, lt.loan_type_name 
)
, t2 as (
    select 
        c.city,
        round(sum(la.loan_amount), 2) as total_loan_amount,
        round(sum(la.loan_amount)/count(distinct la.customer_id), 2) as average_loan_amount,
        count(distinct la.customer_id) as total_customers
    from customers c 
    inner join loan_applications la on la.customer_id = c.customer_id
    group by c.city
)


select 
    t2.city,
    t2.total_loan_amount,
    t2.average_loan_amount,
    t2.total_customers,
    t1.loan_type_name as most_applied_loan_type
from t2 
inner join t1 on t2.city = t1.city
where t1.rk = 1

发表于 2026-03-07 07:17:36 回复(0)

牛客947671679号

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

join (select city, loan_type_name
from (select *, row_number() over (partition by city order by a desc,loan_type_id) b
from (select distinct *
from (select city,
loan_type_id,
loan_type_name,
count(loan_type_id) over (partition by loan_type_id,city) a
from (select *
from loan_applications la
join customers c using (customer_id)
join (select application_id, lat.loan_type_id, loan_type_name
from loan_application_types lat
join loan_types lt on lat.loan_type_id = lt.loan_type_id) t2
using (application_id)) t3) t4
order by a desc) t5) t6
where b = 1) tt2 using (city)
order by city

让我再写一遍也写不出来，。。。居然过了

发表于 2026-03-06 20:39:10 回复(0)

虫哇

with t1 as (

select la.customer_id,

c.city,

la.loan_amount,

lat.application_id,

lt.loan_type_id,

lt.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) as cnt,

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

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 city, total_loan_amount, average_loan_amount,

total_customers, loan_type_name as most_applied_loan_type

from t2

join t3 using(city)

where rn = 1

order by city

发表于 2026-03-03 15:18:16 回复(0)

牛客316984733号

大佬们帮忙看看，这个代码哪里写错了？为啥newYork的总金额不对呢？

select city,total_loan_amount,average_loan_amount,total_customers,loan_type_name as most_applied_loan_type
from 
(
    select
        *,
        row_number() over (
            partition by
                city
            order by
                total_application desc,
                loan_type_id
        ) as rank1
    from
        (
            select
                city,
                b.loan_type_id,loan_type_name,
                sum(loan_amount) as total_loan_amount,
                round(avg(loan_amount), 2) as average_loan_amount,
                count(distinct a.customer_id) as total_customers,
                count(a.application_id) as total_application
            from
                loan_applications as a
                left join loan_application_types as b on a.application_id = b.application_id
                left join loan_types as c on b.loan_type_id = c.loan_type_id
                left join customers as d on a.customer_id = d.customer_id
            group by
                city,
                b.loan_type_id,
                loan_type_name
        ) as m
) as n
where rank1=1
order by 1

发表于 2026-03-03 09:33:19 回复(0)

Mysql

牛客340530180号

with tb1 as

(

select

city,

loan_amount,

tb1.customer_id,

loan_type_name,

tb4.loan_type_id

from

loan_applications as tb1,

customers as tb2,

loan_types as tb3,

loan_application_types as tb4

where

tb1.customer_id=tb2.customer_id

and tb1.application_id=tb4.application_id

and tb3.loan_type_id=tb4.loan_type_id

tb2 as

(

select

city,

sum(loan_amount) total_loan_amount,

sum(loan_amount)/count(distinct customer_id) average_loan_amount,

count(distinct customer_id) total_customers

from

tb1

group by

city

tb3 as

(

select

city,loan_type_name,loan_type_id,

count(*) n

from tb1

group by city,loan_type_name,loan_type_id

tb4 as

(

select

city,

loan_type_name,

rank() over (partition by city order by n desc, loan_type_id) r

from

tb3

tb5 as

(

select

city,loan_type_name

from

tb4

where

r=1

)

select

tb2.city,

round(total_loan_amount,2) total_loan_amount,

round(average_loan_amount,2) average_loan_amount,

total_customers,

loan_type_name most_applied_loan_type

from tb2 join tb5 on tb2.city=tb5.city

order by tb2.city

发表于 2026-02-22 13:20:26 回复(0)

只会写SELECT

-- 计算总/平均贷款金额/客户数
with q1 as(
    select t2.city,round(sum(t1.loan_amount),2) total_loan_amount,
    round(sum(t1.loan_amount)/count(distinct t1.customer_id),2) average_loan_amount,
    count(distinct t1.customer_id) total_customers
    from loan_applications t1 
    left join customers t2 on t1.customer_id=t2.customer_id
    group by t2.city
),
-- 计算贷款类型
q2 as(
    select t3.city,t4.loan_type_name,
    row_number()over(partition by t3.city order by count(t2.loan_type_id) desc,t4.loan_type_id) rk
    from loan_applications t1 
    join loan_application_types t2 on t1.application_id=t2.application_id
    left join customers t3 on t1.customer_id=t3.customer_id
    left join loan_types t4 on t2.loan_type_id=t4.loan_type_id
    group by t3.city,t4.loan_type_id,t4.loan_type_name
)

select
q1.city,q1.total_loan_amount,q1.average_loan_amount,q1.total_customers,
q2.loan_type_name most_applied_loan_type
from q1 join q2 on q1.city=q2.city
where q2.rk=1
order by q1.city

发表于 2026-02-20 11:30:25 回复(0)

超级可乐

平均?跟谁平均?

发表于 2026-02-17 00:19:09 回复(0)

九卿欧尼

SELECT c_t_a.city, total_loan_amount, average_loan_amount, total_customers, most_applied_loan_type FROM
    (SELECT city, sum(loan_amount) total_loan_amount FROM 
        (SELECT c.city, la.loan_amount FROM loan_applications la join customers c on la.customer_id = c.customer_id) c_loan
    GROUP BY city) c_t_a
join
    (SELECT city, loan_type_name most_applied_loan_type FROM
        (SELECT city, loan_type_name, total_loan_amount, row_number()over(partition by city order by cnt desc, loan_type_id asc) rk FROM
            (SELECT c.city, lt.loan_type_name, lt.loan_type_id, sum(la.loan_amount) total_loan_amount, count(la.loan_amount) cnt
            FROM loan_applications la join customers c on la.customer_id = c.customer_id
                join loan_application_types lat on la.application_id = lat.application_id
                join loan_types lt on lat.loan_type_id = lt.loan_type_id
            GROUP BY c.city, lt.loan_type_name, lt.loan_type_id) al) al
    WHERE rk = 1) c_t_m
on c_t_m.city = c_t_a.city
join
    (SELECT city, count(customer_id) total_customers FROM customers
    GROUP BY city) c_t_c
on c_t_a.city = c_t_c.city
join
    (SELECT city, round(avg(sm),2) average_loan_amount FROM
        (SELECT i_s.customer_id, c.city, i_s.sm FROM
            (SELECT customer_id, sum(loan_amount) sm FROM loan_applications
            GROUP BY customer_id) i_s
        join customers c on i_s.customer_id = c.customer_id) al
    GROUP BY city) c_a
on c_a.city = c_t_a.city

发表于 2026-02-10 17:42:21 回复(0)

七分糖少冰a

终于来点稍微有点挑战的题目了，打卡：2026年1月22日22:00:45

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

发表于 2026-01-22 22:01:05 回复(0)

最喜欢冬天的沸羊羊很活泼

复杂的一步在于找出最常见的loan_type，并且这得是单独一张表

发表于 2026-01-15 16:19:47 回复(0)

牛客972538838号

select *
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 customers
    inner join loan_applications using(customer_id)
    inner join loan_application_types using(application_id)
    inner join loan_types using(loan_type_id)
    group by city
) t1 
inner join (
    -- 查询每个城市申请贷款最多的类型并去重
    select 
    distinct city,
    first_value(loan_type_name) over(partition by city order by count(loan_type_id) desc, loan_type_id) as most_applied_loan_type
    from customers
    inner join loan_applications using(customer_id)
    inner join loan_application_types using(application_id)
    inner join loan_types using(loan_type_id)
    group by city, loan_type_id
) t2 using(city)

发表于 2026-01-01 14:55:32 回复(0)

牛客328049299号

平均贷款金额贷款金额要平均到人头，不是平均到每笔，不能直接用avg算，神了

发表于 2025-12-24 20:09:05 回复(0)

在划水的丘比特很爱交友

with
a as ( 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_name ) AS total_customers
FROM loan_applications la
LEFT JOIN customers c ON la.customer_id = c.customer_id
GROUP BY c.city),
b as (select c.city, lt.loan_type_name
from loan_applications la
LEFT JOIN customers c ON la.customer_id = c.customer_id
left join loan_application_types lat on la.application_id = lat.application_id
left join loan_types lt on lat.loan_type_id = lt.loan_type_id),
c AS (SELECT
b.city,
b.loan_type_name,
COUNT(*) AS loan_count,
ROW_NUMBER() OVER (PARTITION BY b.city ORDER BY COUNT(*) DESC) AS rn
FROM b
GROUP BY b.city, b.loan_type_name
)
SELECT a.city, a.total_loan_amount, a.average_loan_amount, a.total_customers, c.loan_type_name AS most_applied_loan_type
FROM a left join c on a.city = c.city
WHERE c.rn = 1;

发表于 2025-12-22 21:33:52 回复(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)

提交观点

问题信息

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

难度：

60条回答 266收藏 1017浏览

贷款情况

输入

输出

问题信息

热门推荐

相关试题