WITH 
car_generation AS 
(
    SELECT 1 AS id, 1 AS name, 1 AS id_car_model, 1 AS id_car_serie, 2017 AS year_begin, 2018 AS year_end  UNION ALL 
    SELECT 2 AS id, 1 AS name, 1 AS id_car_model, 1 AS id_car_serie, 2018 AS year_begin, 2019 AS year_end  UNION ALL 
    SELECT 3 AS id, 1 AS name, 1 AS id_car_model, 1 AS id_car_serie, 2019 AS year_begin, 2020 AS year_end  UNION ALL      
    SELECT 3 AS id, 1 AS name, 2 AS id_car_model, 2 AS id_car_serie, 2017 AS year_begin, 2018 AS year_end  UNION ALL 
    SELECT 4 AS id, 1 AS name, 2 AS id_car_model, 2 AS id_car_serie, 2018 AS year_begin, 2019 AS year_end  UNION ALL 
    SELECT 5 AS id, 1 AS name, 2 AS id_car_model, 2 AS id_car_serie, 2019 AS year_begin, 2020 AS year_end 

) 
SELECT 
	id, 
	name, 
	id_car_model, 
	id_car_serie, 
	year_begin, 
	year_end, 
	LAG(year_begin) OVER (PARTITION BY id_car_model, name ORDER BY year_begin ASC) AS year_begin_lag,
	LAG(year_end) OVER (PARTITION BY id_car_model, name ORDER BY year_begin ASC) AS year_end_lag		
FROM car_generation

LAG(year_begin) OVER (PARTITION BY id_car_model, name ORDER BY year_begin ASC) AS year_begin_lag,
LAG(year_end) OVER (PARTITION BY id_car_model, name ORDER BY year_begin ASC) AS year_end_lag	

WITH cte1 AS 
(
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY id_car_model ORDER BY name ASC) rn
FROM car_generation
),
cte2 AS 
(
SELECT DISTINCT
       id_car_model, 
       FIRST_VALUE(year_end) OVER (PARTITION BY id_car_model ORDER BY name ASC) end1
FROM car_generation
)
SELECT cte1.id, 
       cte1.name, 
       cte1.id_car_model, 
       cte1.id_car_serie,
       CASE WHEN cte1.rn=1 THEN cte1.year_begin
                           ELSE cte2.end1 + 3 * (rn - 2)
                           END year_begin,
       CASE WHEN cte1.rn=1 THEN cte1.year_end
                           ELSE cte2.end1 + 3 * (rn - 1)
                           END year_end
FROM cte1
JOIN cte2 USING (id_car_model)
ORDER BY cte1.id_car_model, cte1.name;