Are we not checking whether the first date is 25/26 and 26/27 are consecutive? We are not checking date consecutiveness anywhere in the query. Even the name has streaks for CD; we are not checking for consecutive tasks. Can we add a check for consecutive dates?

To verify that dates in a sequence are consecutive, you can use window functions such as LAG or LEAD to compare each date with its predecessor. For example, if you have a table tasks with columns task_id, task_name, and task_date, you can write:

SELECT
  task_id,
  task_name,
  task_date,
  CASE
    WHEN LAG(task_date) OVER (PARTITION BY task_name ORDER BY task_date) = DATEADD(day, -1, task_date)
         THEN 'Consecutive'
    ELSE 'Not consecutive'
  END AS consecutive_flag
FROM tasks;

This query partitions by task_name (or any grouping key) and orders by task_date. LAG(task_date) fetches the previous date in that order. By subtracting one day from the current task_date and comparing, you determine if the dates are back‑to‑back. If you need to flag entire streaks, you can use a cumulative sum of non‑consecutive gaps:

WITH gaps AS (
  SELECT
    task_id,
    task_name,
    task_date,
    CASE WHEN LAG(task_date) OVER (PARTITION BY task_name ORDER BY task_date) = DATEADD(day, -1, task_date)
         THEN 0 ELSE 1 END AS is_gap
  FROM tasks
),
  streaks AS (
    SELECT
      task_id,
      task_name,
      task_date,
      SUM(is_gap) OVER (PARTITION BY task_name ORDER BY task_date ROWS UNBOUNDED PRECEDING) AS streak_id
    FROM gaps
  )
SELECT * FROM streaks;

This gives each consecutive block a unique streak_id. Complexity is O(n) per partition, with constant extra space aside from the window buffers. Adjust the partition key if you need to check consecutiveness across different dimensions (e.g., user, project).