SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, PIVOT(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;

SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, FLATTEN(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;

SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, COLLECT_SET(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;

SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, ARRAY_DISTINCT(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;