https://www.youtube.com/watch?v=Xh0EevUOWF0

Similar to: - Add Missing Values & Footer Values

-- Create the brands table
CREATE TABLE brands (
    category    VARCHAR(20),
    brand_name  VARCHAR(20)
);

-- Insert values into the brands table
INSERT INTO brands (category, brand_name)
VALUES
    ('chocolates', '5-star'),
    (NULL,         'dairy milk'),
    (NULL,         'perk'),
    (NULL,         'eclair'),
    ('Biscuits',   'britannia'),
    (NULL,         'good day'),
    (NULL,         'boost');

WITH
ROW_NUMBER()
FIRST_VALUE()
SELECT NULL

with cte as (
select * 
	,row_number() OVER (order by (select null)) as rn
    ,case when category is null then 0 else 1 end as flag
from brands
)
,cte2 as (
select 
	category,
    brand_name,
    sum(flag) OVER (order by rn) as grp
from cte
)

select first_value(category) OVER (partition by grp) as category_new,
brand_name
from cte2