首页 > 试题广场 >

分析客户逾期情况

[编程题]分析客户逾期情况

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

有贷款信息表：loan_tb（agreement_id：合同id，customer_id：客户id，loan_amount：贷款金额，pay_amount：已还金额，overdue_days：逾期天数）

agreement_id	customer_id	loan_amount	pay_amount	overdue_days
10111	1111	20000	18000	NULL
10112	1112	10000	10000	NULL
10113	1113	15000	10000	38
10114	1114	50000	30000	NULL
10115	1115	60000	50000	NULL
10116	1116	10000	8000	NULL
10117	1117	50000	50000	NULL
10118	1118	25000	10000	5
10119	1119	20000	1000	106

客户信息表：customer_tb（customer_id：客户id，customer_age：客户年龄，pay_ability：还款能力级别）

customer_id	customer_age	pay_ability
1111	28	B
1112	38	A
1113	20	C
1114	30	A
1115	29	B
1116	21	C
1117	35	B
1118	36	B
1119	25	C

请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比。要求输出还款能力级别、逾期客户占比。

注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序。

示例数据结果如下：

pay_ability	overdue_ratio
C	66.7%
B	25.0%
A	0.0%

结果解释：

还款能力级别为 C 的客户有1113、1116、1119，其中有逾期行为的客户为 1113、1119，故结果为 2/3=66.7%；其他结果同理。

示例1

输入

