Date calculation

Analytics
N/A

Date calculation

The self join is between b is to fetch a set of values rather than the key1.

(
select a.key1,a.key2,a.key_date,a.val1,a.val2,b2.key1
from a join b b1
on a.key1 = b.key1
join b b2
on b1.descriptors = b2.descriptors

) a1
join c
where c.key1 = b2.key1
and c.key2 = a1.key1
and c.key_date = (
select max(c1.key_date)
from c c1
where c1.key1 = c.key1
and c1.key2 = c.key2
and c1.key_date <= a1.key_date
)
qualify
min(c.value)
over (
partition by a1.key1,a1.key2,a1.key_date
) > 0

The above one performs badly due the max date calculation happening in the subquery. Is there anyway to do it better?
I use that to get the date equal to/ or the last date when comparing with table a.