BigQuery LOGICAL_AND and LOGICAL_OR: Practical Examples

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.

So:
- 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:
- MIN([TRUE,..., FALSE]) = FALSE AND MAX([TRUE,..., FALSE]) = MAX.

Found it useful? Subscribe to my Analytics newsletter at https://notjustsql.com.

Another look at LOGICAL_AND & LOGICAL_OR in BigQuery

Comments

Practical SQL

The first thing I do when analyzing a SQL table

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

The first thing I do when analyzing a SQL table

More from this blog