Instruction
- 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.
- Use
EXTRACT(YEAR from ts_field)andEXTRACT(MONTH from ts_field)to get year and month from a timestamp column. - The order of your results doesn't matter.
Table: payment
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
Sample results
year mon avg_spend 2020 2 3.2543037974683544 2020 5 9.1301559454191033
Expected results
Solution postgres
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;
Explanation
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.