Return the customer's latest activities after their last purchase
1. We’re going to return the customer’s activities since their last purchase where they either added_to_cart or viewed and item.
Action Item: Delete all the SQL code in the editor and apply the code snippet below. We're going to build a CTE, a common table expression, so that we can write multiple sub-queries. Our first query will look like this- don't run the query just yet! You'll notice we're building from our previous query in the last section:
Major SQL code highlights:
On line 1, we created a temporary named result set that we’ll next reference.
2. For the second half of the query, we want to see the customer’s activities by querying the CustomerClickActivities collection. We’ll want to find their activities since their last purchase, and where they viewed or added_to_cart. The query will look like this (don't run the query just yet):
Major SQL code highlights:
On line 9, we grab the activities that have happened since their last purchase.
On lines 12-13, we filter the results where they
viewedoradded_to_cart.On line 16 we order by the
_event_timein descending order.
3. Let's combine both sub-queries and run it. It will look like this:
🗒️ If the results are null, that means the user didn't have any activity since their last purchase. Try a different :userid, until you get a result returned.
NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.
Last updated
