Overview

This query counts how many orders in the selected time range were single-item orders versus multi-item orders by checking the number of unique product IDs in each order. It’s useful for analyzing product bundling, cross-sells, and overall purchase behavior.

SQL Query

SELECT
  countIf(1, arrayUniq(products_info.product_id) = 1) AS single_item_orders,
  countIf(1, arrayUniq(products_info.product_id) > 1) AS multi_item_orders,
  count(1) AS orders
FROM
  orders_table
WHERE
  event_date BETWEEN @start_date AND @end_date

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• products_info
• event_date

Derived

• orders

Customization Options

This query classifies orders by the number of unique products in each. You can customize it in the following ways:

Analyze a specific product category

• Add a WHERE clause condition like arrayExists(x -> x.category = 'Accessories', products_info) to restrict the analysis to a category.

Break down by channel or platform

• Add GROUP BY platform or GROUP BY region to compare item mix across customer segments.

Compare by new vs. returning customer

• Add GROUP BY is_new_customer or use a HAVING clause to isolate new or repeat purchasers.