首页 > 试题广场 >

分析客户逾期情况

[编程题]分析客户逾期情况
  • 热度指数：35756 时间限制：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%
算法知识视频讲解
添加回答
Sqlite
丞相何故发笑头像 丞相何故发笑 
SELECT
    c.pay_ability,
    CONCAT(
        ROUND(
            SUM(CASE WHEN overdue_days IS NOT NULL THEN 1 ELSE 0 END) * 100.0 /
            COUNT(DISTINCT c.customer_id),
            1
        ),
        '%'
    ) AS overdue_ratio
FROM customer_tb c
LEFT JOIN loan_tb l ON c.customer_id = l.customer_id
GROUP BY c.pay_ability
ORDER BY overdue_ratio DESC;

发表于 2025-05-28 18:25:55 回复(0)
Sqlite
牛客664610992号头像 牛客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)
Sqlite
45度信仰头像 45度信仰 
select b.pay_ability,  concat( round(count(overdue_days)/count(*)*100,1), "%") as overdue_ratio  
from  loan_tb a inner join  customer_tb b 
        on  b.customer_id = a.customer_id
group by b.pay_ability 
order by overdue_ratio DESC

发表于 2024-10-02 21:07:10 回复(0)
Sqlite
Edgecarry头像 Edgecarry
select
    pay_ability,
    concat(round((sum(case when overdue_days is null then 0 else 1 end)/count(pay_ability))*100,1),'%') as overdue_ratio
from
    loan_tb l
    join customer_tb c
    on c.customer_id = l.customer_id
group by
    pay_ability
order by
    overdue_ratio desc
发表于 2024-09-25 09:35:41 回复(0)
Sqlite
一颗宏心头像 一颗宏心
select pay_ability,concat(round(total2/total*100,1),'%') as overdue_ratio
from (
    select c.pay_ability,count(*) as total,sum(if(l.overdue_days is not null,1,0)) as total2
    from customer_tb c inner join loan_tb l
    on c.customer_id = l.customer_id
    group by c.pay_ability
)t
order by overdue_ratio desc

发表于 2024-09-19 21:53:14 回复(0)
Sqlite
apoo头像 apoo
with cte as (
    select l.customer_id, overdue_days, pay_ability,
    case when overdue_days is not null then 1
    else 0 end as bad
    from loan_tb l join customer_tb c
    on l.customer_id = c.customer_id
)

select pay_ability, concat(round(sum(bad)/count(bad) * 100 ,1), '%')as overdue_ratio
from cte
group by pay_ability
order by overdue_ratio desc
发表于 2024-09-18 16:48:13 回复(0)
Sqlite
牛客283236853号头像 牛客283236853号
select pay_ability,
          concat(round((sum(case when overdue_days is null then 0 else 1 end)/count(*))*100,1),"%")  as overdue_ratio 
from loan_tb a,
        customer_tb b 
where a.customer_id = b.customer_id 
group by pay_ability 
order by overdue_ratio desc;

发表于 2024-08-19 17:17:07 回复(0)
提交观点

问题信息

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

热门推荐

相关试题

分析客户逾期情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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