Fetch All Records Between Latests Dates Group By ID

Let's say I have a table like below:

id	start_value	end_value	date	value
1	null	null	05-APR-23	2
1	null	5	09-APR-23	null
1	5	null	15-APR-23	null
1	null	null	16-APR-23	4
1	null	null	16-APR-23	-1
1	null	8	16-APR-23	null
2	1	null	05-APR-23	null
2	null	9	09-APR-23	null
2	9	null	13-APR-23	null
2	null	null	13-APR-23	1
2	null	null	14-APR-23	-5
2	null	null	15-APR-23	-3
2	null	null	16-APR-23	-4
2	null	-1	16-APR-23	null

I asked a question before and can get the latest non-null start and end values for an id like below with SQL:

SELECT id,
       start_value,
       end_value,
       start_date,
       end_date
FROM   (
  SELECT id,
         LAST_VALUE(start_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_value,
         LAST_VALUE(end_value)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_value,
         LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_date,
         LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_date,
         ROW_NUMBER()
            OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rn
  FROM   table_name
)
WHERE  rn = 1

id	start_value	end_value	start_date	end_date
1	5	8	15-APR-23	16-APR-23
2	9	-1	13-APR-23	16-APR-23

Now, I would like to get the intermediate values between the latest non null start and end dates for an ID like below, but couldn't figure it out how to do that:

id	start_value	end_value	date	value
1	5	null	15-APR-23	null
1	null	null	15-APR-23	4
1	null	null	16-APR-23	-1
1	null	8	16-APR-23	null
2	9	null	13-APR-23	null
2	null	null	13-APR-23	1
2	null	null	14-APR-23	-5
2	null	null	15-APR-23	-3
2	null	null	16-APR-23	-4
2	null	-1	16-APR-23	null

Solution

Find the LAST_VALUE for start and end date with a window over the entire result set and then filter on that:

SELECT id,
       start_value,
       end_value,
       "DATE",
       value
FROM   (
  SELECT t.*,
         LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (
              PARTITION BY id ORDER BY "DATE"
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS start_date,
         LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END)
            IGNORE NULLS OVER (
              PARTITION BY id ORDER BY "DATE"
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS end_date
  FROM   table_name t
)
WHERE  "DATE" BETWEEN start_date AND end_date

Which, for the sample data:

CREATE TABLE table_name (id, start_value, end_value, "DATE", value) AS
SELECT 1, null, null, DATE '2023-04-05', 2 FROM DUAL UNION ALL
SELECT 1, null, 5,    DATE '2023-04-09', null FROM DUAL UNION ALL
SELECT 1, 5,    null, DATE '2023-04-15', null FROM DUAL UNION ALL
SELECT 1, null, null, DATE '2023-04-16', -4 FROM DUAL UNION ALL
SELECT 1, null, null, DATE '2023-04-16', -1 FROM DUAL UNION ALL
SELECT 1, null, 8,    DATE '2023-04-16', null FROM DUAL UNION ALL
SELECT 2, 1,    null, DATE '2023-04-05', null FROM DUAL UNION ALL
SELECT 2, null, 9,    DATE '2023-04-09', null FROM DUAL UNION ALL
SELECT 2, 9,    null, DATE '2023-04-13', null FROM DUAL UNION ALL
SELECT 2, null, null, DATE '2023-04-13', 1 FROM DUAL UNION ALL
SELECT 2, null, null, DATE '2023-04-14', -5 FROM DUAL UNION ALL
SELECT 2, null, null, DATE '2023-04-15', -3 FROM DUAL UNION ALL
SELECT 2, null, null, DATE '2023-04-16', -4 FROM DUAL UNION ALL
SELECT 2, null, -1,   DATE '2023-04-16', null FROM DUAL;

Outputs:

ID	START_VALUE	END_VALUE	DATE	VALUE
1	5	null	2023-04-15 00:00:00	null
1	null	null	2023-04-16 00:00:00	-4
1	null	null	2023-04-16 00:00:00	-1
1	null	8	2023-04-16 00:00:00	null
2	9	null	2023-04-13 00:00:00	null
2	null	null	2023-04-13 00:00:00	1
2	null	null	2023-04-14 00:00:00	-5
2	null	null	2023-04-15 00:00:00	-3
2	null	null	2023-04-16 00:00:00	-4
2	null	-1	2023-04-16 00:00:00	null

fiddle