drop table if exists  `loan_tb` ; 
CREATE TABLE `loan_tb` (
`agreement_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`loan_amount` int(11) NOT NULL,
`pay_amount` int(11) NOT NULL,
`overdue_days` int(11),
PRIMARY KEY (`agreement_id`));
INSERT INTO loan_tb VALUES(10111,1111,20000,18000,null); 
INSERT INTO loan_tb VALUES(10112,1112,10000,10000,null); 
INSERT INTO loan_tb VALUES(10113,1113,15000,10000,38); 
INSERT INTO loan_tb VALUES(10114,1114,50000,30000,null); 
INSERT INTO loan_tb VALUES(10115,1115,60000,50000,null); 
INSERT INTO loan_tb VALUES(10116,1116,10000,8000,null); 
INSERT INTO loan_tb VALUES(10117,1117,50000,50000,null); 
INSERT INTO loan_tb VALUES(10118,1118,25000,10000,5); 
INSERT INTO loan_tb VALUES(10119,1119,20000,1000,106); 

drop table if exists  `customer_tb` ; 
CREATE TABLE `customer_tb` (
`customer_id` int(11) NOT NULL,
`customer_age` int(11) NOT NULL,
`pay_ability` varchar(2) NOT NULL,
PRIMARY KEY (`customer_id`));
INSERT INTO customer_tb VALUES(1111,28,'B'); 
INSERT INTO customer_tb VALUES(1112,38,'A'); 
INSERT INTO customer_tb VALUES(1113,20,'C'); 
INSERT INTO customer_tb VALUES(1114,30,'A'); 
INSERT INTO customer_tb VALUES(1115,29,'B'); 
INSERT INTO customer_tb VALUES(1116,21,'C'); 
INSERT INTO customer_tb VALUES(1117,35,'B'); 
INSERT INTO customer_tb VALUES(1118,36,'B'); 
INSERT INTO customer_tb VALUES(1119,25,'C');

输出

pay_ability|overdue_ratio
C|66.7%
B|25.0%
A|0.0%

算法知识视频讲解

Simon0625

select c.pay_ability,
concat(round(count(l.overdue_days)/count(*)*100,1),'%') overdue_ratio
from loan_tb l left join customer_tb c using(customer_id)
group by c.pay_ability
order by overdue_ratio desc

发表于 2024-08-09 15:46:41 回复(0)

尽量理智的狗狗

select 
     c.pay_ability,
     concat (
         round(count(l.overdue_days) / count(*) * 100, 1),
         '%'
     ) as overdue_ratio
 from
     loan_tb as l
     right join customer_tb as c on l.customer_id = c.customer_id
     and l.overdue_days is not null
 group by
     c.pay_ability
 order by overdue_ratio desc;

发表于 2023-09-20 11:08:48 回复(3)

Mysql

美丽的回笼觉觉主在刷代码

有坑大家记得最后按照overdue_ratio降序排序；

SELECT 
DISTINCT pay_ability,
CONCAT(ROUND(100*COUNT(overdue_days) / COUNT(pay_ability),1) , '%') AS overdue_ratio
FROM(
    SELECT DISTINCT *
    FROM loan_tb lt
    JOIN customer_tb ct USING(customer_id)
    ) AS t
GROUP BY pay_ability
ORDER BY overdue_ratio DESC;

发表于 2024-08-19 11:25:46 回复(0)

牛客198720938号

要分析各还款能力级别的客户逾期情况，统计有逾期行为客户在各还款能力级别中的占比，可以通过以下步骤来实现：

连接贷款表和客户表，将客户信息和贷款信息结合。
计算每个还款能力级别中有逾期行为的客户数量。
计算每个还款能力级别的总客户数。

计算逾期客户的占比（即逾期客户数 / 总客户数）。
WITH customer_overdue AS (
-- 连接贷款信息表和客户信息表
SELECT
c.pay_ability, -- 客户的还款能力级别
l.customer_id, -- 客户ID
CASE WHEN l.overdue_days > 0 THEN 1 ELSE 0 END AS is_overdue -- 标记逾期客户
FROM
loan_tb l
JOIN
customer_tb c ON l.customer_id = c.customer_id
GROUP BY
c.pay_ability, l.customer_id
),
overdue_stats AS (
-- 统计每个还款能力级别的逾期客户数和总客户数
SELECT
pay_ability, -- 还款能力级别
COUNT(DISTINCT customer_id) AS total_customers, -- 总客户数
SUM(is_overdue) AS overdue_customers -- 逾期客户数
FROM
customer_overdue
GROUP BY
pay_ability
)

-- 计算逾期客户占比并输出结果
SELECT
pay_ability AS repayment_ability_level, -- 还款能力级别
ROUND((overdue_customers / total_customers) * 100, 2) AS overdue_customer_ratio -- 逾期客户占比
FROM
overdue_stats
ORDER BY
repayment_ability_level;

解释
1. CTEcustomer_overdue：
  - 将loan_tb和customer_tb通过customer_id连接，获得每个客户的还款能力级别和逾期情况。
  - 使用CASE判断是否逾期，overdue_days > 0表示该客户有逾期行为，赋值为1（表示逾期），否则为0。
2. CTEoverdue_stats：
  - 按pay_ability分组，统计每个还款能力级别的总客户数total_customers和逾期客户数overdue_customers。
  - COUNT(DISTINCT customer_id)统计每个还款能力级别的总客户数。
  - SUM(is_overdue)统计每个还款能力级别的逾期客户数。
3. 主查询：
  - 计算每个还款能力级别的逾期客户占比overdue_customer_ratio，并保留两位小数，作为最终输出结果。

发表于 2024-10-27 19:11:28 回复(4)

4fbd81e

with t1 as
(
    select lt.*,ct.customer_age, ct.pay_ability
    from loan_tb lt
    join customer_tb ct
    on lt.customer_id=ct.customer_id
)
,
t2 as (
    select pay_ability, 
    sum(case when overdue_days is not null then 1 else 0 end) as  overdue_cnt,
    sum(1) as total_cnt
    from t1
    group by pay_ability
)
select pay_ability,
concat(round(overdue_cnt * 100/total_cnt,1),"%") as overdue_ratio
from t2
order by round(overdue_cnt * 100/total_cnt,1) desc

发表于 2023-12-04 18:31:54 回复(0)

积极做数分的人

select

pay_ability,

concat(

round(sum(case when overdue_days is not null then 1 else 0 end) / count(*) * 100, 1 ),

'%'

) as overdue_ratio

from loan_tb left join customer_tb on loan_tb.customer_id = customer_tb.customer_id

group by pay_ability

order by overdue_ratio desc;

发表于 2025-06-12 16:35:41 回复(0)

牛客164735395号

select
    c.pay_ability,
    concat (round(100 * count(l.overdue_days) / count(*), 1),'%') overdue_ratio
from
    loan_tb l
    join customer_tb c on c.customer_id = l.customer_id
group by
    c.pay_ability
order by
    overdue_ratio desc;

看到求比例的题目,想办法构造null值,然后count(有null的列)除以count(*),而本题无需构造自带null值

发表于 2025-05-01 15:15:00 回复(0)

MiserT

select
    pay_ability,
    concat(round(count(l.overdue_days) / count(*) * 100, 1), "%") overdue_ratio
from loan_tb l
join customer_tb c on c.customer_id = l.customer_id
group by
    c.pay_ability
order by
    overdue_ratio desc

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

Mysql

马国成好样的

加小数点还要注意位置，好坑
select pay_ability,concat(round(sum(if_overdue)/count(*)*100,1),"%") overdue_ratio

from(

select loan_tb.customer_id

,pay_ability

,sum(if(overdue_days is not null ,1,0)) if_overdue

from loan_tb

left join customer_tb

on loan_tb.customer_id = customer_tb.customer_id

group by 1,2)a

group by 1

order by 2 desc

发表于 2025-07-04 15:00:59 回复(0)

Mysql

牛客608290637号

select c.pay_ability, concat(round((count(l.overdue_days) / count(1)) * 100,1),'%')   as overdue_ratio from loan_tb l left join customer_tb c on c.customer_id = l.customer_id group by pay_ability  order by overdue_ratio desc;

NULL不参与聚合运算！！！

发表于 2025-03-10 14:24:36 回复(0)

炎煬

with t1 as
(
select pay_ability,t1.customer_id,overdue_days
from loan_tb t1
left join customer_tb t2
on t1.customer_id=t2.customer_id
)
select pay_ability,concat(round(count(overdue_days)*100/count(*),1),'%')as overdue_ratio
from t1
group by pay_ability
order by 2 desc

众所周知，count只计数不null的行。但是大厂题目含糊不清，只是计算逾期人数占所有的借款的占比。而不是占逾期人数的占比

发表于 2025-12-22 12:43:18 回复(0)

Mysql

牛客734061918号

select 
    pay_ability,
    concat(round(count(overdue_days)/count(*)*100,1),'%') as overdue_ratio
from(
    select
        l.customer_id,
        l.overdue_days,
        c.pay_ability
    from loan_tb l
    join customer_tb c using(customer_id)
) as t
group by pay_ability
order by overdue_ratio desc;

发表于 2025-12-19 15:55:35 回复(0)

牛客847191419号

select

pay_ability,

concat(round(count(distinct case when overdue_days>0 then a.customer_id end) / count(distinct a.customer_id)*100,1),'%') as overdue_ratio

from loan_tb a

left join customer_tb b

on a.customer_id=b.customer_id

group by 1

order by overdue_ratio desc

发表于 2025-12-19 11:08:33 回复(0)

有胆量的托尼在迎接offer

select pay_ability,

concat(round(sum(overdue_days is not null)/count(*)*100,1),"%") as overdue_ratio

from customer_tb c

left join loan_tb l on c.customer_id = l.customer_id

group by pay_ability

order by overdue_ratio desc

发表于 2025-12-12 23:09:47 回复(0)

神奇的小饼干在考古

select c.pay_ability,
concat(round(count(overdue_days)/count(pay_ability)*100 ,1),'%') as overdue_ratio
from loan_tb as l
right join customer_tb as c
on l.customer_id = c.customer_id
group by c.pay_ability
order by overdue_ratio desc

发表于 2025-12-10 17:41:05 回复(0)

牛客461283432号

select a.pay_ability,concat(round(count(distinct case when b.overdue_days is not null then a.customer_id end)/count(distinct a.customer_id)*100,1),'%') as overdue_ratio

from customer_tb a left join loan_tb b on a.customer_id = b.customer_id

group by a.pay_ability

order by overdue_ratio desc;

发表于 2025-12-05 17:52:39 回复(0)

Sqlite

牛客983493477号

select b.pay_ability, concat(round(100*count(a.overdue_days)/count(b.pay_ability),1),"%") as overdue_ratio

from loan_tb a left join customer_tb b on a.customer_id = b.customer_id

group by pay_ability

order by overdue_ratio desc

发表于 2025-11-27 02:29:54 回复(0)

十啊

select

c.pay_ability,

concat(

ROUND(

sum(case when l.overdue_days >0 THEN 1 ELSE 0 END) / COUNT(DISTINCT c.customer_id)*100,1

) ,'%'

)as overdue_ratio

from

loan_tb l

join customer_tb c on c.customer_id = l.customer_id

group by

c.pay_ability

order by

overdue_ratio DESC

发表于 2025-11-26 15:05:23 回复(0)

风流倜傥三金老师

SELECT -- 如果是正常业务场景，大概率还应该输出借款数量和逾期数量，防止某个还款能力级别因为无人借款导致逾期率被低估为0

a.pay_ability,

CONCAT

(ROUND(

AVG( -- 逾期率=AVG（预期记作1，未逾期记作0）

CASE WHEN b.overdue_days IS NOT NULL THEN 1 ELSE 0 END

)*100.0

,1)

,'%') AS overdue_ratio

FROM customer_tb AS a

LEFT JOIN loan_tb AS b

ON a.customer_id = b.customer_id

GROUP BY a.pay_ability

ORDER BY overdue_ratio DESC;

发表于 2025-11-23 09:59:38 回复(0)

牛客152117006号

select

c.pay_ability,

concat(

round(sum(if(l.overdue_days is null, 0, 1)) / count(l.customer_id)*100,1),

'%'

) as overdue_ratio

from loan_tb as l

join customer_tb c

on l.customer_id = c.customer_id

group by c.pay_ability

order by overdue_ratio desc

发表于 2025-11-22 15:58:57 回复(0)

提交观点

问题信息

SQL

来自：2023年蚂蚁春招技术...

难度：

192条回答 812收藏 2173浏览

分析客户逾期情况

输入

输出

解释

问题信息

热门推荐

相关试题