Find repeating values of a certain value

Question

I have a table similar to:

Date	Person	Distance
2022/01/01	John	15
2022/01/02	John	0
2022/01/03	John	0
2022/01/04	John	0
2022/01/05	John	19
2022/01/01	Pete	25
2022/01/02	Pete	12
2022/01/03	Pete	0
2022/01/04	Pete	0
2022/01/05	Pete	1

I want to find all persons who have a distance of 0 for 3 or more consecutive days. So in the above, it must return John and the count of the days with a zero distance. I.e.

Person	Consecutive Days with Zero
John	3

I'm looking at something like this, but I think this might be way off:

Select Person, count(*), 
       (row_number() over (partition by Person, Date order by Person, Date))
from mytable

Answer 1

Provided I understand your requirement you could, for your sample data, just calculate the difference in days of a windowed min/max date:

select distinct Person, Consecutive from (
  select *, DateDiff(day,
    Min(date) over(partition by person), 
    Max(date) over(partition by person)
  ) + 1 Consecutive
  from t
  where distance = 0
)t
where Consecutive >= 3;

Example Fiddle

If you can have gaps in the dates you could try the following that only considers rows with 1 day between each date (and could probably be simplified):

with c as (
  select *, Row_Number() over (partition by person order by date) rn, 
    DateDiff(day, Lag(date) over(partition by person order by date), date) c
  from t
  where distance = 0
), g as (
  select Person, rn - Row_Number() over(partition by person, c order by date) grp
  from c
)
select person, Count(*) + 1 consecutive
from g
group by person, grp
having Count(*) >= 2;