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