21. Customer who rented the most

medium

Instruction
  • Write a query to return the first and last name of the customer who made the most rental transactions 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: rental

   col_name   | col_type
--------------+--------------------------
 rental_id    | integer
 rental_ts    | timestamp with time zone
 inventory_id | integer
 customer_id  | smallint
 return_ts    | timestamp with time zone
 staff_id     | smallint

Sample results

 first_name | last_name
------------+-----------
 JENNIFER   | ANISTON

Expected results

Solution postgres

WITH cust_may_rentals AS (
	SELECT 
		customer_id,
		COUNT(*) AS cust_rentals
	FROM rental
	WHERE DATE(rental_ts) >= '2020-05-01'
	AND DATE(rental_ts) <= '2020-05-31'
	GROUP BY customer_id
	ORDER BY cust_rentals DESC
	LIMIT 1
)
SELECT first_name, last_name 
FROM customer
WHERE customer_id IN (
	SELECT customer_id 
	FROM cust_may_rentals
);
    

Explanation

This query retrieves the first and last names of the customer who rented the most movies in May 2020.

First, it creates a temporary table (CTE) called cust_may_rentals, which counts the number of rentals for each customer in May 2020 and orders the result in descending order by the number of rentals. It then selects the customer with the most rentals by using the LIMIT 1 clause.

Next, the main query selects the first and last names of the customer(s) whose customer ID matches the customer ID(s) in the cust_may_rentals table. It does this by using a subquery in the WHERE clause that selects the customer ID(s) from the cust_may_rentals table.

Overall, this query allows a data analyst to quickly identify the customer who rented the most movies in May 2020 and retrieve their first and last names for further analysis or reporting.



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%
20 Customer who spent the most medium
14%
19 Most productive actor medium
13%
18 Top film category easy
22%
10 Min and max spend easy
11%
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%