SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (
  id	number,
  dt    date
);
✓
insert into mytable values
(1,	'2023-12-10'),
(2,	'2023-12-11'),
(3,	'2023-12-12'),
(4,	'2023-12-16'),
(5,	'2023-12-17'),
(6,	'2023-12-25'),
(7,	'2023-12-26');
✓
-- enumerate successive dates 
WITH cte AS (
  SELECT *,
         CASE WHEN julianday(dt)-julianday(LAG(dt, 1, dt) OVER (ORDER BY dt)) <= 1 THEN 0 ELSE 1 END AS date_diff
  FROM mytable
)
SELECT *, SUM(date_diff) OVER (ORDER BY dt) as grp
FROM cte


id
dt
date_diff
grp


1
2023-12-10
0
0

2
2023-12-11
0
0

3
2023-12-12
0
0

4
2023-12-16
1
1

5
2023-12-17
0
1

6
2023-12-25
1
2

7
2023-12-26
0
2


WITH cte AS (
  SELECT *,
         CASE WHEN julianday(dt)-julianday(LAG(dt, 1, dt) OVER (ORDER BY dt)) <= 1 THEN 0 ELSE 1 END AS date_diff
  FROM mytable
  WHERE dt <= '2023-12-26'
),
cte2 AS (
  SELECT *, SUM(date_diff) OVER (ORDER BY dt) as grp
  FROM cte
)
SELECT c.*
FROM cte2 c
INNER JOIN (
  SELECT MAX(grp) as latest_grp, MAX(dt) as latest_dt
  FROM cte2
) AS s ON s.latest_grp = c.grp AND s.latest_dt = '2023-12-26'


id
dt
date_diff
grp


6
2023-12-25
1
2

7
2023-12-26
0
2


WITH cte AS (
  SELECT *,
         CASE WHEN julianday(dt)-julianday(LAG(dt, 1, dt) OVER (ORDER BY dt)) <= 1 THEN 0 ELSE 1 END AS date_diff
  FROM mytable
  WHERE dt <= '2023-12-26'
),
cte2 AS (
  SELECT *, SUM(date_diff) OVER (ORDER BY dt) as grp
  FROM cte
)
SELECT COUNT(*)
FROM cte2 c
INNER JOIN (
  SELECT MAX(grp) as latest_grp, MAX(dt) as latest_dt
  FROM cte2
) AS s ON s.latest_grp = c.grp AND s.latest_dt = '2023-12-26'


COUNT(*)


2

id	dt	date_diff	grp
1	2023-12-10	0	0
2	2023-12-11	0	0
3	2023-12-12	0	0
4	2023-12-16	1	1
5	2023-12-17	0	1
6	2023-12-25	1	2
7	2023-12-26	0	2