If you're looking to generate a random number in BigQuery, check out the RAND() function.
It's a pseudo-random number generator, generating a float in the interval [0, 1).
I've used it a few times before, but for the today's exercise, I've decide to try something akin to Python's random.pick(). So, let's pick a random value from an ARRAY.
Inspired by one of Mikhail Berlyant's SO answers (which are some of the best answers on BigQuery on SO, linked in comments), I wanted to randomly assign one of 20 options to 100 participants.
As seen in one of my previous posts about accessing array elements, we're going to generate a random 0-based index to retrieve the random array element in each case.
OFFSET(CAST(ARRAY_LENGTH(available_options)*RAND()-0.5 AS INT64))
Found it useful? Subscribe to my Analytics newsletter atnotjustsql.com.