SQL求某天用户在之后每天的留存率
2022.1.1打车用户在之后每天的留存率
建表插入数据
CREATE TABLE `test_order` (
`User_id` int DEFAULT NULL,
`Order_id` int DEFAULT NULL,
`Call_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=armscii8;
INSERT INTO `plant_week_local_test`.`test_order`(`User_id`, `Order_id`, `Call_time`) VALUES (1001, 1000001, '2022-01-01 20:54:16');
INSERT INTO `plant_week_local_test`.`test_order`(`User_id`, `Order_id`, `Call_time`) VALUES (1002, 1000002, '2022-01-01 20:54:16');
INSERT INTO `plant_week_local_test`.`test_order`(`User_id`, `Order_id`, `Call_time`) VALUES (1001, 1000003, '2022-01-02 20:54:16');
INSERT INTO `plant_week_local_test`.`test_order`(`User_id`, `Order_id`, `Call_time`) VALUES (1001, 1000004, '2022-01-04 20:54:16');
INSERT INTO `plant_week_local_test`.`test_order`(`User_id`, `Order_id`, `Call_time`) VALUES (1002, 1000005, '2022-01-04 20:54:16');
算法
SELECT
Call_time,
count( DISTINCT User_id ) `2020.1.1打车用户在当天的用户数`,
( SELECT count( DISTINCT User_id ) FROM test_order WHERE DATE_FORMAT( Call_time, '%Y-%m-%d' ) = '2022-01-01' ) AS `1号登录总人数` ,
count( DISTINCT User_id )/( SELECT count( DISTINCT User_id ) FROM test_order WHERE DATE_FORMAT( Call_time, '%Y-%m-%d' ) = '2022-01-01' ) AS `2020.1.1打车用户在之后每天的留存率`
FROM
test_order
WHERE
User_id IN ( SELECT DISTINCT User_id FROM test_order WHERE DATE_FORMAT( Call_time, '%Y-%m-%d' ) = '2022-01-01' )
GROUP BY
Call_time
