I wasn't expecting sql pros to see this. :)
I was testing queries as I was writing the post and didn't get a chance optimize it. :)
Normally I would have less nested queries and do more computation in python, I just wanted to make sure these work without python as well.
Good point, I didn't even consider how vest to hive ration changes.
Thank You @dalz!