Sql-server – Determine Order Streak

gaps-and-islandsorder-byquerysql server

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!

Best Answer

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

Related Question