clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798988 fiddles created (41854 in the last week).

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);
 hidden batch(es)


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
series id date
1 147501 20181022
1 147502 20181115
1 147503 20181211
1 147504 20190110
1 147505 20190205
1 147506 20190306
1 147507 20190402
1 147508 20190429
1 147509 20190529
1 147510 20190603
1 147511 20190703
1 147512 20190802
1 177901 20190905
1 177902 20191001
1 177903 20191108
1 177904 20191205
1 190401 20200103
1 190402 20200205
2 189101 20200129
 hidden batch(es)