-- DATE_SUB(login_date,rn):如果连续则做差后值一样则表示连续活跃
SELECT
USER_ID,
LOGIN_DATE,
DATE_SUB( LOGIN_DATE, RN ) AS DIFF_DATE
FROM
(
SELECT USER_ID,LOGIN_DATE,
ROW_NUMBER ( ) OVER ( PARTITION BY USER_ID ORDER BY LOGIN_DATE ) AS RN
FROM TEST.USER_LOGIN
) T1
select
user_id,
count(diff_date) as total
from
(
select user_id,
login_date,
date_sub(login_date,rn) as diff_date
from(
select
user_id,
login_date,
row_number() over(partition by user_id order by login_date) as rn
from test.user_login
)t1
)t2
group by
user_id,
diff_date
用户的最长连续活跃天数:
WITH tmp AS(
SELECT 1 AS group_id, '2021-03-01' as day
UNION ALL SELECT 1 AS group_id, '2021-03-02' as day
UNION ALL SELECT 1 AS group_id, '2021-03-04' as day
UNION ALL SELECT 1 AS group_id, '2021-03-05' as day
UNION ALL SELECT 1 AS group