1109课后作业

一、customer-account-trade

客户表customer

字段	数据类型	描述
cust_id	varchar2	客户号
cust_nm	varchar2	客户名称
sex	varchar2	性别
company	varchar2	工作单位

cust_id	cust_nm	sex	company
1000100001	任正非	男	华为
1000100002	马云	男	阿里
1000100003	董明珠	女	格力
1000100004	马化腾	男	腾讯
1000100005	李彦宏	男	百度

客户账户表account
一个客户可存在多个账号

字段	数据类型	描述
cust_id	varchar2	客户号
acct_id	varchar2	账号
cur_bal	number	余额

cust_id	acct_id	cur_bal
1000100001	70127630	500,000
1000100001	62142022	1500,000
1000100002	60351056	3000,000
1000100003	70128600	5000,000
1000100003	70131208	1000,000

账户交易表trade

字段	数据类型	描述
acct_id	varchar2	账号
txn_dt	date	交易日期
txn_amt	number	交易金额

acct_id	txn_dt	txn_amt
70127630	20220101	200,000
60351056	20220102	50,000
70128600	20220102	10,000
70131208	20220204	3000
70127630	20220205	60,000

1、请写出sql，查询当前余额大于100w的客户

select t1.cust_id
from account t1
group by t1.cust_id
having sum(t1.cur_bal) > 1000000;

2、请写出sql，查询2022年1月交易金额大于10w的客户

select t1.cust_id
from account t1
inner join trade t2
        on t1.acct_id = t2.acct_id
        and to_char(t2.txn_dt, 'yyyymm') = '202201'
group by t1.cust_id
having sum(t2.txn_amt) > 100000; 

3、请写出sql，查询每个客户交易金额最大的日期及金额

select t3.cust_id
      ,t3.txn_amt
      ,t3.txn_dt
from (
	select t1.cust_id
	      ,t2.txn_amt
	      ,t2.txn_dt
	      --,max(t2.txn_amt)over(partition by t1.cust_id) as max_txn_amt
	      ,rank()over(partition by t1.cust_id order by t2.txn_amt desc) as rn
	from account t1
	inner join trade t2
	        on t1.acct_id = t2.acct_id
     ) t3
--where t3.txn_amt = t3.max_txn_amt; 
where t3.rn = 1;

4、请写出sql，查询每个客户交易日期最近的日期及金额

select t3.cust_id
      ,t3.txn_amt
      ,t3.txn_dt
from (
	select t1.cust_id
	      ,t2.txn_amt
	      ,t2.txn_dt
	      --,max(t2.txn_dt)over(partition by t1.cust_id) as max_txn_dt
	      ,rank()over(partition by t1.cust_id order by t2.txn_dt desc) as rn
	from account t1
	inner join trade t2
	        on t1.acct_id = t2.acct_id
     ) t3
--where t3.txn_dt = t3.max_txn_dt; 
where t3.rn = 1;

二、申请表apply_list\放款表loan_list

字段	数据类型	描述
apply_date	date	申请日期
loan_no	varchar2	合同编号
apply_prin	number	申请金额
result	varchar2	审批结果

apply_date	loan_no	apply_prin	result
2022/2/5	gm290114	10000	pass
2022/2/5	gm290140	10000	pass
2022/2/5	gm290144	10000	pass
2022/3/1	gm290923	10000	reject
2022/3/1	gm290937	10000	reject
2022/3/1	gm290938	10000	pass
2022/4/17	gm29571	8000	pass

1、请写出sql，查询每天的审批通过率及审批通过的平均申请金额

select t2.apply_day
      ,round(t2.sum_apply_pass / t2.count_apply * 100, 2) || '%' as pass_rate
      ,t2.sum_apply_prin / t2.sum_apply_pass as avg_apply_pass_prin
from (
	select truncate(t1.apply_date,'dd') as apply_day
          ,sum(case when t1.result = 'pass' then 1 else 0 end) as sum_apply_pass
          ,count(1) as count_apply
          ,sum(case when t1.result = 'pass' then t1.apply_prin else 0 end) as sum_apply_prin
from apply_list t1
    group by truncate(t1.apply_date,'dd');
	 ) t2

放款表loan_list

字段	数据类型	描述
loan_date	date	放款日期
loan_no	varchar2	合同编号
id_no	varchar2	身份证号
loan_prin	number	放款金额
paid_principal	number	已还本金
product_rate	varchar2	消费等级
overdue_days	number	逾期天数

loan_date	loan_no	id_no	loan_prin	paid_principal	product_rate	overdue_days
2022/2/5	gm290144	1100001990	10000	8000	a	null
2022/4/17	gm296833	5500001992	8000	1500	d	11
2022/5/11	gm310938	2300001991	6000	5500	d	null
2022/5/18	gm350939	4500001989	1000	0	b	432
2022/4/18	gm296834	5100001992	6000	1500	d	31
2022/4/20	gm296894	5100001982	60000	15000	d	40
2022/3/20	gm296874	5100001987	13000	10000	d	60

2、请写出sql，查询2022年2-5月份，不同消费等级的放款笔数、放款金额、30天以上金额逾期率（剩余本金/放款金额）

select t1.product_rate
      ,count(1) as count_loan
      ,sum(t1.loan_prin) as sum_loan_prin
      --,sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin - t1.paid_principal else 0 end) as sum_overdue_prin
      --,sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin else 0 end ) as  sum_loan_prin_of_overdue
      --,round(sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin - t1.paid_principal else 0 end) / sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin end) * 100, 2) || '%' as overdue_rate
      ,nvl(round(sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin - t1.paid_principal else 0 end) / sum(case when nvl(t1.overdue_days,0) > 30 then t1.loan_prin end) * 100, 2),0) || '%' as overdue_rate
from loan_list t1
where to_char(t1.loan_date, 'yyyymm') in ('202202','202203','202203','202204','202205') 
group by t1.product_rate;