How Can I Find Consecutive Active Weeks In Sql?

What I would like to do is find the number of consecutive weeks that someone is active on Sundays and assign them a value. They have to participate in at least 2 races a day to be

Solution 1:

When looking for sequential values, there is a simple observation that helps. If you subtract a sequence from the dates then the value is a constant. You can use this as a grouping mechanism

select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
       count(*) as NumDaysRaced
from (select t.*,
              dateadd(week, -row_number() over (partitionby customerID, RaceDate),
                      RaceDate) as grp
      fromtable t
      where races >=2
     ) t
groupby CustomerId, grp;

You can then use this to get your final "points":

select CustomerId,
       sum(casewhen NumDaysRaced >1then (NumDaysRaced -1) *100else0end) as Points
from (select CustomerId, min(RaceDate) as seqStart, max(RaceDate) as seqEnd,
             count(*) as NumDaysRaced
      from (select t.*,
                    dateadd(week, -row_number() over (partitionby customerID, RaceDate),
                            RaceDate) as grp
            fromtable t
            where races >=2
           ) t
      groupby CustomerId, grp
     ) c
groupby CustomerId;