1. 题目需求
从登录明细表(user_login_detail)中查询出,所有用户的连续登录两天及以上的日期区间,以登录时间(login_ts)为准。
期望结果如下:
2. 需要用到的表:
登录明细表:user_login_detail
3. 查询sql
SELECT DISTINCT
user_id,
first_value (login_ts) OVER (PARTITION BY user_id,subdate ORDER BY login_ts) start_date,
last_value (login_ts) OVER (PARTITION BY user_id,subdate ORDER BY login_ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) end_date
FROM
(
SELECT
user_id,
login_ts,
subdate,
count(*) OVER (PARTITION BY user_id, subdate) count_num
FROM
(
SELECT
user_id,
login_ts,
date_sub (login_ts, rk) subdate
FROM
(
SELECT distinct
user_id,
date_format (login_ts, 'YYYY-MM-dd') login_ts,
rank() over (PARTITION BY user_id order BY login_ts) rk
FROM user_login_detail
) t1
) t2
) t3
WHERE count_num >= 2