clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335713 fiddles created (27464 in the last week).

CREATE TABLE test (`Date` DATE) SELECT '2021-07-28' `Date` UNION ALL SELECT '2021-07-27' UNION ALL SELECT '2021-07-26' UNION ALL SELECT '2021-07-25' UNION ALL SELECT '2021-07-24' UNION ALL SELECT '2021-07-23' UNION ALL SELECT '2021-07-22' UNION ALL SELECT '2021-07-21' UNION ALL SELECT '2021-07-18' UNION ALL SELECT '2021-07-17' UNION ALL SELECT '2021-07-14' UNION ALL SELECT '2021-07-11' UNION ALL SELECT '2021-07-09' UNION ALL SELECT '2021-07-06' UNION ALL SELECT '2021-07-04' UNION ALL SELECT '2021-07-03' UNION ALL SELECT '2021-07-02'; SELECT * FROM test ORDER BY `Date`;
Date
2021-07-02
2021-07-03
2021-07-04
2021-07-06
2021-07-09
2021-07-11
2021-07-14
2021-07-17
2021-07-18
2021-07-21
2021-07-22
2021-07-23
2021-07-24
2021-07-25
2021-07-26
2021-07-27
2021-07-28
 hidden batch(es)


WITH StartingPoints AS ( SELECT *, CASE WHEN DATE_ADD( LAG(`Date`, 1, '1900-01-01') OVER (ORDER BY `Date`), INTERVAL 1 DAY ) < `Date` THEN 1 END AS IsStart FROM test ), Grouped AS ( SELECT *, COUNT(IsStart) OVER (ORDER BY `Date` ROWS UNBOUNDED PRECEDING) AS GroupId FROM StartingPoints ) SELECT MIN(`Date`) AS StartDate, COUNT(*) AS `Count` FROM Grouped GROUP BY GroupId HAVING COUNT(*) > 1 ORDER BY GroupId DESC;
StartDate Count
2021-07-21 8
2021-07-17 2
2021-07-02 3
 hidden batch(es)