首页 > 试题广场 >

贷款情况

[编程题]贷款情况

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

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)

季夏十一

with loan_detail as (
select
t1.loan_amount
,t2.city
,t1.customer_id
,t4.loan_type_name
,t3.loan_type_id
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
)

select
    t1.city
    ,total_loan_amount
    ,average_loan_amount
    ,total_customers
    ,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_detail
    group by city
) t1

left join
(
    select
        city
        ,loan_type_name as most_applied_loan_type
    from
    (
        select
            city
            ,loan_type_name
            ,row_number() over(partition by city order by loan_type_count desc,loan_type_id asc) as rk
        from
        (
            select
                city
                ,loan_type_id
                ,loan_type_name
                ,count(distinct loan_type_name) as loan_type_count
            from loan_detail
            group by
                city
                ,loan_type_id
                ,loan_type_name
        ) t1
    ) t2
    where rk = 1
) t3 on t1.city = t3.city

发表于 2025-09-15 14:41:26 回复(0)

牛客668587629号

with tmp1 as

(

select f.*,row_number()over(partition by city order by cnt desc ,loan_type_id) as rn

from

(

select t2.city

,t3.loan_type_id

,t4.loan_type_name

,count(loan_type_name)over(partition by t2.city) as cnt

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

) f

)

select t2.city

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

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

,count(distinct t1.customer_id) as total_customers

,t5.loan_type_name as most_applied_loan_type

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 tmp1 t5

on t2.city = t5.city and t5.rn = 1

group by t2.city,t5.loan_type_name

order by t2.city

发表于 2025-09-14 16:45:10 回复(0)

找工倒霉蛋

不算难，但是很繁琐，因为要求排名没想出来能用一张大表解决的方法，只能分开做了

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

select
    t.city,total_loan_amount,average_loan_amount,total_customers,most_applied_loan_type
from t 
join t2 on t.city = t2.city
order by city

发表于 2025-09-10 14:50:55 回复(0)

jacky酱

