create database cda;
use cda;

create table cmn_investment_request(
Created_at datetime,
User_id varchar(10),
invest_item varchar(10),
invest_amount decimal(38,10)
);

create table dim_agent(
User_id varchar(10),
Start_date datetime,
End_date datetime,
Agent_id varchar(10)
);

insert into cmn_investment_request values
('2017-11-01 01:32:00','A123','CFH',100000),
('2017-12-25 03:42:00','A123','AX',450000),
('2017-12-11 17:42:00','A123','CH',700000),
('2017-12-06 20:06:00','B456','CFH',1500000),
('2017-12-16 14:32:00','B456','AX',800000),
('2017-12-26 17:22:00','B456','AX',600000),
('2018-11-01 14:32:00','C789','JUIN',300000);

insert into dim_agent values
('A123','2016-01-01 00:00:00','2017-12-04 23:59:59',10001),
('A123','2017-12-05 00:00:00','3001-12-31 23:59:59',10002),
('B456','2015-10-31 00:00:00','2016-12-15 23:59:59',10001),
('B456','2016-12-16 00:00:00','3001-12-31 23:59:59',10003),
('C789','2015-01-01 00:00:00','3001-12-31 23:59:59',10002);

select * from cmn_investment_request;
select * from dim_agent;


#1、计算2017年每笔投资均大于50万的用户
select User_id from cmn_investment_request
where year(Created_at) = 2017
group by User_id
having min(invest_amount) > 500000;


#2、计算2017年仅投资过CFH和AX产品的用户
select User_id 
from cmn_investment_request
where year(Created_at) = 2017
group by User_id
having  group_concat(distinct invest_item) in ('AX,CFH','CFH,AX');

select User_id 
from cmn_investment_request
where year(Created_at) = 2017
group by User_id
having  group_concat(distinct invest_item order by invest_item) = ('AX,CFH');

select user_id
from cmn_investment_request
where year(created_at)=2017
group by user_id
having avg(invest_item in('CFH','AX'))=1
and count(distinct invest_item) = 2;

#3、计算归属于10002业务员的投资金额
select Agent_id,sum(invest_amount)
from cmn_investment_request c
left join dim_agent d
on c.User_id = d.User_id
and Created_at between Start_date and End_date
group by Agent_id
having Agent_id = 10002;