6月份连续3天登录的用户数

明确题意：

统计6月份连续登录至少为3天的用户数

问题拆解：

• 对每个用户每天登录记录去重，一个一个一天只保留一条。知识点：distinct
• 对每个用户的登录日期按顺序编号。知识点：按顺序编号row_number() over(partition by user_id order by login_date)；
• 计算每个用户每次连续的登录天数。知识点：按用户ID、登录日期减去序号（即本次连续登录起始日期）分组group by；统计连续签到天数count()；
• 筛选连续登录天数大于等于3的记录。知识点：having
• 统计满足上述条件的用户数，注意一个用户可能有多次连续签到满足条件。知识点：count(distinct user_id)

代码实现：

select count(distinct user_id) as num
from (
    select user_id, count(*) as continuous_days
    from (
        select user_id, login_date,
            row_number() over(partition by user_id order by login_date) as rn
        from (
            select distinct user_id, login_date
            from logintb
            where login_date between "2021-06-01" and "2021-06-30"
        ) as t_uniq_user_login
    ) as t_login_rn
    group by user_id, DATE_SUB(login_date, INTERVAL rn day)
    having continuous_days>=3
) as t_login_ge3