[大厂真题]SQL 11 题解 | 查询连续登陆的用户

# 请查询连续登陆不少于3天的新注册用户？要求：输出user_id并升序排序。
# 注册可以一个渠道，但是登入可以另外一个渠道
# 注意表名称
# 时间相减 DATE_SUB(login_date,INTERVAL num day)

with temp as 
(
    SELECT user_id,log_port,DATE_SUB(login_date,INTERVAL num day) as login_dates
    from (
            SELECT user_id,log_port,login_date,
            row_number() over(partition by user_id,log_port order by login_date asc) as num
            from (
                    SELECT DISTINCT  b.user_id,DATE_FORMAT(b.log_time,'%Y-%m-%d') as login_date,log_port
                    FROM login_tb b
            ) t
    ) tt

)

SELECT user_id
from (
        SELECT a.user_id,DATE_FORMAT(a.reg_time,'%Y-%m-%d') as reg_date,a.reg_port,
        b.login_dates
        FROM register_tb a
        inner JOIN temp b
        on a.user_id=b.user_id
       -- and a.reg_port=b.log_port

) t
group by user_id
having count(login_dates)>=3
order by user_id asc