Getting values based on other column

I have the following data in SQL.

NAME,DATE,REF


Pat1,2021-07-15,5072

'',NULL,5072

'',NULL,5072

'',NULL,5072

'',NULL,5072

Pat2,2021-07-15,5073

Is there a way using SELECT QUERY that we can replace the NULL values in DATE column based on the REF values?

Like replace the NULL values with the first available date for matching REF value, without making any change to the database.

Expected Result

NAME,DATE,REF




Pat1,2021-07-15,5072

'',2021-07-15,5072

'',2021-07-15,5072

'',2021-07-15,5072

'',2021-07-15,5072

Pat2,2021-07-15,5073

Answer

You can do it with MAX() window function:

SELECT NAME,
       COALESCE(DATE, MAX(DATE) OVER (PARTITION BY REF)) DATE,
       REF
FROM tablename 

See the demo.