Recursive query to replace null values

With the following table, I need to make a query to replace the null values of “time” (number of minutes, integer) by a value consistent with the preceding and following values, with partitioning by “esz” and sorting by ascending “rank”. In the example, the data is already sorted. Ideally:

• the value should be as close as possible to the preceding one (+1) when it exists
• the value should be as close as possible to the one that follows (-1) if none precedes (rank 1)
• if the difference between the preceding and following values is insufficient (0 or 1, for example), then the value must be equal to the smaller of the two.
• the value must never be less than the previous one, nor greater than the following one. No "esz" can have only empty "time" values I've tried LAG() and LEAD() but, as the null values can be anywhere, I don't think this is the best option. I guess I'd have to use a recursive query, but I can't do it. Can anyone help me find a solution? Thank you very much

Sample output:

I can handle most of the cases in the table with LAG() and LEAD() and CASE. But I still have problems with some of them: id=11 and id=12 for example. Either I have to go backwards from the value of id=13, or I have to know when filling id=11 that I have to leave a value available to fill id=12.

My last query:

    SELECT
      esz,
      code,
      rank,
      CASE
        WHEN time IS NOT NULL THEN time
        ELSE
          CASE
            WHEN rank = 1 THEN 1
            WHEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) + 1
            WHEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) - 1
            WHEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL THEN
              CASE
                WHEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) - LAG(time) OVER(PARTITION BY esz ORDER BY rank) <= 1 THEN LAG(time) OVER(PARTITION BY esz ORDER BY rank)
                ELSE LAG(time) OVER(PARTITION BY esz ORDER BY rank) +1
              END
            WHEN rank > LEAD(rank) OVER(PARTITION BY esz ORDER BY rank) OR LEAD(rank) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN 999
          END
      END AS time
      FROM mytable
      ORDER BY esz, rank

Here's the result:

I've highlighted the values that differ from the expected result. It's not perfect, but there are no inconsistencies. However, there would be a problem if more than 3 consecutive overrides were inserted.

I found a way to resolved this issue correctly.
As there are two NULL scenario in your inputs. So, design query according to that ...

select id, esz,  code, ranks,
  -- lag(times) over(partition by esz order by ranks) as prev_time,
  -- lead(times) over(partition by esz order by ranks) as next_time,
  times,
  CASE
      -- Previous || Current*  || Next :: Scenario
      --    ANY   || NOT NULL* || ANY  
      WHEN(times is not NULL) 
           THEN times
      -- NOT NULL ||   NULL*   || NOT NULL
      WHEN(times is NULL AND 
           (lag(times) over(partition by esz order by ranks)) is not NULL AND 
           (lead(times) over(partition by esz order by ranks)) is not NULL) 
           THEN CASE 
                  WHEN((lag(times) over(partition by esz order by ranks) + 1) <
                        (lead(times) over(partition by esz order by ranks)) - 1)
                     THEN ((lag(times) over(partition by esz order by ranks)) + 1)
                  ELSE
                      ((lead(times) over(partition by esz order by ranks)) - 1)
                END
      --   NOT NULL ||  NULL  ||   NULL*   ||  ANY   (esz = 4 and 2)
      WHEN(times is NULL AND
           (lag(times) over(partition by esz order by ranks)) is NULL AND
           (lag(times, 2) over(partition by esz order by ranks)) is not NULL) 
           THEN CASE
               WHEN((lead(times) over(partition by esz order by ranks)) is NULL)
                  THEN (lag(times, 2) over(partition by esz order by ranks) + 2)
               ELSE CASE
                  WHEN((lag(times, 2) over(partition by esz order by ranks) + 2) <
                       (lead(times) over(partition by esz order by ranks) - 1))
                     THEN (lag(times, 2) over(partition by esz order by ranks) + 2)
                  ELSE (lead(times) over(partition by esz order by ranks) - 1)
               END
            END
       --   ANY  ||   NULL*   ||  NULL || NOT NULL  (esz = 3)
      WHEN(times is NULL AND
           (lead(times) over(partition by esz order by ranks)) is NULL AND
           (lead(times, 2) over(partition by esz order by ranks)) is not NULL) 
           THEN CASE
              WHEN ((lag(times) over(partition by esz order by ranks)) is NULL)
                  THEN (lead(times, 2) over(partition by esz order by ranks) - 2)
              ELSE CASE
                  WHEN((lag(times) over(partition by esz order by ranks) + 1) <
                        (lead(times, 2) over(partition by esz order by ranks) - 2))
                      THEN (lag(times) over(partition by esz order by ranks) + 1)
                  ELSE (lead(times, 2) over(partition by esz order by ranks) - 2)
              END
            END
      --    NOT NULL ||   NULL*   || ANY
      WHEN(times is NULL AND 
           (lag(times) over(partition by esz order by ranks)) is not NULL) 
           THEN ((lag(times) over(partition by esz order by ranks)) + 1)
      --       NULL  ||   NULL*   ||  NOT NULL
      WHEN(times is NULL AND 
           (lag(times) over(partition by esz order by ranks)) is NULL AND 
           (lead(times) over(partition by esz order by ranks)) is not NULL) 
           THEN ((lead(times) over(partition by esz order by ranks)) - 1)
      ELSE -999
  END as time_output
from T1

Run the Query: db<>fiddle

Output with validation:

This solution work for two null scenario top or bottom or both, as follows:

value | null | *(Current Position = NULL) | null | value
value | null | *(Current Position = NULL) | anything
anything | *(Current Position = NULL) | null | value

So, go to db<>fiddle page and there I explain all conditions one by one.
Please add the Case-03 (null-CurrentPos-null) scenario if required.

I think you understand my approach. So, design your query according to that.