目录
类型2:取出连续登录的日期区间(取出连续区间的开始和结束数字)
类型1:取出连续N天登录的用户名
题目:
写一个 SQL 查询, 找到活跃用户的 id 和 name,活跃用户是指那些至少连续 5 天登录账户的用户,返回的结果表按照 id 排序
答案:
SELECT DISTINCT b.id, name
FROM
(SELECT id, login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY id ORDER BY login_date) DAY) AS diff
FROM
(SELECT DISTINCT id, login_date
FROM Logins) a) b
INNER JOIN Accounts ac
ON b.id = ac.id
GROUP BY b.id, diff
HAVING COUNT(b.id) >= 5
结果表: