首页 > 试题广场 >

分析客户逾期情况

[编程题]分析客户逾期情况
  • 热度指数：35359 时间限制：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%
算法知识视频讲解
添加回答
Mysql
牛客608290637号头像 牛客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)
Mysql
喜欢吃蛋糕孤勇者想要offer头像 喜欢吃蛋糕孤勇者想要offer
有没有大佬能帮我分析一下为啥不对哇
select pay_ability, concat(round(count(overdue_days) / count(c.customer_id) ,1),'%') as overdue_ratio
from loan_tb l left join customer_tb c
on l.customer_id = c.customer_id
where overdue_days != 'NULL'
group by pay_ability
order by overdue_ratio desc
发表于 2025-02-14 15:48:43 回复(0)
Mysql
牛客893505329号头像 牛客893505329号
结果是对的。但是顺序不一样，就一直错

select pay_ability, concat(round(num_over/sum_ability*100,1),'%') as overdue_ratio from (
    select g1.pay_ability,g1.sum_ability,if(g2.num_over is not null,g2.num_over,0) as num_over from

(select pay_ability, count(*) as sum_ability from customer_tb group by pay_ability) as g1

left join

(select pay_ability, count(*) as num_over from
(select c.customer_id,c.pay_ability,l.overdue_days from customer_tb c left join loan_tb l on c.customer_id = l.customer_id where l.overdue_days is not null) p2
group by pay_ability) g2

on g1.pay_ability = g2.pay_ability
)p

order by pay_ability desc
;
发表于 2025-01-16 14:27:24 回复(0)
Mysql
不要做小白头像 不要做小白
select pay_ability,
concat(round(count(overdue_days)/count(*)*100,1),"%") as overdue_ratio
from loan_tb a right join customer_tb b
on a.customer_id=b.customer_id and overdue_days is not null
group by pay_ability
order by overdue_ratio desc
发表于 2024-12-23 16:44:31 回复(0)
Mysql
牛客299952918号头像 牛客299952918号
  1. 首先考虑count(l.overdue_days)自动计数逾期日的非空行，count(*)计数所有行；
  2. 考虑到一个客户可能有多个贷款，客户表左连接贷款表后c.customer_id可能会重复，因此最好用count(DISTINCT c.customer_id)来计算客户数量；
  3. 再考虑到一个客户有多个贷款时，也会有多个逾期日，但我们只需算为一个逾期客户，所以改成计算有逾期日的distinct客户id数。这里要用到条件语句case when then end；
  4. 字符串拼接：concat( ,'%') 加上百分号。 
select
    c.pay_ability,
    concat (
        round(
            count(
                DISTINCT case
                    when l.overdue_days is not NULL then l.customer_id
                END
            ) / count(DISTINCT c.customer_id) * 100,
            1
        ),
        '%'
    ) as overdue_ratio
from
    customer_tb as c
    left join loan_tb as l on c.customer_id = l.customer_id
group by
    c.pay_ability
order by
    overdue_ratio desc


发表于 2024-12-08 11:46:38 回复(0)
Mysql
牛客78712505号头像 牛客78712505号
select pay_ability,
       concat(format((count(overdue_days)/count(pay_ability))*100,1),'%')as overdue_ratio
from loan_tb as l
left join customer_tb as c on l.customer_id=c.customer_id
group by pay_ability 
order by overdue_ratio desc,pay_ability ;
发表于 2024-09-27 11:34:57 回复(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)
提交观点

问题信息

SQL
来自：2023年蚂蚁春招技术...
难度：
8条回答 1160浏览

热门推荐

相关试题

分析客户逾期情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号