Finding date-streak in SQL

I got my sqlite table with entries having a DateTime field. I want to check if there are entries with n consecutive days. Like a streak. Now I could just query all entries ordered by the date-field and iterate over them and check by myself. I am just wondering if there is some more efficient way to do this. Like a build-in function in SQL that I don’t know about or something like that.

Thanks for your advice!

example data:

id, dateTime

0, 2021-06-01 00:00:00
1, 2021-06-03 00:00:00
2, 2021-06-04 00:00:00
3, 2021-06-05 00:00:00 
4, 2021-06-05 00:00:00
5, 2021-06-06 00:00:00
6, 2021-06-07 00:00:00
7, 2021-06-10 00:00:00
8, 2021-06-10 00:00:00
9, 2021-06-11 00:00:00
10, 2021-06-12 00:00:00

Looking for a 5-day-streak, this should result true. Because of date 3-7.

Answer

Assuming the data has no duplicates, you can use lag(). So, if you wanted to see if 5 dates were in a row:

select t.*
from (select t.*, lag(date, 4) over (order by date) as prev_date_n
      from t
     ) t
where prev_date_n = date(date, '-4 day');

Basically, this “goes back” four rows and peaks at the date. If that days is exactly 4 days before, then there are five days in sequence.

EDIT:

If you have duplicates, remove them in a subquery:

select t.*
from (select t.*, lag(date, 4) over (order by date) as prev_date_n
      from (select distinct t.date from t) t
     ) t
where prev_date_n = date(date, '-4 day');