查询每个用户登录日期的最大空档期
题目需求
从登录明细表(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 |
代码实现
select
user_id,
max(diff) max_diff
from
(
select
user_id,
login_date,
datediff(next_login_date, login_date) diff
from
(
select
user_id,
login_date,
lead(login_date, 1, '2021-10-10') over (partition by user_id order by login_date) next_login_date
from
(
select
user_id,
date_format(login_ts, 'yyyy-MM-dd') login_date
from user_login_detail
group by user_id, date_format(login_ts, 'yyyy-MM-dd')
)t1
)t2
)t3
group by user_id;