SQL Counting Consecutive Days in Date Ranges

Question

I'm trying to count the number of consecutive days a person may have, the only issue is I have date ranges and not a straight list of dates. Here is an example of what I mean by ranges:

Name    Start_Date  End_Date
Johnny  2020-01-02  2020-01-04
Johnny  2020-01-05  2020-01-05
Johnny  2020-01-06  2020-01-10
Jenny   2020-02-07  2020-02-07
Jenny   2020-02-10  2020-02-11
Jenny   2020-02-12  2020-02-12

The start and end dates are a range in 2 columns.

The result I'm trying to achieve is this:

Johnny has 9 consecutive days
Jenny  has 3 consecutive days

I have come across examples of solutions, but I can't find one that fits my problem with having date ranges.

Example of code used so far:

WITH
 
  dates(date, employee_number) AS (
    SELECT DISTINCT CAST(start_date AS DATE), name
    FROM myTABLE
    WHERE name = "Jenny"

  ),
   
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY date) AS rn, name,
      dateadd(day, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp,
      date
    FROM dates
  )
SELECT
  name,
  COUNT(*) AS consecutiveDates,
  MIN(date) AS minDate,
  MAX(date) AS maxDate
FROM groups
GROUP BY grp, name

Answer 1

This is a gaps-and-islands problem. One option is to use lag() and a window sum() to build groups of adjacent records. You can then aggregate by group and count the number of consecutive days, and finally filter on the greatest streak by name:

select name, max(consecutive_days) consecutive_days
from (
    select name, datediff(day, min(start_date), max(end_date)) + 1 consecutive_days
    from (
        select t.*, 
            sum(case when start_date = dateadd(day, 1, lag_end_date) then 0 else 1 end) over(partition by name order by start_date) grp
        from (
            select t.*, 
                lag(end_date) over(partition by name order by start_date) lag_end_date
            from mytable t
        ) t
    ) t
    group by name, grp
) t
group by name

Demo on DB Fiddle:

name   | consecutive_days
:----- | ---------------:
Jenny  |                3
Johnny |                9