扫码加入数据分析学习群

create database aaa;

use aaa;

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;

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

select

apply_date,

count(case when result='pass' then loan_no end )/count(loan_no) as 审批通过率,

sum(case when result='pass' then apply_prin end )/count(case when result='pass' then loan_no end ) as 审批通过的平均申请金额

from app_list

group by apply_date;

select apply_date,avg(result='pass') 审批通过率,sum((result='pass')*apply_prin)/sum(result='pass') 审批通过的平均申请金额

from app_list

group by apply_date;

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;

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

select

product_rate 费率等级 ,

count(loan_no) 放款笔数,

sum(loan_prin) 放款金额,

sum(case when overdue_days>30 then (loan_prin-paid_principal) end )/sum(loan_prin ) 30天以上金额逾期率

from loan_list

where year(loan_date)=2018 and month(loan_date) in(2,3,4,5)

group by product_rate;

select product_rate,count(loan_no) 放款笔数,sum(loan_prin) 放款金额,ifnull(sum((overdue_days>30)*(loan_prin-paid_principal))/sum(loan_prin),0) 30天以上金额逾期率

from loan_list

where year(loan_date)=2018 and month(loan_date) between 2 and 5

group by product_rate;

create table customer(id_no varchar(25),group_type 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;

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

select group_type,count(distinct a.id_no)/(select count(distinct id_no) from loan_list)

from loan_list a

left join customer b on a.id_no = b.id_no

group by group_type;