step1:将2021年6月份所有的用户根据user_id进行分组并按登录时间升序排序后进行编号,编号记为rn;
step2:按照user_id及login_date-rn进行分组,过滤连续登录天数大于3天的用户;
step3:统计满足要求的所有的用户数。
row_number() over(partition by col1 order by col2)的含义为对先按col1进行分组,再按col2进行排序的结果依次进行编号。
SELECT count(*) AS num
FROM(
SELECT user_id, count(*) as days_count
FROM (
SELECT *
,row_number() over (partition by user_id order by login_date asc) as rn
FROM logintb WHERE DATE_FORMAT(login_date, '%Y-%m')='2021-06') t
group by user_id,DATE_ADD(login_date,interval - rn day)
HAVING days_count >= 3
) t1;