Разделение перекрывающихся периодов в SQL

У меня есть эта таблица в SQL (называемая «df»):

         v1         v2   v3         v4 name
 2015-06-23 2024-06-09 2013 2015-03-31  red
 2015-06-23 2024-06-09 2014 2015-03-31  red
 2015-06-23 2024-06-09 2018 2019-03-18  red
 2015-06-23 2024-06-09 2020 2021-02-21  red
 2015-06-23 2024-06-09 2023 2024-03-15  red
 2015-06-23 2024-06-09 2013 2015-03-31 blue
 2015-06-23 2024-06-09 2014 2015-03-31 blue
 2015-06-23 2024-06-09 2018 2019-03-18 blue


CREATE TABLE df (
    v1 DATE,
    v2 DATE,
    v3 INT,
    v4 DATE,
    name VARCHAR(10)
);

INSERT INTO df (v1, v2, v3, v4, name) VALUES
('2015-06-23', '2024-06-09', 2013, '2015-03-31', 'red'),
('2015-06-23', '2024-06-09', 2014, '2015-03-31', 'red'),
('2015-06-23', '2024-06-09', 2018, '2019-03-18', 'red'),
('2015-06-23', '2024-06-09', 2020, '2021-02-21', 'red'),
('2015-06-23', '2024-06-09', 2023, '2024-03-15', 'red'),
('2015-06-23', '2024-06-09', 2013, '2015-03-31', 'blue'),
('2015-06-23', '2024-06-09', 2014, '2015-03-31', 'blue'),
('2015-06-23', '2024-06-09', 2018, '2019-03-18', 'blue');

Для каждого цвета я пытаюсь выполнить следующий расчет:

• Шаг 1. Сколько лет между: [max(V2) - min(V1)] + 1?
• Шаг 2: Какие значения V3 находятся между: Год[max(V2) - 1] до Год[min(V1) - 1]
• Шаг 3. Сколько значений V3 содержится в ответе на шаг 2?
• Шаг 4: Разделение: Шаг 3/Шаг 1

Например, в случае «красного»:

• Шаг 1: 10 лет: 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024.
• Шаг 2: Годы: 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023.
• Шаг 3: Количество лет = 4 (2014, 2018, 2020, 2023)
• Шаг 4: 4/10 = 0,4

Я попытался написать SQL-код для этой задачи, используя отдельный CTE для каждого шага:

WITH step_1 AS (
    SELECT
        name,
        YEAR(MAX(v2)) - YEAR(MIN(v1)) + 1 AS YearRange
    FROM df
    GROUP BY name
),
step_2_part1 AS (
    SELECT
        name,
        YEAR(MAX(v2)) - 1 AS MaxYear,
        YEAR(MIN(v1)) - 1 AS MinYear
    FROM df
    GROUP BY name
),
step_2_part2 AS (
    SELECT
        d.name,
        d.v3
    FROM df d
    JOIN step_2_part1 s2p1 ON d.name = s2p1.name
    WHERE d.v3 BETWEEN s2p1.MinYear AND s2p1.MaxYear
),
step_3 AS (
    SELECT
        name,
        COUNT(v3) AS V3Count
    FROM step_2_part2
    GROUP BY name
),
step_4 AS (
    SELECT
        s1.name,
        CASE
            WHEN s1.YearRange = 0 THEN 'Error: denominator is 0'
            ELSE CAST(s3.V3Count AS FLOAT) / s1.YearRange
        END AS Result
    FROM step_1 s1
    JOIN step_3 s3 ON s1.name = s3.name
)
SELECT * FROM step_4;

Мой вопрос: Я не уверен, правильно ли я выполняю Шаг 3. Я не уверен, нужно ли мне добавлять дополнительный CTE и использовать подвыбор/подзапрос вместе с оператором IN, чтобы правильно определить этот диапазон лет.

Может кто-нибудь помочь мне сделать это правильно?

Спасибо!