Is there a way to only show the first date of a 5 day streak?

Question

What I want is to show the first date of a 5 day streak, if it is a total of 10 days it counts as 2 streaks. The range of months can be set specifically only once. For example: I want to get all the available dates from the month of Jan. 2023-01-01 => 2023-01-31.

For example:

My Table:

id	date
1	2023-01-01
1	2023-01-02
1	2023-01-03
1	2023-01-04
1	2023-01-05
1	2023-01-06
1	2023-01-07
1	2023-01-08
1	2023-01-09
1	2023-01-10
1	2023-01-15
1	2023-01-16

Output:

id	date
1	2023-01-01
1	2023-01-06

What I am hoping to ultimately achieve from this output:

id	streak
1	2

What I have tried I tried using counters but they are expensive and I tried filtering out days with no continuous dates after but it also takes out the very last day of a streak.

SELECT 
    DISTINCT
    a.id AS id,
    a.date as DATE
FROM 
    logins a,
    (SELECT @counter := 0, @streak := 0) counter,
    (
        SELECT 
            DISTINCT(b.date) as date
        FROM 
            logins b
        WHERE
            b.id = 1 AND
            b.date >= '2023-01-01' AND
            b.date  <= '2023-01-31'
    ) b
WHERE 
    a.id = 1 AND
    a.date >= '2023-01-01' AND
    a.date <= '2023-01-31' AND
    DATE_ADD(a.date, INTERVAL 1 DAY) = b.date AND
    b.date BETWEEN a.date AND DATE_ADD(a.date, INTERVAL 5 DAY)
GROUP BY
    id, date

How would I go about this?

Answer 1

You can do it with variables:

• @prev_date, that will check for successive values of date
• @counter, that will check for reaching 5 consecutive values, then restart
• @partition, that will check be updated when @counter reaches 1 again

Once you update the values of these variables correctly, you can run an aggregation on each partition (thanks to @partition) and selecting the minimum date that satisfy the count of consecutive days = 5.

SET @prev_date = NULL;
SET @counter = 1;
SET @partition = 0;

SELECT id, MIN(date_) AS date_
FROM (SELECT id,
             CASE WHEN @prev_date =  date_ - INTERVAL 1 DAY AND @counter < 5 
                  THEN @counter := @counter + 1
                  ELSE @counter := 1 
             END AS counter,
             IF(@counter = 1, @partition := @partition + 1, @partition) AS parts,
             @prev_date := date_ AS date_
      FROM tab) cte
GROUP BY id, parts
HAVING COUNT(parts) = 5

Check the demo here.