原题链接:http://practice.atguigu.cn/#/question/21/desc?qType=SQL
题目需求
从登录明细表(user_login_detail)中查询每个用户两个登录日期(以login_ts为准)之间的最大的空档期。统计最大空档期时,用户最后一次登录至今的空档也要考虑在内,假设今天为2021-10-10。
期望结果如下:
user_id(用户id) | max_diff(最大空档期) |
---|---|
101 | 10 |
102 | 9 |
103 | 10 |
104 | 9 |
105 | 6 |
106 | 5 |
107 | 10 |
108 | 4 |
109 | 10 |
1010 | 12 |
需要用到的表:
用户登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
---|---|---|---|
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
解题思路
SELECT user_id,
MAX(diff) AS max_diff
FROM
(
SELECT user_id,
DATEDIFF(LEAD(date(login_ts),1,'2021-10-10') OVER (PARTITION BY user_id ORDER BY login_ts ASC),date(login_ts)) AS diff
FROM user_login_detail
) t1
GROUP BY user_id