I searched through JIRA and the web, and didn't find an answer. I'm wondering when/if there are plans for Impala to support correlated subqueries.
Something similar to this:
orders.id AS orders_id, (SELECT COUNT(order_items.id) FROM order_items WHERE order_items.order_id = orders.id) AS orders_total_number_of_items FROM thelook.orders AS orders ORDER BY orders_total_number_of_items LIMIT 500
Sometimes it's quicker to grab data this way than building a separate table. I saw that there is support for IN (SELECT ...), which is great.