高阶SQL

-- 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