Return a customer's previous purchase

In this section, we’re going to display the customer’s previous purchases.

1. Navigate back to the Query Editor.

2. Open a new tab by clicking on the + sign:

3. When we look at the CustomerPurchaseActivities collection fields, we’ll notice that the purchased fields has an array of product ids. In order to flatten the data, we’ll need to UNNEST() it. We did this earlier when we wrote the recommendation query. To only show a customer’s purchases, we can use the WHERE clause.

Action Item: In the new empty editor, paste this code:

Major SQL code highlights:

  • We added lines 1-9 .

  • On line 5 we flatten the array so we can get the product ids.

  • On line 7 we are grabbing a specific user via a parameter, :userid.

4. In order to run the query, we’ll need to create a parameter. Navigate to the Parameters tab and click on Add Parameter:

5. Add the parameter details and click on Add:

6. Run the query! The results should look something like this:

If nothing is returned, changed the parameter value to a different number.

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

Last updated