6 BigQuery SQL Functions Every User Should Know

Check if your database has them too

Jul 27, 2022

3 min read

In my previous article, I discussed 4 BigQuery SQL shortcuts that can simplify your queries. Since then I discovered 6 more useful SQL functions I wished I had known earlier and that I’d like to share today.

1. GENERATE_ARRAY

The GENERATE_ARRAY function allows you to create an array with a start and ending value incremented by a step value. This is useful if you want to generate a series of numbers with a specific sequence. Note the query result has only 1 row of data because the values are in an array.

Screenshot with GENERATE_ARRAY query example created by author

2. GENERATE_DATE_ARRAY

Similar to GENERATE_ARRAY, the GENERATE_DATE_ARRAY function allows you to create an array of dates by a step interval. This is useful if you need to create a series of dates with specific intervals. I’ve created 14 dates using a 1 day interval below but you can use any interval supported by Bigquery such as WEEK, MONTH, and YEAR.

Screenshot with GENERATE_DATE_ARRAY query example created by author

3. UNNEST

The UNNEST function allows you to create rows for each value of an array. UNNEST can be used in conjunction with GENERATE_ARRAY or GENERATE_DATE_ARRAY. This function was useful when I needed a master list of dates to join against to make sure I had values for all dates in the time period I was querying.

In the query below, I created a CTE named _twoweeks and then used the UNNEST function on the resulting array column named _datearray to create a row per date. In order for UNNEST to work correctly, you must add a comma after the CTE or table name you are querying from and then call the UNNEST function on the array column. Note the query results show 14 rows indicating UNNEST created a row for each date in _datearray.

Screenshot with UNNEST query example created by author

4. LAST_DAY

The LAST_DAY function returns the last day of the month for a given date. This is useful if you need to provide the last day of the month for reporting purposes.

In the query below, I created a CTE named _oneyear using the GENERATE_DATE_ARRAY containing the first date of every month in 2022 and then called the LAST_DAY function to create a second column with the last day of the month. The query results show the start and end of every month in 2022.

Screenshot with LAST_DAY query example created by author

5 / 6. LEAST and GREATEST

LEAST returns the smallest value among a set of values or columns while GREATEST shows the largest value. Note if any of the arguments are NULL then a NULL is returned.

In the example below, I used the LEAST and GREATEST functions to return the smallest and largest values across the 3 columns from my sample table _test_greatestleast. Note how LEAST and GREATEST are different than the MIN and MAX function which looks across all rows of a column in a table. Here MIN and MAX on _cola would return 0 as the smallest value among the 10 rows and MAX would return 5.

Screenshot with LEAST and GREATEST query example created by author

Final Thoughts

While the functions I mentioned are available in BigQuery, a few such as LAST_DAY, GREATEST, and LEAST are available in other databases such as Redshift and Snowflake. If you have a few minutes I highly recommend reading your database documentation because you never know the useful functions you may find.

Note: All queries above were run on BigQuery sandbox that’s free to anyone with a Google account.