mysql如何查询连续签到的多天的用户

CREATE TABLE IF NOT EXISTS `user_activity` (
  `user_id` varchar(20) DEFAULT NULL,
  `activity_date` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `user_activity` DISABLE KEYS */;
INSERT IGNORE INTO `user_activity` (`user_id`, `activity_date`) VALUES
	('user1', '2023-03-01'),
	('user2', '2023-03-02'),
	('user3', '2023-03-03'),
	('user4', '2023-03-04'),
	('user1', '2023-03-08'),
	('user2', '2023-03-08'),
	('user5', '2023-03-08'),
	('user6', '2023-03-08'),
	('user3', '2023-03-09'),
	('user5', '2023-03-09'),
	('user6', '2023-03-09'),
	('user7', '2023-03-09'),
	('user3', '2023-03-10'),
	('user5', '2023-03-10'),
	('user6', '2023-03-10'),
	('user7', '2023-03-10'),
	('user5', '2023-03-11'),
	('user6', '2023-03-11'),
	('user7', '2023-03-11'),
	('user6', '2023-03-12'),
	('user7', '2023-03-12'),
	('user7', '2023-03-13'),
	('user8', '2023-03-13'),
	('user7', '2023-03-14'),
	('user8', '2023-03-14'),
	('user7', '2023-03-15'),
	('user8', '2023-03-15'),
	('user8', '2023-03-16'),
	('user9', '2023-03-16'),
	('user9', '2023-03-11'),
	('user9', '2023-03-10'),
	('user9', '2023-03-12'),
	('user9', '2023-03-13');

创建表数据如上，需要查询连续签到多天的

可以先通过查询当前天和上一天的数据

 SELECT  
    user_id,  
    activity_date,  
    (SELECT MAX(activity_date) FROM user_activity WHERE user_id = sub.user_id AND activity_date < sub.activity_date) AS prev_login_date  
  FROM user_activity sub  

得到如下数据

然后再对数据进行计算

 SELECT  
  user_id,  
  activity_date,  
  COUNT(*) AS consecutive_days  
FROM (  
  SELECT  
    user_id,  
    activity_date,  
    (SELECT MAX(activity_date) FROM user_activity WHERE user_id = sub.user_id AND activity_date < sub.activity_date) AS prev_login_date  
  FROM user_activity sub  
) AS subquery  
WHERE activity_date - INTERVAL 1 DAY = prev_login_date  
GROUP BY user_id, activity_date

得到如下数据:

得到这个数据后 就可以算出，在进行分个组就可以得到签到几天的人数了