- Write a query to return the average customer spend by month.
- Definition: average customer spend: total customer spend divided by the unique number of customers for that month.
EXTRACT(YEAR from ts_field)and
EXTRACT(MONTH from ts_field)to get year and month from a timestamp column.
- The order of your results doesn't matter.
Movie rental payment transactions table
col_name | col_type --------------+-------------------------- payment_id | integer customer_id | smallint staff_id | smallint rental_id | integer amount | numeric payment_ts | timestamp with time zone
year mon avg_spend 2020 2 3.2543037974683544 2020 5 9.1301559454191033
SELECT EXTRACT(YEAR FROM payment_ts) AS year, EXTRACT(MONTH FROM payment_ts) AS mon, SUM(amount)/COUNT(DISTINCT customer_id) AS avg_spend FROM payment GROUP BY year, mon ORDER BY year, mon;
This query is selecting data from the 'payment' table in a PostgreSQL database. The data selected includes the year and month of each payment transaction, as well as the average amount spent per customer during that time period.
The EXTRACT function is used to extract the year and month from the payment timestamp column ('payment_ts'). The SUM function is used to calculate the total amount spent during each month, and the COUNT and DISTINCT functions are used together to count the number of unique customers who made payments during that month. The result is an average spend per customer for each month in the dataset.
Finally, the results are grouped by year and month and ordered in ascending order by year and month. This allows the data analyst to easily see trends in average spend over time.