回忆是一座桥
却是通往寂寞的牢

SQL案例 - 分析客户逾期情况

描述

有贷款信息表: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%

答案

解法1:

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

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 分析客户逾期情况
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论