SQL，力扣标题1107，每日新用户统计

一、力扣链接

LeetCode_1107
二、标题描述

Traffic 表：

1. +---------------+---------+
2. | Column Name   | Type    |
3. +---------------+---------+
4. | user_id       | int     |
5. | activity      | enum    |
6. | activity_date | date    |
7. +---------------+---------+
8. 该表可能有重复的行。
9. activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。

复制代码

编写解决方案，找出从今天起最多 90 天内，每个日期该日期初次登录的用户数。假设今天是 2019-06-30 。
以 任意顺序 返回结果表。
三、目标拆解

四、建表语句

1. Create table If Not Exists Traffic (user_id int, activity ENUM('login', 'logout', 'jobs', 'groups', 'homepage'), activity_date date)
2. Truncate table Traffic
3. insert into Traffic (user_id, activity, activity_date) values ('1', 'login', '2019-05-01')
4. insert into Traffic (user_id, activity, activity_date) values ('1', 'homepage', '2019-05-01')
5. insert into Traffic (user_id, activity, activity_date) values ('1', 'logout', '2019-05-01')
6. insert into Traffic (user_id, activity, activity_date) values ('2', 'login', '2019-06-21')
7. insert into Traffic (user_id, activity, activity_date) values ('2', 'logout', '2019-06-21')
8. insert into Traffic (user_id, activity, activity_date) values ('3', 'login', '2019-01-01')
9. insert into Traffic (user_id, activity, activity_date) values ('3', 'jobs', '2019-01-01')
10. insert into Traffic (user_id, activity, activity_date) values ('3', 'logout', '2019-01-01')
11. insert into Traffic (user_id, activity, activity_date) values ('4', 'login', '2019-06-21')
12. insert into Traffic (user_id, activity, activity_date) values ('4', 'groups', '2019-06-21')
13. insert into Traffic (user_id, activity, activity_date) values ('4', 'logout', '2019-06-21')
14. insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-03-01')
15. insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-03-01')
16. insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-06-21')
17. insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-06-21')

复制代码

五、过程分析

1、找出用户第一天登录的日期

2、找出第一天登录日期与指定日期间隔90天以内的日期，并计算人数

六、代码实现

1. with t1 as(
2. select user_id, activity, activity_date, row_number() over(partition by user_id order by activity_date) rn
3. from Traffic
4. where activity = 'login'
5. )
6. select activity_date login_date, count(user_id) user_count
7. from t1
8. where rn = 1
9. and datediff('2019-06-30', activity_date) <= 90
10. group by activity_date;

复制代码

七、结果验证

八、小结

1、CTE表达式 + 窗口函数 + datediff()
2、思绪：找出每个用户第一天登录的日期之后再进行过滤90天以内的日期

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。