Counts of counts

It would be really nice if there were a Looker way to group by one dimension, count the number of rows, and then count how many group have each result. In MySQL that’s

select c, count(*) from(
  select foo, count(*)c from t group by foo
)t1 group by c

but making a derived table every time I want to do this is annoying. It would be good if there were some feature in Looker that enabled this. I’m not sure what form (UI) such a feature should take, though.

I think someone worked on this internally at a Looker hack day we had! I didn’t see any kind of releasable feature, but the idea was to create a one-click way to dimensionalize a measure (which is currently a fairly complex process using derived tables, like you mentioned).

UI-wise, I wonder if it could present itself like a custom field. It’d be ephemeral, so you could spin them up or down as you wanted as an end user, and now that custom fields (separate from table calcs) are included in the generated SQL, it seems there’s some foundation set for that possibility.