3 ways to divide safely in BigQuery

We all learned in school that division by zero is an impossible operation in ordinary arithmetic. George Berkeley learned it much earlier and wrote about it in his book “Analyst” (1734). Many people disagreed with him. As he was a satirical writer he called such people ‘’free-thinkers”.

Probably he was right as in September 1997 a division by zero error on USS Yorktown cruiser broke all the machines in the network and as result, the cruiser was broken.

Such trivial at first glance errors can cause a huge headache in an analyst’s life as well.

Let’s say we have a table of customers and their regular cat food orders over the last month. We just want to calculate the average order value (AOV) for last month.

Our dataset is simple.

It’s just a table with the customer’s name, revenue and number of orders made during the last month.

Our query to calculate AOV isn’t very complicated either.

SELECT customer_, (revenue / orders) as aov FROM `dummy_customers.customers`

However, it generates an error.

Not all of the clients in our dataset made orders in the last month. Mary didn’t make an order!

One of the ways to solve the problem is to use the CASE function:

SELECT
customer_,
(CASE
WHEN orders > 0 THEN revenue / orders
ELSE
0
END
) AS aov
FROM
`dummy_customers.customers`

Query results:

Even though it works perfectly it doesn’t seem like an elegant technical solution. Too much code!

Another way to handle this problem is a mathematical function IEEE_DIVIDE which divides X by Y, but never fails if Y is 0. I love the phrase ‘’never fails”.

Let’s run this query. Our X will be revenue and our Y will be orders

SELECT
customer_,
IEEE_DIVIDE(revenue, orders) AS aov
FROM
`dummy_customers.customers`

The result is:

We are good now as we get results without any errors and we also don’t have to write a long case statement just to handle zero.

SAFE_DIVIDE function can handle this issue elegantly as well. What it does is return NULL in case of error.

SELECT
customer_,
SAFE_DIVIDE(revenue, orders) AS aov
FROM
`dummy_customers.customers`

Query results:

The results are amazing. Our query returns NULL for customers who didn’t have any orders last month.

Basically the prefix SAFE_ can be added to other BigQuery functions to handle errors. For example, SAFE_CAST or SAFE_SUBSTR

We reviewed 3 ways to handle the “division by zero” error in BigQuery: CASE WHEN, IEEE_DIVIDE and SAFE_DIVIDE. So we will never break our analytics cruiser in BigQuery.

That leaves only one question. Why didn’t Mary order anything from us last month?