In my last post, I tried to tackle getting the Top N Results per Group from a BigQuery dataset.

When I tweeted out the post, I got some great feedback and suggestions for more efficient ways to get the same results, so in this post I want to try to understand why the alternatives are more efficient.

EDIT: After I posted this initially, I got some great feedback, so I wrote a follow-up post here.

In this post, we are going to explore a strategy for collecting the Top N results per Group over a mixed dataset, all in a single query.

I stumbled onto this solution the other day, mostly driven by the fear that I was re-scanning my BigQuery data too often. At the time, the only way I knew how to look at a Top 10 list of a subset of the data was to add a WHERE clause limiting the whole data set to a single group and combine with ORDER BY and LIMIT clauses.

For each group, I would just modify the WHERE clause, rescan all the data, and get new results. I thought there had to be an easier way to get the same ordered subset for any particular group in the data, all at once.

It turns out, there is a much more efficient way to solve this problem.

It’s easy to blow your BigQuery budget when you are exploring a new data set. Because you’re billed for the amount of data scanned, not the ultimate result set, when you don’t know what you’re looking for, you can end up with wasteful queries.

In this post, I’m going to share some tips for more efficiently scanning data in BigQuery when you don’t quite know what you need.