CREATE DATABASE t_view;
use t_view;
CREATE TABLE t_view (
	访问_id int,
	访问时间 varchar(255),
	访问用户id int,
	访问页面名称 varchar(255)
);

INSERT INTO t_view VALUES (1, '2023-01-01 13:44:39', 1111, '首页');
INSERT INTO t_view VALUES (2, '2023-01-01 16:44:39',  1111, '创建订单页');
INSERT INTO t_view VALUES (3, '2023-01-01 12:44:08',  2222, '首页');
INSERT INTO t_view VALUES (4, '2023-01-02 08:44:39',  3333, '首页');
INSERT INTO t_view VALUES (5, '2023-01-03 13:16:13',  4444, '首页');
INSERT INTO t_view VALUES (6, '2023-01-02 14:44:08',  3333, '创建订单页');
INSERT INTO t_view VALUES (7, '2023-01-01 13:08:39',  2222, '创建订单页');
INSERT INTO t_view VALUES (8, '2023-01-04 13:16:14',  1111, '首页');
INSERT INTO t_view VALUES (9, '2023-01-04 14:44:13',  3333, '首页');
INSERT INTO t_view VALUES (10, '2023-01-03 13:16:39',  2222, '首页');
INSERT INTO t_view VALUES (11, '2023-01-03 13:30:00',  2222, '创建订单页');
 



CREATE DATABASE ord;
use ord;
CREATE TABLE ord (
    订单_id int,
    支付用户id varchar(255),
    套餐内容 varchar(255),
    下单日期 date NOT NULL,
    订单状态  varchar(255)
);
INSERT INTO ord VALUES (10080, 2222,'套餐一', '2023-01-03 14:00:00','支付完成');
INSERT INTO ord VALUES (10081, 3333,'套餐一', '2023-01-04 15:23:00','支付完成');
INSERT INTO ord VALUES (10083, 2222,'套餐二', '2023-01-01 13:28:33','支付完成');
INSERT INTO ord VALUES (10084, 5555,'套餐二', '2023-01-01 13:28:33','支付完成');
INSERT INTO ord VALUES (10085, 1111,'套餐一', '2023-01-01 14:00:00','支付完成');
INSERT INTO ord VALUES (10086, 1111,'套餐一', '2023-01-04 15:23:00','取消');


SELECT * FROM t_view;
SELECT * FROM ord;



笔试解答
##问题一：请分别计算“每日访问用户数”；“每日支付完成用户数”，最后结果输出到一张表
 
SELECT  dt,
 count(DISTINCT case when type='访问用户' then userid end) as  `每日访问用户数`,
 count(DISTINCT case when type='支付用户' then userid end) as `每日支付完成用户数`
 from
(select `支付用户id` as userid,
`下单日期` as dt,
'支付用户' as type
from ord.ord where 订单状态='支付完成'
union all
SELECT `访问用户id` as userid,
date(`访问时间`) as dt,
'访问用户' as type
from t_view.t_view
) t1
group by 1
 
##问题二：计算访问用户下单转化，即每天访问的用户中有多少下单的用户，输出：日期、当天访问用户、当天访问下单用户数
SELECT date(`访问时间`) as `日期`,
count(DISTINCT `访问用户id`) as `当天访问用户`,
count(DISTINCT `支付用户id`) as `当天访问下单用户数`
from
(SELECT * FROM t_view.t_view) t1
left join
(SELECT * FROM ord.ord where `订单状态`='支付完成') t2
on date(`访问时间`)=`下单日期`
and `访问用户id`=`支付用户id`
##问题三：计算2023-01-01的的下单新用户中有多少复购的用户，复购的定义为有支付完成第二单
SELECT count(DISTINCT t2.`支付用户id`)
from
(select * from ord.ord where `下单日期`='2023-01-01' and `订单状态`='支付完成' ) t1
left join
(select * from ord.ord where `下单日期`>'2023-01-01' and `订单状态`='支付完成') t2
on t1.`支付用户id`=t2.`支付用户id`