题目
Traffic 表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
该表没有主键,它可能有重复的行。
activity 列是 ENUM 类型,可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。
编写一个 SQL 查询,以查询从今天起最多 90 天内,每个日期该日期首次登录的用户数。假设今天是 2019-06-30.
查询结果格式如下例所示:
Traffic 表:
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1 | login | 2019-05-01 |
| 1 | homepage | 2019-05-01 |
| 1 | logout | 2019-05-01 |
| 2 | login | 2019-06-21 |
| 2 | logout | 2019-06-21 |
| 3 | login | 2019-01-01 |
| 3 | jobs | 2019-01-01 |
| 3 | logout | 2019-01-01 |
| 4 | login | 2019-06-21 |
| 4 | groups | 2019-06-21 |
| 4 | logout | 2019-06-21 |
| 5 | login | 2019-03-01 |
| 5 | logout | 2019-03-01 |
| 5 | login | 2019-06-21 |
| 5 | logout | 2019-06-21 |
+---------+----------+---------------+
Result 表:
+------------+-------------+
| login_date | user_count |
+------------+-------------+
| 2019-05-01 | 1 |
| 2019-06-21 | 2 |
+------------+-------------+
请注意,我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01,因此他不算在 2019-06-21 的的统计内。
解答
先查询每个用户的首次登陆
select T.user_id, min(T.activity_date) as first_date
from Traffic as T
where T.activity = 'login'
group by T.user_id
再去掉超过90 天的情况
select *
from (select T.user_id, min(T.activity_date) as first_date
from Traffic as T
where T.activity = 'login'
group by T.user_id) as tmp
where datediff(tmp.first_date, '2019-06-30') <= 90
然后对activity_date分组 统计user_id的数量即可
select tmp.first_date as login_date, count(tmp.user_id) as user_count
from (select T.user_id, min(T.activity_date) as first_date
from Traffic as T
where T.activity = 'login'
group by T.user_id) as tmp
where datediff(tmp.first_date, '2019-06-30') <= 90
group by tmp.first_date
order by tmp.first_date;
网友评论