Question 10
Instruction
MIN, and MAX function to return the min and max customer spend .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
min_spend | max_spend
-----------+-----------
0.99 | 52.90
Solution
postgresWITH 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;Explanation
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.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
5:00
Run your query to preview results here.