- Write a query to return the minimum and maximum customer total spend in June 2020.
- For each customer, first calculate their total spend in June 2020.
- Then use
MAXfunction to return the min and max customer spend .
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
min_spend | max_spend -----------+----------- 0.99 | 52.90
WITH cust_tot_amt AS ( SELECT customer_id, SUM(amount) AS tot_amt FROM payment WHERE DATE(payment_ts) >= '2020-06-01' AND DATE(payment_ts) <= '2020-06-30' GROUP BY customer_id ) SELECT MIN(tot_amt) AS min_spend, MAX(tot_amt) AS max_spend FROM cust_tot_amt;
This query is trying to find the minimum and maximum amounts spent by customers on payments made between June 1st, 2020 and June 30th, 2020.
First, it creates a temporary table called "cust_tot_amt" that aggregates the total amount spent by each customer during the specified time period.
Then, the main query selects the minimum and maximum total amounts from the "cust_tot_amt" table, which gives us the desired result.
Overall, this query is useful for analyzing spending patterns of customers during a specific time period.