SQL面试题-手写连续3日登录

环境

阿里云MaxCompute

初始化数据

CREATE TABLE user_login
(
    user_id     BIGINT COMMENT '用户ID'
    ,login_date STRING COMMENT '登录日期'
)
COMMENT '用户登录日志'
;

INSERT OVERWRITE TABLE user_login
SELECT  1
        ,'2023-08-02'
UNION ALL
SELECT  1
        ,'2023-08-03'
UNION ALL
SELECT  1
        ,'2023-08-04'
UNION ALL
SELECT  2
        ,'2023-11-02'
UNION ALL
SELECT  2
        ,'2023-12-09'
UNION ALL
SELECT  3
        ,'2023-01-01'
UNION ALL
SELECT  3
        ,'2023-04-23'
UNION ALL
SELECT  3
        ,'2023-09-10'
UNION ALL
SELECT  3
        ,'2023-09-11'
UNION ALL
SELECT  3
        ,'2023-09-12'
UNION ALL
SELECT  4
        ,'2023-08-02'
UNION ALL
SELECT  4
        ,'2023-08-04'
UNION ALL
SELECT  4
        ,'2023-08-05'
UNION ALL
SELECT  4
        ,'2023-08-07'
;

方法一

SELECT  user_id
        ,COUNT(1) AS cnt
FROM    (
            SELECT  user_id
                    ,login_date
                    ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date ) AS rk
            FROM    user_login 
        ) t
GROUP BY user_id
         ,DATE_SUB(login_date, t.rk)
HAVING  COUNT(1) >= 3;

--或者 HAVING  cnt >= 3;

方法二

SELECT  a.user_id
FROM    (
            SELECT  user_id
                    ,login_date
                    ,LEAD(login_date,1) OVER (PARTITION BY user_id ORDER BY login_date ) AS lead_1day
                    ,LEAD(login_date,2) OVER (PARTITION BY user_id ORDER BY login_date ) AS lead_2day
            FROM    user_login
        ) AS a
WHERE   DATEDIFF(lead_2day, lead_1day) = 1
AND     DATEDIFF(lead_1day, login_date) = 1
;