MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.3.32-MariaDB


create table samp (product_id int, category_id varchar(10), parent_category varchar(10));
insert into samp values(1,'aaa',0);
insert into samp values(1,'bbb','aaa');
insert into samp values(1,'ccc','bbb');
insert into samp values(2,'aaa',0);
insert into samp values(2,'bbb','aaa');
insert into samp values(2,'ddd',0);
select *, row_number() over (partition by product_id order by parent_category  desc) as row_ from samp 


product_id
category_id
parent_category
row_


1
ccc
bbb
1

1
bbb
aaa
2

1
aaa
0
3

2
bbb
aaa
1

2
aaa
0
2

2
ddd
0
3


with recursive cte as (
select 
*,
0 as level, 
concat(product_id, '-', category_id) as ar 
from samp where parent_category ='0'
union all
select t1.*, t2.level+1, ar
from samp t1 inner join cte t2 on t1.parent_category =t2.category_id and t1.product_id=t2.product_id
),
cte1 as (
select *, row_number() over (partition by ar order by level desc) as rank_ from cte 
)

select product_id, category_id,parent_category   from cte1 where rank_=1


product_id
category_id
parent_category


1
ccc
bbb

2
bbb
aaa

2
ddd
0