# Determine Order Streak

For a particular customer, I have data returned by a query that looks like this:

``````OrderDate   OrderCount
2020-01-12  0
2020-01-13  17
2020-01-14  22
2020-01-15  21
2020-01-16  30
2020-01-17  12
2020-01-18  0
2020-01-20  0
2020-01-21  49
2020-01-22  28
2020-01-23  4
``````

These are the number of orders for each business day for that customer. How do I efficiently determine their current "Streak" of orders? For instance, if today was 2020-01-24, the answer would be 3, since they have ordered 3 days without missing a day.

Note that the days may not always be consecutive. There are some non-business days that aren't returned by the query (ie 2020-01-19 in example).

Can anyone tell me how to do this? Thanks in advance!

• Imagine the values for 2020-01-18 and 2020-01-20 are not zero. What is "Streak" in that case? 4 (from 20 to 23)? 10 (records count from 13 to 23)? 11 (days from 13 to 23, including skipped non-business day)? And the same when only 2020-01-20 is not zero. – Akina Jan 23 at 19:39
• Please tag this question with the particular RDBMS you are trying to solve this problem with. – David Browne - Microsoft Jan 23 at 19:53
• The search term to use is "gaps and islands problem". – mustaccio Jan 23 at 19:59

As mustaccio pointed out this is a "gaps and islands" problem:

``````declare @date date = '2020-01-16';

with ct as
(
/* First you must detect the gaps, filtering those rows lesser than the selected date */
select
OrderDate,
case when
datediff(day,
coalesce(lag(OrderDate) over (order by OrderDate), OrderDate),
OrderDate
) <> 1 then 1 else 0 end rst
from
orders
where
OrderDate <= @date
)
, ct2 as
(
/* Then set an island of each group */
select
OrderDate,
sum(rst) over (order by OrderDate) as grp
from
ct
)
/* And finally count the affected rows */
select top 1
count(OrderDate) streak
from
ct2
group by
grp
order by
max(OrderDate) desc;
``````
```| streak |
| -----: |
|      5 |
```

db<>fiddle here

• That seems like it works like a charm... thank you. – Dicer Jan 23 at 21:10
• I'm glad to help. – McNets Jan 23 at 21:11