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,group_concat(distinct invest_item order by invest_item )

from cmn_investment_request

where year(created_at)="2017"

group by user_id having group_concat(distinct invest_item)="AX,CFH";

#3、计算归属于10002业务员的投资金额

select 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 where Agent_id="10002";