力扣高频SQL 50题(基础版)第二十一题
1141.查询近30天活跃用户数
题目说明
表:Activity
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
±--------------±--------+
该表没有包含重复数据。
activity_type 列是 ENUM(category) 类型, 从 (‘open_session’, ‘end_session’, ‘scroll_down’, ‘send_message’) 取值。
该表记录社交媒体网站的用户活动。
注意,每个会话只属于一个用户。
编写解决方案,统计截至 2019-07-27
(包含2019-07-27),近 30
天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。
以 任意顺序 返回结果表。
思路分析
实现过程
准备数据
Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'))
Truncate table Activity
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'send_message')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session')
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session')
实现方式
select activity_date day,count(distinct user_id) active_users from Activity where activity_date between '2019-06-28' and '2019-07-27' group by activity_date;