描述
有贷款信息表: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;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*