一道SQL筆試題

今天給大家分享某廠一道面試題，附上參考答案，希望能夠幫到大家！

◎ 每天的審批通過(guò)率及審批通過(guò)的平均申請(qǐng)金額
◎ 2018年2-5月份，不同費(fèi)率的放款筆數(shù)、放款金額、30天以上金額逾期率（剩余本金/放款金額）
◎ 所有放款客戶中，不同客群類型的人數(shù)占比

申請(qǐng)表 app_list

字段名稱：申請(qǐng)日期，合同編號(hào)，申請(qǐng)金額，審批結(jié)果

參考解答

※ 每天的審批通過(guò)率及審批通過(guò)的平均申請(qǐng)金額

☆ 解析:

① 每天的 -- 需要將申請(qǐng)日期apply_date聚合group by

② 審批通過(guò)率 -- 計(jì)算通過(guò)總數(shù)除以申請(qǐng)總數(shù)。判斷result = 'pass'則為通過(guò)，相等則為1，不等則為0，運(yùn)用求和函數(shù)sum()即可求出通過(guò)總數(shù)。申請(qǐng)總數(shù)可以直接運(yùn)用計(jì)數(shù)函數(shù)count()即可。

③ 審批通過(guò)的平均申請(qǐng)金額 -- 類似第二條的邏輯，直接用通過(guò)金額除以通過(guò)總數(shù)即可。

SELECT apply_date, 
       SUM(result = 'pass')/COUNT(loan_no) 審批通過(guò)率,  -- 別名
       SUM((result = 'pass' )*apply_prin)/SUM(result='pass') 審批通過(guò)的平均申請(qǐng)金額 -- 別名
FROM app_list
GROUP BY apply_date;

☆ 結(jié)果：

放款表 loan_list

字段名稱：放款日期，合同編號(hào)，身份證號(hào)，放款金額，已還本金，消費(fèi)等級(jí)，預(yù)期天數(shù)

※ 2018年2-5月份，不同費(fèi)率的放款筆數(shù)、放款金額、30天以上金額逾期率（剩余本金/放款金額）

☆ 解析：

① 2018年2-5月份 -- 通過(guò)where篩選即可。

② 放款筆數(shù)、放款金額 -- 分別使用計(jì)數(shù)函數(shù)count()和求和函數(shù)sum()即可。

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

1. 逾期30天以上 -- overdue_days>=30
2. 剩余本金 -- 放款金額減去已還本金loan_prin - paid_principal
3. 上面兩條相乘并求和，即可得到逾期30天以上剩余本金
4. 通過(guò)字表查出2018年2-5月份內(nèi)放款金額總數(shù)

select product_rate, 
			 count(loan_no) 放款筆數(shù), 
			 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  -- 時(shí)間篩選
               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;

☆ 結(jié)果

客戶信息表 customer

字段名稱：身份證號(hào)，客群類型，年齡

※ 所有放款客戶中，不同客群類型的人數(shù)占比

☆ 解析：

① 放款客戶和客群類型分別屬于放款表和客戶信息表，因此需要用到表鏈接，鏈接字段為身份證號(hào)id_no。

② 不同人數(shù)占比 -- 放款客戶去重計(jì)數(shù)，除以所有客戶總數(shù)（通過(guò)字表查詢）

SELECT groupp, 
			 COUNT(distinct loan_list.id_no)/
			 (SELECT count(distinct id_no) 
        FROM customer) 人數(shù)占比
FROM loan_list left JOIN customer
ON loan_list.id_no = customer.id_no
GROUP BY groupp;

☆ 結(jié)果:

建表與導(dǎo)數(shù)

為方便小伙伴們操作聯(lián)系，數(shù)據(jù)庫(kù)建表和導(dǎo)入數(shù)據(jù)代碼給你貼出來(lái)了。

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

點(diǎn)擊下方卡片進(jìn)行關(guān)注，獲取更多內(nèi)容

點(diǎn)分享
點(diǎn)收藏
點(diǎn)點(diǎn)贊
點(diǎn)在看