面试题-SQL-银行问题

1、建表语句

CREATE TABLE report_cust_prod_aum_stats(cust_no VARCHAR2(20),prod_code VARCHAR2(20),aum NUMBER);
INSERT INTO report_cust_prod_aum_stats VALUES('00001','001234','10000');
INSERT INTO report_cust_prod_aum_stats VALUES('00001','000330','5000');
INSERT INTO report_cust_prod_aum_stats VALUES('00002','000397','8000');
INSERT INTO report_cust_prod_aum_stats VALUES('00003','400141','20000');
INSERT INTO report_cust_prod_aum_stats VALUES('00001','400141','2000');
INSERT INTO report_cust_prod_aum_stats VALUES('00004','000600','50000');
COMMIT;

CREATE TABLE dim_product_info(prod_code VARCHAR2(20),prod_name VARCHAR2(20),prod_type VARCHAR(20)); 
INSERT INTO dim_product_info VALUES('001234','国金','活期+');
INSERT INTO dim_product_info VALUES('000330','现金宝','活期+');
INSERT INTO dim_product_info VALUES('000397','全额宝','活期+');
INSERT INTO dim_product_info VALUES('000600','和聚宝','定期+');
INSERT INTO dim_product_info VALUES('400141','广证红锦','短期+');
COMMIT;

SELECT * FROM report_cust_prod_aum_stats;
SELECT * FROM dim_product_info;

问题1

统计每个产品类型总持有余额，持有余额大于5000的客户数；

select t.prod_type, count(t.cust_no) cn
  from (select r.cust_no, d.prod_type, sum(r.aum)
          from report_cust_prod_aum_stats r
          left join dim_product_info d
            on r.prod_code = d.prod_code
         group by r.cust_no, d.prod_type
        having sum(r.aum) > 5000) t
 group by t.prod_type;

问题2

输出每个产品类型持有总余额前10的客户号，根据产品类型持有总余额从大到小排序；

select t1.a, t1.b, t1.sumaum, t1.rn
  from (select t.a,
               t.b,
               t.sumaum,
               rank() over(partition by t.b order by t.sumaum desc) rn
          from (select r.cust_no a, d.prod_type b, sum(r.aum) sumaum
                  from report_cust_prod_aum_stats r
                  left join dim_product_info d
                    on r.prod_code = d.prod_code
                 group by r.cust_no, d.prod_type) t) t1
 where t1.rn <= 10
 order by t1.sumaum desc;

问题3

 统计持有两个及两个以上产品的客户数；

select count(t1.cust_no) cn
from(
select t.cust_no
from report_cust_prod_aum_stats t
group by t.cust_no
having count(t.cust_no) >= 2)t1;

问题4

输出产品与产品之间持有余额的重叠客户数
结果表样式：产品A，产品B，同时持有的客户数（需列举所有在客户产品持有余额统计表中的产品的两两交集）。

--先确定方向是笛卡尔积，一步一步筛选需要的数据；

select t1.prod_code a, t.prod_code b, count(t.cust_no) cn
  from report_cust_prod_aum_stats t, report_cust_prod_aum_stats t1
 where t.prod_code < t1.prod_code
   and t.cust_no = t1.cust_no
 group by t1.prod_code, t.prod_code;