有贷款信息表：loan_tb（agreement_id：合同id，customer_id：客户id，loan_amount：贷款金额，pay_amount：已还金额，overdue_days：逾期天数） 客户信息表：customer_tb（customer_id：客户id，customer_age：客户年龄，pay_ability：还款能力级别） 请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比？ 要求输出还款能力级别、逾期客户占比； 注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序； 示例数据结果如下： 结果解释： 还款能力级别为 C 的客户有1113、1116、1119，其中有逾期行为的客户为 1113、1119，故结果为 23=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%
加载中...