Aggregate all the purchases

1. Weโ€™ll want to count all the purchases to see if there is a correlation of the product of interest and other products. For example, if a customer is interested in productId 530, are they more likely to buy other items with that product?

In the previous query, we get an array returned of other items that were purchased along with the item of interest via otherItemsPurchased. Weโ€™ll need to UNNEST() this array in order to count the number of times customers bought other products along with the product of interest. The full query will look like this:

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 45-54.

  • On line 49, we are using UNNEST() to flatten the array.

  • Once the array is flattened, we can count how many times a product was purchased via line 46-47.

The query results will look similar to this:

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

Last updated