Function to REPLACE* last previous known value for NULL

Question

I want to fill the NULL values with the last given value for that column. A small sample of the data:

2021-08-15  Bulgaria    1081636
2021-08-16  Bulgaria    1084693
2021-08-17  Bulgaria    1089066
2021-08-18  Bulgaria    NULL
2021-08-19  Bulgaria    NULL

In this example, the NULL values should be 1089066 until I reach the next non-NULL value.

I tried the answer given in this response, but to no avail. Any help would be appreciated, thank you!

EDIT: Sorry, I got sidetracked with trying to return the last value that I forgot my ultimate goal, which is to replace the NULL values with the previous known value.

Therefore the query should be

UPDATE covid_data

SET people_vaccinated = ISNULL(?)

Answer 1

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

SELECT dt
     , country
     , cnt
     , MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
  FROM #data

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

SELECT dt
     , country
     , cnt
     , SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
    SELECT country
         , dt
         , cnt
         , SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
    FROM #data
) AS d
ORDER BY dt

Here's a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you'll see MAX(...) method producing wrong results.