signed

QiShunwang

“诚信为本、客户至上”

SQL - Daily Practice3

2020/12/28 23:29:57   来源:

Leetcode 1141 查询近30天活跃用户数

活动记录表:Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
该表是用户在社交网站的活动记录。
该表没有主键,可能包含重复数据。
activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
每个 session_id 只属于一个用户。

请写SQL查询出截至 2019-07-27(包含2019-07-27),近 30天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。

查询结果示例如下:

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   |
+---------+------------+---------------+---------------+

Result table:
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
非活跃用户的记录不需要展示。

这道题目的筛选条件与日期有关,所以肯定要用到日期相关的函数。我不推荐 hard code 起始日期,因为这样还需要自己计算。我们应该利用 DBMS 自带的函数去处理日期。根据题目所知,近30日意味着一个有30日的时间区间。在 MySQL 里,我们可以用 DATE_SUB 函数或者 DATEDIFF 函数。


如果用的是 `DATE_SUB` 函数,那么我们配搭 `INTERVAL` 关键字。注意,`DATE('2019-07-27'), INTERVAL 29 DAY` 代表的区间范围是 2019-07-27 到 2019-07-27 的前29天,区间范围大小为30。如果改写成 `DATE('2019-07-27'), INTERVAL 30 DAY` 是错误的,因为这就表示区间 2019-07-27 和 2019-07-27 的前30天,区间范围大小为31。
select activity_date as day, count(distinct user_id) as active_users
from Activity
where activity_date >= DATE_SUB(DATE('2019-07-27'), INTERVAL 29 DAY) 
group by activity_date;

同理,利用 DATEDIFF 的话,如果用的是小于号,那么小于号右手边的数字为30。如果用的小于等于号,小于等于号右手边的数字为29。

select activity_date as day, count(distinct user_id) as active_users
from Activity
where DATEDIFF('2019-07-27', activity_date) < 30
group by activity_date;