CREATE TABLE test (series INT, id INT, `date` INT); INSERT INTO test VALUES (1, 190402, 20200205), (1, 190401, 20200103), (1, 177904, 20191205), (1, 177903, 20191108), (1, 177902, 20191001), (1, 177901, 20190905), (1, 147512, 20190802), (1, 147511, 20190703), (1, 147510, 20190603), (1, 147509, 20190529), (1, 147508, 20190429), (1, 147507, 20190402), (1, 147506, 20190306), (1, 147505, 20190205), (1, 147504, 20190110), (1, 147503, 20181211), (1, 147502, 20181115), (1, 147501, 20181022); INSERT INTO test VALUES (2, 189101, 20200129), (2, 164705, 20190925), (2, 164704, 20190904), (2, 164703, 20190802), (2, 164702, 20190703), (2, 164701, 20190605);

WITH cte1 AS ( SELECT series, id, `date`, DATEDIFF(LEAD(STR_TO_DATE(`date`, '%Y%m%d')) OVER (PARTITION BY series ORDER BY `date`), STR_TO_DATE(`date`, '%Y%m%d')) diff FROM test ), cte2 AS ( SELECT series, id, `date`, COALESCE(SUM(diff > 40) OVER (PARTITION BY series ORDER BY `date` DESC), 0) grp FROM cte1 ) SELECT series, id, `date` FROM cte2 WHERE grp = 0 ORDER BY 1,3