Overview

This query identifies product pairing patterns by finding which products are most commonly purchased immediately after a given product. It uses a window function to compare each order with the customer's next order, helping uncover natural follow-on purchases and bundling opportunities.

SQL Query

WITH orders AS
(
  SELECT
    o.customer_id AS customer_id,
    COALESCE(o.created_at,date_add(o.event_date, INTERVAL o.event_hour HOUR)) AS created_at,
    arrayDistinct(arrayMap(x -> (x.product_id AS product_id, x.product_name AS product_name), o.products_info)) AS products,
    any(products) OVER (PARTITION BY customer_id ORDER BY created_at ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_products
  FROM
    orders_table o
  WHERE
    event_date BETWEEN @start_date AND @end_date
)
SELECT
  p.product_id AS product_id,
  p.product_name AS product_name,
  np.product_id AS next_product_id,
  np.product_name AS next_product_name,
  count(1) AS orders
FROM orders
  ARRAY JOIN products AS p
  ARRAY JOIN next_products AS np
GROUP BY ALL
HAVING np.product_id IS NOT NULL
ORDER BY orders DESC
LIMIT 100

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• customer_id
• event_date
• event_hour
• products_info.product_id
• products_info.product_name

Derived

• orders

Customization Options

This query detects sequential purchase patterns by customer. You can customize it in the following ways:

Extend beyond one future order

• Change the window frame from ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING to ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING to explore longer sequences.

Add platform or region filters

• Insert AND platform = 'Shopify' or similar inside the WHERE clause to limit analysis to specific segments.

Filter specific product families

• Add WHERE arrayExists(x -> x.category = 'Supplements', products_info) inside the CTE to restrict the analysis to a product category.