mysql 查询用户连续3天答题分数等于100分的数据，

Question

mysql 查询用户连续3天答题分数等于100分的数据，
DROP TABLE IF EXISTS t_exam;
CREATE TABLE t_exam (
id int(11) NOT NULL AUTO_INCREMENT,
user_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID',
fraction int(11) NULL DEFAULT NULL COMMENT '分数',
create_time datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO t_exam VALUES (1, '001', 100, '2022-05-14 09:04:38');
INSERT INTO t_exam VALUES (2, '001', 100, '2022-05-15 09:05:04');
INSERT INTO t_exam VALUES (3, '001', 100, '2022-05-16 09:05:15');
INSERT INTO t_exam VALUES (4, '001', 90, '2022-05-17 09:05:40');
INSERT INTO t_exam VALUES (5, '002', 100, '2022-05-14 09:08:54');
INSERT INTO t_exam VALUES (6, '002', 100, '2022-05-15 09:09:05');

SET FOREIGN_KEY_CHECKS = 1;
感谢 各位的帮助
如果可以的话 希望能有sql 语句

Answer 1

select user_id,fraction  from (
select user_id
,fraction 
, the_date 
, DATE_SUB(the_date,INTERVAL ROW_NUMBER() over(partition by user_id order by the_date ) day ) as res_date
from 
  (
   select user_id,fraction, date(CREATE_time) as the_date from t_exam where fraction =100 group by user_id,fraction,date(CREATE_time)  
  ) a 
    ) b group by user_id,fraction having count(res_date) >= 3