Return the total and average customer spend
When we look up a customer, we’ll want to know how much they have spent and what is their average spend. This helps determine how much value the business is providing them.
2. Open a new tab by clicking on the
+ sign:
Query Editor
3. We’ll be querying
AggregateCustomerPurchaseActivities collections in order to calculate this. When we created the collection, we created daily rollups based on the user_id. Instead of grabbing the customer’s spend based on the daily timeframe, we’re interested in their total and average spend. Our query will look like this:Action Item: In the new empty editor, paste this code:
SQL query
Major SQL code highlights:
- We added lines 1-10.
- On lines 2-3 we are summing and averaging the customer's spend. We’re using the ROUND() function so the values go up to 2 decimal places.
- On lines 7-8 we are specifying the parameter,
:userid, we want the query results to return for. - On lines 9-10 we are grouping by the
user_id.
4. Run the query. You should get results similar to this:
Query Editor- results
NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.
Last modified 1yr ago