MySQL queries users logged in for at least N consecutive days

data

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for orde
-- ----------------------------
DROP TABLE IF EXISTS `orde`;
CREATE TABLE `orde` (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `date` datetime NOT NULL,
  `orders` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of orde
-- ----------------------------
BEGIN;
INSERT INTO `orde` VALUES ('1', '2022-01-01 00:00:00', '101');
INSERT INTO `orde` VALUES ('1', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('1', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('1', '2022-01-04 00:00:00', '991');
INSERT INTO `orde` VALUES ('1', '2022-01-05 00:00:00', '145');
INSERT INTO `orde` VALUES ('1', '2022-01-06 00:00:00', '155');
INSERT INTO `orde` VALUES ('1', '2022-01-07 00:00:00', '199');
INSERT INTO `orde` VALUES ('1', '2022-01-08 00:00:00', '188');
INSERT INTO `orde` VALUES ('2', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('3', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('4', '2022-01-01 00:00:00', '100');
INSERT INTO `orde` VALUES ('4', '2022-01-04 00:00:00', '199');
INSERT INTO `orde` VALUES ('5', '2022-01-05 00:00:00', '145');
INSERT INTO `orde` VALUES ('6', '2022-01-06 00:00:00', '1455');
INSERT INTO `orde` VALUES ('7', '2022-01-07 00:00:00', '199');
INSERT INTO `orde` VALUES ('8', '2022-01-08 00:00:00', '188');
INSERT INTO `orde` VALUES ('9', '2022-01-01 00:00:00', '110');
INSERT INTO `orde` VALUES ('9', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('9', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('9', '2022-01-04 00:00:00', '919');
INSERT INTO `orde` VALUES ('9', '2022-01-06 00:00:00', '145');
INSERT INTO `orde` VALUES ('9', '2022-01-09 00:00:00', '455');
INSERT INTO `orde` VALUES ('9', '2022-01-10 00:00:00', '199');
INSERT INTO `orde` VALUES ('9', '2022-01-13 00:00:00', '188');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

ranking

SELECT 
	*,
	ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
FROM orde

Ideas ： Consecutive date - Continuous sort value = Fix a date
In this way, we can count how many consecutive dates there are

SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp

And then according to dateSub, id Count the number in groups
Calculate the earliest and latest time of continuous time period

SELECT 
	tmp2.id,
	min(tmp2.date) startDate,
	max(tmp2.date) endDate,
	count(*) count
FROM
 (SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp) tmp2
GROUP BY tmp2.dateSub, tmp2.id

Add judgment , Specifies to get continuous data 3 More than days of data

SELECT 
	tmp2.id,
	min(tmp2.date) startDate,
	max(tmp2.date) endDate,
	count(*) count
FROM
 (SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp) tmp2
GROUP BY tmp2.dateSub, tmp2.id
HAVING count(*) > 3