33. Returning customers

medium

Instruction

  • Write a query to return the number of customers who rented at least one movie in both May 2020 and June 2020.

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


 count
-------
   123

Solution 1: postgres

WITH may_cust AS (
	SELECT DISTINCT customer_id AS may_cust_id
	FROM rental
	WHERE DATE(rental_ts) >= '2020-05-01'
    AND   DATE(rental_ts) <= '2020-05-31'
)

SELECT COUNT(DISTINCT customer_id)
FROM rental 
WHERE DATE(rental_ts) >= '2020-06-01'
AND   DATE(rental_ts) <= '2020-06-30'
AND  customer_id IN (
    SELECT may_cust_id
    FROM may_cust
);
    

Explanation

This query is trying to count the number of customers who rented a product in June 2020 but also rented something in May 2020.

The first part of the query creates a common table expression (CTE) named "may_cust" which selects all the unique customer IDs who rented something between May 1, 2020, and May 31, 2020.

The second part of the query selects the count of distinct customer IDs who rented something between June 1, 2020, and June 30, 2020, and also rented something in May 2020.

The "IN" clause in the second part of the query is used to filter the results to only include customers who rented something in May 2020, as identified by the "may_cust" CTE.

Overall, this query is useful for analyzing customer retention and identifying which customers are returning to rent products.

Solution 2: postgres

-- You can also use INNER JOIN to get the overlap between May and June paid customers

WITH may_customers AS (
	SELECT DISTINCT customer_id 
	FROM rental
	WHERE DATE(rental_ts) >= '2020-05-01'
        AND   DATE(rental_ts) <= '2020-05-31'
), 

june_customers AS (
	SELECT DISTINCT customer_id 
	FROM rental
	WHERE DATE(rental_ts) >= '2020-06-01'
    AND   DATE(rental_ts) <= '2020-06-30'
)
SELECT COUNT(M.customer_id)
FROM may_customers M
INNER JOIN june_customers J
ON M.customer_id = J.customer_id
;
    

Explanation

This query retrieves the number of customers who rented from a company in both May and June of 2020. It starts by creating two temporary tables, one for May customers and another for June customers, based on the rental data. Then, it uses an INNER JOIN to combine the two tables and only keep the rows where the customer ID is the same in both tables. Finally, it counts the number of rows in the resulting table, which represents the number of customers who rented in both months.

Last Submission postgres

Expected results



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 google hard
17%
35 Film length report easy
27%
34 Stocked up movies easy
27%
32 Unpopular movies hard
17%
31 Movies that have not been returned easy
26%
30 Inactive customers in May easy
22%
29 Second highest spend customer medium
14%
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%