10. Min and max spend

easy

Instruction

  • 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 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

Expected results

Solution postgres

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;
    

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.



More COUNT, SUM, AVERAGE, MIN, MAX, GROUP BY, HAVING questions

ID Title Level FTPR
216 Highest spender roblox hard -
215 Monthly active users roblox hard -
214 D30 retention rate roblox hard -
209 Card issuer customers count visa easy -
206 Search results recall by testing group apple medium
100%
205 Search results recall apple medium
100%
204 AB testing sanity check apple easy
50%
202 Monthly active paid subscriptions apple hard
67%
198 Average score per subject snap easy
50%
197 Students who didn't finish all subjects snap easy
25%
196 User segments report snap hard
18%
193 Difference of replies by testing groups twitter medium
33%
192 Number of replies to the original tweet by group twitter medium
25%
190 Twitter campaign spend report twitter hard
22%
187 Comment distribution at Google Forum google easy
17%
186 Average comments per question google easy
10%
185 Top 5 non-English contents by watch time netflix medium
49%
184 Top 5 movies by watch time netflix medium
16%
183 Top 5 movies by number of watchers netflix easy
46%
181 Users who watched less than one hour of Netflix netflix easy
19%
174 Closed accounts social easy
23%
173 User Popularity Percentage social medium
11%
171 Binge watches tiktok easy
19%
170 Top 3 countries by average watch time tiktok easy
11%
169 Same day streams tiktok easy
34%
167 Top country in video streaming tiktok easy
28%
164 Orders distribution by brand walmart easy
28%
163 Average number of orders per brand walmart easy
32%
161 Biggest sales day ebay easy
20%
159 Transaction volume from suspended accounts ebay easy
58%
154 Rideshare completion rate lyft easy
25%
153 Project ROI dropbox easy
18%
149 fans by video category social easy
36%
146 Purchase by platform afterpay easy
14%
143 Customer purchase report afterpay easy
14%
134 Expensive departments robinhood medium
38%
133 Small departments robinhood easy
45%
132 Top 3 trending countries affirm medium
9%
130 Newly created accounts affirm easy
37%
128 Completion rate uber easy
26%
125 Active restaurants with fewer than 5 trips per city uber easy
16%
124 Active restaurants by city uber easy
41%
112 Top 3 customers amazon easy
30%
107 Customer count by platform mobile medium
17%
106 Mobile vs. web mobile medium
10%
101 Average number of visits per user mobile easy
14%
98 Most popular user social easy
33%
97 Social influencer social easy
10%
96 Overall acceptance rate social easy
13%
95 Candidate products for subscription amazon hard
13%
93 number of sales for each product this month amazon easy
20%
89 Top 5 artists in the US spotify medium
13%
86 Top song in the US spotify easy
18%
85 Top 4 queries based on click through rate on new year's day search engine hard
7%
84 Click through rate on new year's day search engine medium
9%
82 The top search query on new year's day search engine easy
13%
81 How many people searched on new year's day search engine easy
24%
23 Average spend per customer in Feb 2020 easy
16%
21 Customer who rented the most medium
16%
20 Customer who spent the most medium
14%
19 Most productive actor medium
13%
18 Top film category easy
22%
9 Number of high spend customers by month medium
4%
8 Average customer spend by month easy
20%
7 Unique customers count by month easy
18%
6 Daily revenue in June, 2020 medium
12%
5 Monthly revenue easy
20%