MySQL计算连续3天登陆的用户

解题思路

根据user_id对用户登陆日期进行开窗分组排序，再用登陆日期减去序号rn,如果连续的话，登陆日期减去序号的值应该是相同的。

开始解题

创建数据集

USE demo;
DROP TABLE IF EXISTS login3days;
CREATE TABLE login3days(user_id varchar(20),login_date date);
INSERT INTO login3days VALUES ('a','2020-07-01'),('a','2020-07-02'),('a','2020-07-03'),('b','2020-07-01');
SELECT * FROM login3days;

分析过程

SELECT user_id,login_date,rn,date_add(login_date,interval -rn day) as date_flag
FROM (
	SELECT *
		,row_number() over (partition by user_id order by login_date asc) as rn
	FROM login3days) t

最终答案

SELECT USER_id,count(*) as days_count
FROM (
SELECT *
	,row_number() over (partition by user_id order by login_date asc) as rn
FROM login3days) t
group by user_id,DATE_ADD(login_date,interval -rn day)
HAVING days_count = 3