【SQL】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 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

以 任意顺序 返回结果表。

结果示例如下。

示例 1:

输入：
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
输出：
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
解释：注意非活跃用户的记录不需要展示。

分析

统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数

包含2019-07-27，即2019-06-28到2019-07-27这个时间段，可以直接使用between and

为泛化考虑，仅引入截止时间和时间段长度，可以考虑两种办法

datediff方法

遍历所有时间，与截止时间差的绝对值在0到29，datediff("2019-07-27",activity_date) between 0 and 29

date_add方法

简化计算量，通过date_add计算起始时间点再between and 截止时间，这样不用逐一遍历所有时间，activity_date between date_add("2019--07-27",interval -29 day) and "2019-07-27"

近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）

通过distinct加count统计活跃用户数，count(distinct user_id) 

代码

select activity_date day, count(distinct user_id) active_users
from Activity
-- where datediff("2019-07-27",activity_date) between 0 and 29
where activity_date between date_add("2019--07-27",interval -29 day) and "2019-07-27"
group by activity_date