SQL - Return most recent batch of sequential rows
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 &gt; 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


fiddle