首页 > 试题广场 >

分析客户逾期情况

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

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

算法知识视频讲解

尽量理智的狗狗

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 回复(2)

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)

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)

牛客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)

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)

Sqlite

牛客664610992号

select
    t2.pay_ability,
    concat(FORMAT(round(
        (sum(
            case
                when t.overdue_days is not null then '1'
                else '0'
            end
        ) / count(t.customer_id)) * 100,
        1
    ),1),"%") overdue_ratio
from
    loan_tb t
    left join customer_tb t2 on t.customer_id = t2.customer_id
group by
    t2.pay_ability
order by overdue_ratio desc;

发表于 2025-04-27 16:20:31 回复(0)

想去三亚看海的梅花鹿ssp到手了

select pay_ability,concat(round(SUM(CASE WHEN a.overdue_days IS NOT NULL THEN 1 ELSE 0 END)/count(a.customer_id)*100,1),'%') as overdue_ratio
from loan_tb a join customer_tb b on a.customer_id=b.customer_id
group by pay_ability

order by overdue_ratio desc

1.总结。

case when 的用法 case when XX =‘ ’ then X else X end

2.sum里面也可以使用判断语句。

发表于 2025-04-21 17:59:04 回复(1)

小白⚪

#请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比。要求输出还款能力级别、逾期客户占比。
#注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序。

#结果是等级（该等级逾期人数）/（该等级总人数人数）

#1、计算是否逾期
SELECT c.pay_ability,case when l.overdue_days > 0 THEN 1 else 0 end isOverdue
from customer_tb c left join loan_tb l on c.customer_id = l.customer_id

#2、计算逾期人数该等级总人数
SELECT c.pay_ability,sum(case when l.overdue_days > 0 THEN 1 else 0 end ) overduecustomer,count(c.customer_id) customers
from customer_tb c left join loan_tb l on c.customer_id = l.customer_id
GROUP BY pay_ability;

#3、结果是等级（该等级逾期人数）/（该等级总人数人数）
with ltb as(
SELECT c.pay_ability,sum(case when l.overdue_days > 0 THEN 1 else 0 end ) overduecustomer,count(c.customer_id) customers
from customer_tb c left join loan_tb l on c.customer_id = l.customer_id
GROUP BY pay_ability
)
SELECT pay_ability,CONCAT(ROUND(CAST((overduecustomer/customers) AS DECIMAL(10, 3))*100,1),'%') as overdue_ratio
from ltb
ORDER BY (overduecustomer/customers) desc;

发表于 2025-04-18 21:24:53 回复(0)

牛客131088855号

select
pay_ability
,concat(round(countoverdays/countcostomer*100,1),'%') overdue_ratio
from
( 
    select
    pay_ability	
    ,sum(if (loan_tb.overdue_days is null,0,1)) countoverdays
    ,count(loan_tb.customer_id) countcostomer
    from loan_tb
    left join customer_tb on
    loan_tb.customer_id=customer_tb.customer_id
    group by 1
) a
order by overdue_ratio desc

发表于 2025-04-15 15:26:44 回复(0)

周小果2025

select

t2.pay_ability,

CONCAT (

FORMAT (

count(

distinct if (overdue_days > 0, t2.customer_id, null)

) / count(distinct t2.customer_id) * 100,

'%'

) overdue_ratio

from

loan_tb t1

right join customer_tb t2 on t1.customer_id = t2.customer_id

group by

t2.pay_ability

order by

overdue_ratio desc

发表于 2025-04-10 16:53:56 回复(0)

kukuju

with t as(

select b.customer_id, b.pay_ability,overdue_days,

count(overdue_days)over(partition by pay_ability) as over_cnt,

count(1)over(partition by pay_ability) as cnt

from loan_tb a

left join customer_tb b

on a.customer_id=b.customer_id

)

select distinct pay_ability,concat(round(over_cnt/cnt *100,1),'%')as overdue_ratio

from t

order by overdue_ratio desc

发表于 2025-04-10 15:16:43 回复(0)

uid101057251

select pay_ability,
    concat(
        round(100*sum(if(overdue_days is not null,1,0))/count(*),1),'%'
    ) as overdue_ratio
from loan_tb lt 
join customer_tb ct
on lt.customer_id=ct.customer_id
group by pay_ability
order by overdue_ratio desc

发表于 2025-04-09 21:24:56 回复(0)

小八飞毛腿

select pay_ability,
concat(round(count(distinct case when overdue_days is not null 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-04-09 14:41:50 回复(0)

牛客971219448号

select pay_ability,
concat(round(count(case when overdue_days<>0 then 1 end)*100/count(1),1),'%') overdue_ratio
from(
select ct.pay_ability pay_ability,
case when lt.overdue_days is null then 0 else lt.overdue_days end overdue_days
from customer_tb ct
left join loan_tb lt on ct.customer_id=lt.customer_id
) a
group by pay_ability
order by overdue_ratio desc

发表于 2025-04-04 14:18:41 回复(0)

紧张的小猫在春招

SELECT

pay_ability,

CONCAT (

ROUND(COUNT(overdue_days) * 100 / COUNT(*), 1),

'%'

) AS overdue_ratio

FROM

(

SELECT

IFNULL (overdue_days, 0) AS overdus_days_new

FROM

loan_tb

) loan_tb_new

JOIN customer_tb USING (customer_id)

GROUP BY

pay_ability

ORDER BY

overdue_ratio DESC;

发表于 2025-03-31 14:47:08 回复(0)

迟缓的小龙虾说我这儿没问题啊

select pay_ability,

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

from

loan_tb l left join customer_tb c

on l.customer_id=c.customer_id

group by pay_ability

order by overdue_ratio desc

发表于 2025-03-28 13:47:42 回复(0)

牛客130469250号

这样可以不用考虑逾期天数为null，count自动不计数导致数据不准确的问题了

select pay_ability,concat(round(count(overdue_days)*100/count(pay_ability),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 pay_ability desc

发表于 2025-03-26 10:28:00 回复(0)

牛客311750497号

with overdue as(

select distinct customer_id #单个客户存在多笔逾期合同

from loan_tb

where overdue_days is not null

)

select pay_ability,

concat(round(count(o.customer_id)/count(c.customer_id)*100,1),'%') as overdue_ratio

from customer_tb c left join overdue o #公司已有客户但未贷款情况

on c.customer_id = o.customer_id

group by c.pay_ability

order by overdue_ratio desc;

发表于 2025-03-25 16:09:17 回复(0)

提交观点

问题信息

SQL

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

难度：

114条回答 346收藏 1078浏览

分析客户逾期情况

输入

输出

解释

问题信息

热门推荐

相关试题