一道SQL笔试题
俊红的数据分析之路
共 4903字,需浏览 10分钟
·
2021-09-18 03:03
今天给大家分享某厂一道面试题,附上参考答案,希望能够帮到大家!
◎ 每天的审批通过率及审批通过的平均申请金额
◎ 2018年2-5月份,不同费率的放款笔数、放款金额、30天以上金额逾期率(剩余本金/放款金额)
◎ 所有放款客户中,不同客群类型的人数占比
申请表 app_list
字段名称:申请日期,合同编号,申请金额,审批结果
apply_date | loan_no | apply_prin | result |
---|---|---|---|
2018/2/5 | GM290114 | 10000 | pass |
2018/2/5 | GM290140 | 10000 | pass |
2018/2/5 | GM290144 | 10000 | pass |
2018/3/1 | GM290923 | 10000 | reject |
2018/3/1 | GM290937 | 10000 | reject |
2018/3/1 | GM290938 | 10000 | pass |
2018/4/17 | GM29571 | 8000 | pass |
参考解答
※ 每天的审批通过率及审批通过的平均申请金额
☆ 解析:
① 每天的 -- 需要将申请日期apply_date
聚合group by
② 审批通过率 -- 计算通过总数除以申请总数。判断result = 'pass'
则为通过,相等则为1,不等则为0,运用求和函数sum()
即可求出通过总数。申请总数可以直接运用计数函数count()
即可。
③ 审批通过的平均申请金额 -- 类似第二条的逻辑,直接用通过金额除以通过总数即可。
SELECT apply_date,
SUM(result = 'pass')/COUNT(loan_no) 审批通过率, -- 别名
SUM((result = 'pass' )*apply_prin)/SUM(result='pass') 审批通过的平均申请金额 -- 别名
FROM app_list
GROUP BY apply_date;
☆ 结果:
apply_date | 审批通过率 | 审批通过的平均申请金额 |
---|---|---|
2018/2/5 | 1 | 10000 |
2018/3/1 | 0.3333 | 10000 |
2018/4/17 | 1 | 8000 |
2018/5/11 | 1 | 6000 |
2018/5/25 | 0.3333 | 15000 |
2018/6/18 | 1 | 1000 |
2018/10/12 | 1 | 12000 |
2018/11/5 | 0.6667 | 20000 |
放款表 loan_list
字段名称:放款日期,合同编号,身份证号,放款金额,已还本金,消费等级,预期天数
loan_date | loan_no | id_no | loan_prin | paid_principal | product_rate | overdue_days |
---|---|---|---|---|---|---|
2018/2/5 | GM290144 | 1100001990 | 10000 | 8000 | A | NULL |
2018/4/17 | GM296833 | 5500001992 | 8000 | 1500 | D | 11 |
2018/5/11 | GM310938 | 2300001991 | 6000 | 5500 | D | NULL |
2018/6/18 | GM350939 | 4500001989 | 1000 | 0 | B | 432 |
2018/4/18 | GM296834 | 5100001992 | 6000 | 1500 | D | 31 |
2018/4/20 | GM296894 | 5100001982 | 60000 | 15000 | D | 40 |
2018/3/20 | GM296874 | 5100001987 | 13000 | 10000 | D | 60 |
※ 2018年2-5月份,不同费率的放款笔数、放款金额、30天以上金额逾期率(剩余本金/放款金额)
☆ 解析:
① 2018年2-5月份 -- 通过where
筛选即可。
② 放款笔数、放款金额 -- 分别使用计数函数count()
和求和函数sum()
即可。
③ 30天以上金额逾期率(剩余本金/放款金额)
逾期30天以上 -- overdue_days>=30
剩余本金 -- 放款金额减去已还本金 loan_prin - paid_principal
上面两条相乘并求和,即可得到逾期30天以上剩余本金 通过字表查出2018年2-5月份内放款金额总数
select product_rate,
count(loan_no) 放款笔数,
sum(loan_prin) 放款金额,
ifnull(sum((loan_prin - paid_principal)*(overdue_days>=30))/
(select sum(loan_prin)
from loan_list
where month(loan_date) between 2 and 5 -- 时间筛选
and year(loan_date) = 2018),0) 30天以上金额逾期率
from loan_list
where month(loan_date) between 2 and 5 and year(loan_date) = 2018
group by product_rate;
☆ 结果
product_rate | 放款笔数 | 放款金额 | 30天以上金额逾期率 |
---|---|---|---|
A | 1 | 10000 | 0 |
D | 5 | 93000 | 0.5097 |
客户信息表 customer
字段名称:身份证号,客群类型,年龄
id_no | groupp | age |
---|---|---|
1100001990 | house | 29 |
5500001992 | creditcard | 27 |
2300001991 | creditcard | 28 |
4500001989 | creditcard | 30 |
4500001988 | house | 31 |
5100001992 | car | 46 |
5100001982 | car | 35 |
5100001987 | house | 31 |
※ 所有放款客户中,不同客群类型的人数占比
☆ 解析:
① 放款客户和客群类型分别属于放款表和客户信息表,因此需要用到表链接,链接字段为身份证号id_no
。
② 不同人数占比 -- 放款客户去重计数,除以所有客户总数(通过字表查询)
SELECT groupp,
COUNT(distinct loan_list.id_no)/
(SELECT count(distinct id_no)
FROM customer) 人数占比
FROM loan_list left JOIN customer
ON loan_list.id_no = customer.id_no
GROUP BY groupp;
☆ 结果:
groupp | 人数占比 |
---|---|
car | 0.25 |
creditcard | 0.375 |
house | 0.25 |
建表与导数
为方便小伙伴们操作联系,数据库建表和导入数据代码给你贴出来了。
-- create database STUDIO;
use STUDIO;
create table app_list
(apply_date date,
loan_no varchar(10) primary key,
apply_prin int,
result varchar(10));
insert into app_list values
("2018-2-5","GM290144",10000,"pass"),
("2018-3-1","GM290937",10000,"reject"),
("2018-4-17","GM296833",8000,"pass"),
("2018-5-11","GM310938",6000,"pass"),
("2018-5-25","GM327400",15000,"reject"),
("2018-6-18","GM350939",1000,"pass"),
("2018-10-12","GM380936",12000,"pass"),
("2018-11-5","GM400940",20000,"reject"),
("2018-2-5","GM290140",10000,"pass"),
("2018-3-1","GM290938",10000,"pass"),
("2018-4-17","GM296843",8000,"pass"),
("2018-5-11","GM310939",6000,"pass"),
("2018-5-25","GM327401",15000,"pass"),
("2018-6-18","GM350966",1000,"pass"),
("2018-10-12","GM380976",12000,"pass"),
("2018-11-5","GM400949",20000,"pass"),
("2018-2-5","GM290114",10000,"pass"),
("2018-3-1","GM290923",10000,"reject"),
("2018-4-17","GM29571",8000,"pass"),
("2018-5-11","GM310928",6000,"pass"),
("2018-5-25","GM32411",15000,"reject"),
("2018-6-18","GM351939",1000,"pass"),
("2018-10-12","GM376936",12000,"pass"),
("2018-11-5","GM441940",20000,"pass");
select * from app_list;
create table loan_list
(loan_date date,
loan_no varchar(15),
id_no varchar(25),
loan_prin int,
paid_principal int,
product_rate varchar(2),
overdue_days int);
insert into loan_list values
("2018-2-5","GM290144","1100001990",10000,8000,"A",null),
("2018-4-17","GM296833","5500001992",8000,1500,"D",11),
("2018-5-11","GM310938","2300001991",6000,5500,"D",null),
("2018-6-18","GM350939","4500001989",1000,0,"B",432),
("2018-4-18","GM296834","5100001992",6000,1500,"D",31),
("2018-4-20","GM296894","5100001982",60000,15000,"D",40),
("2018-3-20","GM296874","5100001987",13000,10000,"D",60);
select * from loan_list;
create table customer(id_no varchar(25),
groupp varchar(25),
age int);
insert into customer values
("1100001990","house",29),
("5500001992","creditcard",27),
("2300001991","creditcard",28),
("4500001989","creditcard",30),
("4500001988","house",31),
("5100001992","car",46),
("5100001982","car",35),
("5100001987","house",31);
select * from customer;
![](https://filescdn.proginn.com/6d2ffc44ae525cc7eac692537d56b34e/9d4c65ec6b9b08260e35fa66db8dc7a3.webp)
点分享 点收藏 点点赞 点在看
评论