***********
- ********************************************************************************
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
avg
--------------------
1.23456789
Solution postgres
WITH cust_feb_spend AS (
SELECT customer_id,
SUM(amount) cust_spend
FROM payment
WHERE DATE(payment_ts ) >= '2020-02-01'
AND DATE(payment_ts ) <= '2020-02-28'
GROUP BY customer_id
)
SELECT AVG(cust_spend)
FROM cust_feb_spend
;
Explanation
This query is calculating the average spend per customer in the month of February 2020.
The first part of the query creates a temporary table called "cust_feb_spend". It selects the customer ID and the sum of the amount spent by each customer in February 2020 from the "payment" table. The WHERE clause filters the payments made within the month of February. Finally, the GROUP BY clause groups the results by customer ID.
The second part of the query selects the average of the "cust_spend" column from the temporary table "cust_feb_spend". This gives us the average spend per customer in February 2020.
Expected results