题解 | 查询连续登陆的用户
查询连续登陆的用户
https://www.nowcoder.com/practice/9944210610ec417e94140ac09512a3f5
WITH t1 AS(
SELECT user_id, log_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_time) AS rn
FROM login_tb
),
t2 AS(
SELECT user_id, log_time, DATE_SUB(DATE(log_time), INTERVAL rn DAY) AS grp
FROM t1
),
t3 AS(
SELECT user_id, grp, COUNT(*) AS log_days
FROM t2
GROUP BY user_id, grp
)
SELECT t3.user_id
FROM t3
INNER JOIN register_tb r ON t3.user_id = r.user_id
WHERE log_days >= 3
ORDER BY user_id;
投递韶音科技等公司7个岗位
查看30道真题和解析
