Find the latest streak

I would like to create an SQlite query to find streaks from a specific date.

I have the table:

id	dt
1	10/12/2023
2	11/12/2023
3	12/12/2023
4	16/12/2023
5	17/12/2023
6	25/12/2023
7	26/12/2023

For example, if I query the data for:

09/12/2023 - the result will be 0
12/12/2023 - the result will be 3
11/12/2023 - the result will be 2
17/12/2023 - the result will be 2
24/12/2023 - the result will be 0
26/12/2023 - the result will be 2

Solution

You have a gaps and islands problem. To solve it we need to enumerate each successive group of rows by a unique id.

We can use the window function LAG() to get the previous row and calculate the difference between successive dates and then apply a running total to the resulting differences:

WITH cte AS (
  SELECT *,
         CASE WHEN julianday(dt)-julianday(LAG(dt, 1, dt) OVER (ORDER BY dt)) <= 1 THEN 0 ELSE 1 END AS date_diff
  FROM mytable
  WHERE dt <= '2023-12-26'
),
cte2 AS (
  SELECT *, SUM(date_diff) OVER (ORDER BY dt) as grp
  FROM cte
)
SELECT COUNT(*)
FROM cte2 c
INNER JOIN (
  SELECT MAX(grp) as latest_grp, MAX(dt) as latest_dt
  FROM cte2
) AS s ON s.latest_grp = c.grp AND s.latest_dt = '2023-12-26'

Demo here