Traffic 表:
Column Name | Type |
---|---|
user_id | int |
activity | enum |
activity_date | date |
编写解决方案,找出从今天起最多 90 天内,每个日期该日期首次登录的用户数。假设今天是 2019-06-30 。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入:
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 |
输出:
login_date | user_count |
---|---|
2019-05-01 | 1 |
2019-06-21 | 2 |
解释:
请注意,我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01,因此他不算在 2019-06-21 的的统计内。
我们是要查询出 2019-06-30 的最近 90 天(包括 90 天)的首次登录用户,有四种情况
最小时间
那个才是首次登录时间SELECT login_date, COUNT(one.user_id) AS user_count
FROM (
SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
AND activity_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
AND user_id NOT IN (
SELECT user_id
FROM Traffic
WHERE activity = 'login'
AND activity_date < DATE_SUB('2019-06-30', INTERVAL 90 DAY)
)
GROUP BY user_id
) AS one
GROUP BY login_date
代码分析:
activity_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
要求分析
里面的 2 和 4 的情况user_id NOT IN (
SELECT user_id
FROM Traffic
WHERE activity = 'login'
AND activity_date < DATE_SUB('2019-06-30', INTERVAL 90 DAY)
)
要求分析
里面的 1 和 3 情况,主要是 3 情况MIN(activity_date)
SELECT login_date, COUNT(one.user_id) AS user_count
FROM (
SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
GROUP BY user_id
HAVING login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
) AS one
GROUP BY login_date
代码分析:
SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
GROUP BY user_id
SELECT user_id, MIN(activity_date) AS login_date
FROM Traffic
WHERE activity = 'login'
GROUP BY user_id
HAVING login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)