select count(distinct part) from 
 (select count(1) c, coalesce(p.part, s.part) as part from supplier full/left/right join part) on p.part=s.part group by 2)
 where part like '%a' and c > 10

We currently do not pushdown the filter (part like '%a%' down) to the scans. But carefully observing the semantics of coalesce, this can be pushdown below join. It currently works for inner join but it should work outer/full join as well.

CC: @feilong-liu