一道SQL笔试题

IT农民工1

于 2021-09-07 08:18:00 发布

阅读量130

点赞数



公众号后台回复“图书“，了解更多号主新书内容
     作者：云朵君
     来源：数据STUDIO

一个数据工作者面试数据相关岗位，SQL查询语句是必不可少的笔试环节，今天云朵君给大家带来了某厂一道面试题，附上参考答案，希望能够帮到大家！

◎ 每天的审批通过率及审批通过的平均申请金额
◎ 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;

◆ ◆ ◆  ◆ ◆
麟哥新书已经在当当上架了，我写了本书：《拿下Offer-数据分析师求职面试指南》，目前当当正在举行活动，大家可以用相当于原价5折的预购价格购买，还是非常划算的：



数据森麟公众号的交流群已经建立，许多小伙伴已经加入其中，感谢大家的支持。大家可以在群里交流关于数据分析&数据挖掘的相关内容，还没有加入的小伙伴可以扫描下方管理员二维码，进群前一定要关注公众号奥，关注后让管理员帮忙拉进群，期待大家的加入。
管理员二维码：
猜你喜欢
● 卧槽！原来爬取B站弹幕这么简单● 厉害了！麟哥新书登顶京东销量排行榜！● 笑死人不偿命的知乎沙雕问题排行榜
● 用Python扒出B站那些“惊为天人”的阿婆主！● 你相信逛B站也能学编程吗