I'm trying to figure out how to find for the monthly average order value of a dataset.
The dataset has 4 columns: id, total_price, date created
I'm going through the tutorial but have not been able to find a method for my initial inquiry.
Any help would be greatly appreciated.
Hey Lou Wills ! You can use something like the following query:
SELECT AVG(total_amt_usd), date_trunc('month', occurred_at) AS month FROM demo.orders GROUP BY month ORDER BY month
This query makes use of the date_trunc function, which returns a string representing the date truncated to the specified time interval - in this case, to the month. It then takes the average of the price column, grouped by the truncated date. You can try this query out yourself in the Mode Public Warehouse, which houses the demo.orders table.
To learn more about aggregate functions like AVG, check out this section of our SQL Tutorial!Reply