SQL，力扣标题1107，每日新用户统计
一、力扣链接LeetCode_1107
二、标题描述
Traffic 表：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
该表可能有重复的行。
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。
编写解决方案，找出从今天起最多 90 天内，每个日期该日期初次登录的用户数。假设今天是 2019-06-30 。
以 任意顺序 返回结果表。
三、目标拆解
https://i-blog.csdnimg.cn/direct/bd98d8c63165481c977628fd751e9633.png
四、建表语句
Create table If Not Exists Traffic (user_id int, activity ENUM('login', 'logout', 'jobs', 'groups', 'homepage'), activity_date date)
Truncate table Traffic
insert into Traffic (user_id, activity, activity_date) values ('1', 'login', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('1', 'homepage', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('1', 'logout', '2019-05-01')
insert into Traffic (user_id, activity, activity_date) values ('2', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('2', 'logout', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('3', 'login', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('3', 'jobs', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('3', 'logout', '2019-01-01')
insert into Traffic (user_id, activity, activity_date) values ('4', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('4', 'groups', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('4', 'logout', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-03-01')
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-03-01')
insert into Traffic (user_id, activity, activity_date) values ('5', 'login', '2019-06-21')
insert into Traffic (user_id, activity, activity_date) values ('5', 'logout', '2019-06-21') 五、过程分析
1、找出用户第一天登录的日期
https://i-blog.csdnimg.cn/direct/a1683671dca847f5afca3f297f1f004c.png
2、找出第一天登录日期与指定日期间隔90天以内的日期，并计算人数
https://i-blog.csdnimg.cn/direct/e2e5639ba8644be0aa26c2d87e1d2afd.png
六、代码实现
with t1 as(
select user_id, activity, activity_date, row_number() over(partition by user_id order by activity_date) rn
from Traffic
where activity = 'login'
)
select activity_date login_date, count(user_id) user_count
from t1
where rn = 1
and datediff('2019-06-30', activity_date) <= 90
group by activity_date; 七、结果验证
https://i-blog.csdnimg.cn/direct/b7eb19cffd9b46869b7da8fda93b300d.png
八、小结
1、CTE表达式 + 窗口函数 + datediff()
2、思绪：找出每个用户第一天登录的日期之后再进行过滤90天以内的日期
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]