Find previous purchases that contain the customer's items

1. We’ll want to find other items that were purchased along with the item(s) of interest since the user's previous purchase activity. We’ll need to query the CustomerPurchaseActivities collection and search through the purchased field.

For example, let’s say the item the customer viewed or added_to_cart was productId 530. We’ll want to see what products other customers’ purchased along with productId 530. From there, we can make a recommendation if there is a correlation with productId 530 and another product.

In order to see if the item of interest is part of the items other customers’ purchased, we will use ARRAY_CONTAINS().

Action Item: Delete all the SQL code in the editor and apply the code snippet below. We're going to be building on the previous query we wrote:

Major SQL code highlights:

  • We added lines 32-42.

  • On lines 14 & 31, we created a named result for the previous query we wrote.

  • On line 42, we are using ARRAY_CONTAINS() where we check to see if a specific product is part of the purchases.

The query results will be similar to this:

NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.

Last updated