SQL笔试题深析

今天给大家分享某厂一道面试题，附上参考答案，希望能够帮到大家！

◎ 每天的审批通过率及审批通过的平均申请金额
◎ 2018年2-5月份，不同费率的放款笔数、放款金额、30天以上金额逾期率（剩余本金/放款金额）
◎ 所有放款客户中，不同客群类型的人数占比

申请表 app_list

字段名称：申请日期，合同编号，申请金额，审批结果

参考解答

※ 每天的审批通过率及审批通过的平均申请金额

☆ 解析:

① 每天的 — 需要将申请日期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_listGROUP BY apply_date;

☆ 结果：

放款表 loan_list

字段名称：放款日期，合同编号，身份证号，放款金额，已还本金，消费等级，预期天数

※ 2018年2-5月份，不同费率的放款笔数、放款金额、30天以上金额逾期率（剩余本金/放款金额）

☆ 解析：

① 2018年2-5月份 — 通过where筛选即可。

② 放款笔数、放款金额 — 分别使用计数函数count()和求和函数sum()即可。

③ 30天以上金额逾期率（剩余本金/放款金额）

1. 逾期30天以上 — overdue_days>=30
2. 剩余本金 — 放款金额减去已还本金loan_prin - paid_principal
3. 上面两条相乘并求和，即可得到逾期30天以上剩余本金
4. 通过字表查出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_listwhere month(loan_date) between 2 and 5 and year(loan_date) = 2018group by product_rate;

☆ 结果

客户信息表 customer

字段名称：身份证号，客群类型，年龄

※ 所有放款客户中，不同客群类型的人数占比

☆ 解析：

① 放款客户和客群类型分别属于放款表和客户信息表，因此需要用到表链接，链接字段为身份证号id_no。

② 不同人数占比 — 放款客户去重计数，除以所有客户总数（通过字表查询）

SELECT groupp, 			 COUNT(distinct loan_list.id_no)/			 (SELECT count(distinct id_no)         FROM customer) 人数占比FROM loan_list left JOIN customerON loan_list.id_no = customer.id_noGROUP BY groupp;

☆ 结果:

建表与导数

为方便小伙伴们操作联系，数据库建表和导入数据代码给你贴出来了。

-- 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;