with t1 as (

select

c.city,

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

round(sum(l.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

group by c.city),

t2 as (

select

c.city,

lt.loan_type_name,

count(lt.loan_type_id) as cn,

row_number() over (partition by c.city order by count(lt.loan_type_id) desc,lt.loan_type_id asc) as rk

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_id,lt.loan_type_name)

select

t1.city,

t1.total_loan_amount,

t1.average_loan_amount,

t1.total_customers,

t2.loan_type_name as most_applied_loan_type

from t1 join t2 on t1.city = t2.city

where t2.rk = 1

order by t1.city;

发表于 2025-09-09 18:28:56 回复(0)

Jimhacker

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,count(distinct a.customer_id) as total_customers from loan_applications a join customers b on a.customer_id=b.customer_id group by b.city)

,t2 as(

select b.city,c.loan_type_name,row_number()over(partition by b.city order by count(c.loan_type_name) desc,c.loan_type_id asc) as rk from loan_types c join loan_application_types d on c.loan_type_id=d.loan_type_id join loan_applications a on a.application_id=d.application_id join customers b on b.customer_id=a.customer_id group by b.city,c.loan_type_name,c.loan_type_id)

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

发表于 2025-09-09 17:56:57 回复(0)

矫健的小刺猬在看牛客

with
loan_customer_info as
(
    select
        c.customer_id
        , city
        , lat.loan_type_id
        , lt.loan_type_name
        , loan_amount
    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
),
city_most_applied_loan_type as
(
    select
        city
        , loan_type_name
    from
        (
            select
                city 
                , loan_type_name 
                , row_number() over(partition by city order by count(loan_type_name) desc, loan_type_id) as rk
            from loan_customer_info
            group by city, loan_type_id, loan_type_name
        ) temp
    where rk = 1
)
select
    lci.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
    , max(cm.loan_type_name) as most_applied_loan_type
from loan_customer_info lci
    left join city_most_applied_loan_type cm on lci.city = cm.city
group by city
order by city 
;

发表于 2025-09-07 15:30:53 回复(0)

沐泽n

select
    a.city,
    total_loan_amount,
    average_loan_amount,
    total_customers,
    loan_type_name as most_applied_loan_type
from
    (
        select
            city,
            sum(loan_amount) as total_loan_amount,
            round(
                sum(loan_amount) / count(distinct customer_name),
                2
            ) as average_loan_amount,
            count(distinct customer_name) as total_customers
        from
            loan_applications a
            join customers b
            join loan_types c
            join loan_application_types d on a.application_id = d.application_id
            and a.customer_id = b.customer_id
            and c.loan_type_id = d.loan_type_id
        group by
            city
    ) a
    join (
        select
            city,
            c.loan_type_id loan_type_id,
            rank() over (
                partition by
                    city
                order by
                    count(*) desc,
                    c.loan_type_id asc
            ) as rnk
        from
            loan_applications a
            join customers b
            join loan_types c
            join loan_application_types d on a.application_id = d.application_id
            and a.customer_id = b.customer_id
            and c.loan_type_id = d.loan_type_id
        group by
            city,
            c.loan_type_id
    ) b
    join loan_types c on a.city = b.city
    and b.loan_type_id = c.loan_type_id
where
    rnk = 1
order by
    a.city

发表于 2025-09-05 10:30:21 回复(0)

不讲武德的比尔很自来熟

select
    a.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 la.customer_id),
                2
            ) as average_loan_amount,
            count(distinct la.customer_id) as total_customers
        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
        group by
            city
    ) a
    left join (
        select
            city,
            lt.loan_type_name,
            row_number() over (
                partition by
                    city
                order by
                    count(1) desc, lt.loan_type_id ASC
            ) as ranking
        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
        group by
            lt.loan_type_name,
            city,
            lt.loan_type_id
    ) b on a.city = b.city
where
    ranking = 1
order by
    city asc

发表于 2025-08-31 17:04:06 回复(0)

理性的布拉德破防了

with base as (

select

c.city,

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

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

count(distinct l.customer_id) as total_customers

from customers c join loan_applications l

on c.customer_id = l.customer_id

group by c.city

loan_type_count as (

SELECT

c.city,

lat.loan_type_id,

COUNT(*) AS application_count

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

GROUP BY c.city, lat.loan_type_id

loan_type_rank as(

select

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

from loan_type_count

common_loan_type as (

select

city,

lt.loan_type_name

from loan_type_rank lyr

join loan_types lt on lyr.loan_type_id = lt.loan_type_id

where rn = 1

)

select

b.city,

b.total_loan_amount,

b.average_loan_amount,

b.total_customers,

c.loan_type_name as most_applied_loan_type

from base b join common_loan_type c

on b.city = c.city

order by city

发表于 2025-08-31 11:22:36 回复(0)

追赶太阳的雪碧很可爱

-- 利用视窗筛选出来需要的数据，并且添加列：rank

with t1 as(

select

,count(loan_type_id) over(PARTITION BY city,loan_type_id) as count

from loan_applications

join customers c using (customer_id)

join loan_application_types using (application_id)

join loan_types using (loan_type_id)

)

-- 写总和、平均数、计数，注意不能用avg，要分开写。另外由于first_value（）窗口函数和group冲突，所以这里用相关子查询。

select

city

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

,round((sum(loan_amount)/count(distinct customer_name)),2) as average_loan_amount

,count(distinct customer_name) as total_customers

select

loan_type_name from t1 t2

where t1.city=t2.city

order by count desc,loan_type_id

limit 1

)

as most_applied_loan_type

from t1

group by city

发表于 2025-08-25 10:19:41 回复(0)

提交观点

问题信息

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

难度：

36条回答 134收藏 351浏览

贷款情况

输入

输出

问题信息

热门推荐

相关试题