I’m looking for an easy way to use measure results as a dimension in outer query without creating a new derived table. Is it possible through defining a new dimension somehow or any other way?
SELECT nr_of_orders, count(user_id)
SELECT user_id, count(orders) as nr_of_orders
GROUP BY user_id) trips
GROUP BY nr_of_orders
SELECT [new dimension], count(user_id)
GROUP BY 1
Best way that I know of is to use a native derived table. Then join that native derived table back on the order table.
Indeed, as Sach suggests, generally you have to do this via a derived table. You could explore using a correlated subquery, but that’s not an approach supported by Looker.