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.

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.

SELECT
  customer_id,
  LOGICAL_AND(is_paid) AS all_orders_paid,
  LOGICAL_OR(NOT is_shipped) AS outstanding_orders,
  LOGICAL_OR(product_id = 'olives' AND
             order_date > DATE_SUB(CURRENT_DATE(),
                                   INTERVAL 3 MONTH)) AS ordered_olives_last_3_months
FROM input_data

GROUP BY customer_id