1

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
  • 2
    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
2

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

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.