Showcasing Versatile BigQuery Aggregation Functions: LOGICAL_AND and LOGICAL_OR

LOGICAL_AND and LOGICAL_OR are non-standard SQL aggregation functions in BigQuery. They are used to evaluate boolean expressions within a grouping bucket:

• LOGICAL_OR: Returns TRUE if at least one value in the grouping bucket is TRUE.
• LOGICAL_AND: Returns TRUE if all values in the grouping bucket are TRUE.

These functions can be paired with NOT when needed and are versatile in their application.

To use LOGICAL_AND and LOGICAL_OR with GROUP BY in BigQuery, follow these steps:

1. Group your data: Use the GROUP BY clause to group your data into buckets.
2. Apply the functions: Use LOGICAL_AND or LOGICAL_OR to compute a result for each bucket.
3. Filter the output: Use the HAVING clause to filter the results based on the computed boolean values.

Example:

SELECT customer_id, LOGICAL_AND(order_paid) AS all_orders_paid
FROM orders
GROUP BY customer_id
HAVING LOGICAL_AND(order_paid) = TRUE;

To use LOGICAL_AND and LOGICAL_OR with window functions in BigQuery, follow these steps:

1. Partition your data: Use the OVER (PARTITION BY ...) clause to define the window for your data.
2. Apply the functions: Use LOGICAL_AND or LOGICAL_OR within the window function to compute results.
3. Filter the output: Use the QUALIFY clause to filter the results based on the computed boolean values.

Example:

SELECT customer_id, order_id, LOGICAL_OR(order_paid) OVER (PARTITION BY customer_id) AS any_order_paid
FROM orders
QUALIFY LOGICAL_OR(order_paid) OVER (PARTITION BY customer_id) = TRUE;

Some practical use cases for LOGICAL_AND and LOGICAL_OR in BigQuery include:

• Checking if all orders are paid: Use LOGICAL_AND to determine if all orders for a customer are paid.
• Identifying outstanding orders: Use LOGICAL_OR to check if there are any orders that are not shipped yet.
• Tracking specific product orders: Use LOGICAL_OR to find out if a customer has ordered a specific product, such as olives, in the last 3 months.

Example:

SELECT customer_id,
       LOGICAL_AND(order_paid) AS all_orders_paid,
       LOGICAL_OR(order_shipped = FALSE) AS outstanding_orders,
       LOGICAL_OR(product = 'olives' AND order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AS ordered_olives_recently
FROM orders
GROUP BY customer_id;