---
updatedAt: 2025-08-04T11:35:03.000Z
agentTools:
  projectIndex: https://triplewhale.readme.io/llms.txt
---

# SQL Example: Most Common Next Product Bought (Sequential Product Purchases)

# 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 customer’s order with their very next order, then joins the products from both orders to uncover natural follow-on purchases and bundling opportunities.

# SQL Query

```sql
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](https://triplewhale.readme.io/docs/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.