连续三天登录用户

 

 

方式一：
SELECT
*
FROM
( SELECT *, lead ( login_data, 2 ) over ( PARTITION BY user_id ORDER BY login_data ) AS rn FROM last_3days ) AS a
WHERE
datediff ( login_data, rn ) =-2

 

 

方式二：
SELECT
*
FROM
( SELECT *, lag ( login_data, 2 ) over ( PARTITION BY user_id ORDER BY login_data ASC ) AS rn FROM last_3days ) AS a
WHERE
datediff( login_data, rn ) = 2;