牛客 每日新用户登录数， 每日新用户次日留存率

 

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for login
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `login`;
 8 CREATE TABLE `login`  (
 9   `id` int NOT NULL,
10   `user_id` int NOT NULL,
11   `client_id` int NOT NULL,
12   `date` date NOT NULL,
13   PRIMARY KEY (`id`) USING BTREE
14 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
15 
16 -- ----------------------------
17 -- Records of login
18 -- ----------------------------
19 INSERT INTO `login` VALUES (1, 2, 1, '2020-10-12');
20 INSERT INTO `login` VALUES (2, 3, 2, '2020-10-12');
21 INSERT INTO `login` VALUES (3, 1, 2, '2020-10-12');
22 INSERT INTO `login` VALUES (4, 2, 2, '2020-10-13');
23 INSERT INTO `login` VALUES (5, 1, 2, '2020-10-13');
24 INSERT INTO `login` VALUES (6, 3, 1, '2020-10-14');
25 INSERT INTO `login` VALUES (7, 4, 1, '2020-10-14');
26 INSERT INTO `login` VALUES (8, 4, 1, '2020-10-15');
27 
28 SET FOREIGN_KEY_CHECKS = 1;


每日新用户数量-我的实现方式

 1 SELECT
 2     t.date,
 3     sum(IF(t.new=0,1,0)  ) `new` 
 4 FROM
 5     (
 6     SELECT
 7         date,
 8         l1.user_id,
 9         ( SELECT COUNT( l2.date ) FROM login l2 WHERE l1.user_id = l2.user_id AND l1.date > l2.date ) `new` 
10     FROM
11         login l1 
12     GROUP BY
13         l1.date,
14         l1.user_id 
15     ) t 
16 GROUP BY
17     t.date 
18 ORDER BY
19     t.date

牛人的实现方式

 1 SELECT 
 2     date,
 3     sum(
 4     CASE
 5             WHEN ( user_id, date ) IN ( SELECT user_id, min( date ) FROM login GROUP BY user_id ) THEN
 6             1 ELSE 0 
 7         END 
 8         ) 
 9     FROM
10         login 
11     GROUP BY
12         date 
13 ORDER BY
14     date;


 每日新用户次日留存率

 1 SELECT
 2     l1.date,
 3     ifnull(
 4         round((
 5                 sum(
 6                 CASE
 7                         
 8                         WHEN ( l1.user_id, l1.date ) IN ( SELECT l2.user_id, date_add( l2.date, INTERVAL - 1 DAY ) FROM login l2 ) 
 9                         AND ( l1.user_id, l1.date ) IN ( SELECT l3.user_id, min( l3.date ) FROM login l3 GROUP BY user_id ) THEN
10                             1 ELSE 0 
11                         END 
12                         ))/ (
13                         sum(
14                         CASE
15                                 
16                                 WHEN ( l1.user_id, l1.date ) IN ( SELECT user_id, min( date ) FROM login GROUP BY user_id ) THEN
17                                 1 ELSE 0 
18                             END 
19                             )),
20                         3 
21                     ),
22                     0 
23                 ) AS p 
24             FROM
25                 login l1
26             GROUP BY
27                 l1.date 
28         ORDER BY
29     l1.date;

 


仅此记录一下

posted @ 2021-04-03 19:31  VVII  阅读(126)  评论(0)    收藏  举报