29. Second highest spend customer

medium

Instruction

  • Write a query to return the name of the customer who spent the second-highest for movie rentals in May 2020.

Table 1: customer

  col_name   | col_type
-------------+--------------------------
 customer_id | integer
 store_id    | smallint
 first_name  | text
 last_name   | text
 email       | text
 address_id  | smallint
 activebool  | boolean
 create_date | date
 active      | integer

Table 2: 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

 first_name | last_name
------------+-----------
 MARK       | ZUCKERBERG

Expected results

Solution postgres

WITH cust_spend_may AS (
    SELECT customer_id,
	SUM(amount) AS cust_spend
    FROM payment
    WHERE payment_ts >= '2020-05-01'
    AND payment_ts < '2020-06-01'
    GROUP BY customer_id
    ORDER BY cust_spend DESC
    LIMIT 2
)
SELECT 
    first_name, 
    last_name
FROM customer
WHERE customer_id IN (
    SELECT customer_id
    FROM cust_spend_may
    ORDER BY cust_spend
    LIMIT 1
);
    

Explanation

This query is trying to find the customer who spent the least amount of money in May 2020. It does this by first creating a temporary table called "cust_spend_may" which calculates the total amount spent by each customer in May 2020. It then selects the top 2 customers with the highest amount spent and stores their customer IDs.

After that, it selects the first name and last name of the customer who spent the least amount of money in May 2020 by checking which customer ID is in the temporary table "cust_spend_may" and selecting the one with the least amount spent.

Overall, this query is trying to find the customer who spent the least amount of money in May 2020 out of the top 2 customers who spent the most amount of money.



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
177 Purchases by platform report amazon medium
10%
176 Employees' annual bonus amazon easy
12%
160 Sellers with no sales by day ebay hard
11%
156 Cancellation rate by unbanned users lyft hard
15%
155 Driver with the highest cancellation rate lyft easy
24%
151 Salary report dropbox easy
18%
138 Happy restaurants doordash easy
14%
136 Extremely late orders doordash easy
11%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration mobile hard
17%
35 Film length report easy
27%
34 Stocked up movies easy
27%
33 Returning customers medium
8%
32 Unpopular movies hard
17%
31 Movies that have not been returned easy
26%
30 Inactive customers in May easy
22%
28 Film with the second largest cast medium
26%
27 Film with the largest cast easy
27%
26 Second shortest film easy
30%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
19%
22 Average cost per rental transaction easy
42%
15 Fast movie watchers vs slow watchers hard
6%
14 Good days and bad days hard
4%
13 Actors' first name medium
14%
12 Actors' last name ending in 'EN' or 'RY' easy
27%
11 Actors' last name easy
21%