Another look at LOGICAL_AND & LOGICAL_OR in BigQuery

Out of all those non-standard SQL functions in BigQuery, I think I like LOGICAL_AND and LOGICAL_OR the most.

These are aggregation functions I've posted about before (link in my comments), but just wanted to showcase how versatile they can be.

- LOGICAL_OR = at least one value in the grouping bucket is TRUE.
- LOGICAL_AND = all the values in the grouping bucket are TRUE.

Plenty of stuff you can do with it:
- pair them with NOT when needed
- since they're aggregation functions, you compute a result for a bucket with GROUP BY or you can opt for using a window function call OVER (PARTITION BY ...)
- if you opt for GROUP BY, you can opt for filtering output with HAVING; whereas if you go through the window function route, you have QUALIFY for that matter

In the example below, I'm looking to compute three things about customers:
- are all their orders are paid?
- do they have any outstanding orders (i.e. not shipped yet)?
- whether they have ordered olives in the last 3 months

I make use of LOGICAL_AND and LOGICAL_OR for that.

As usual, one can achieve the same results using MIN and MAX, since:

Found it useful? Subscribe to my Analytics newsletter at