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.