30. Inactive customers in May

easy

Instruction

  • Write a query to return the total number of customers who didn't rent any movies in May 2020.
Hint
  • You can use NOT IN to exclude customers who have rented movies 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

 count
-------
 1234

Expected results

Solution postgres

SELECT COUNT(*) 
FROM customer
WHERE customer_id NOT IN(
	SELECT customer_id
	FROM rental
	WHERE  DATE(rental_ts) >= '2020-05-01'
	AND    DATE(rental_ts) <= '2020-05-31'
);
    

Explanation

This query is counting the number of customers who did not rent any movies in May 2020.

The query first selects the table "customer" and then filters it using a subquery. The subquery selects the customer IDs from the "rental" table where the rental date is between May 1, 2020, and May 31, 2020.

The "NOT IN" operator then excludes those customer IDs from the main query, leaving only the customer IDs who did not rent any movies during that time period.

Finally, the "COUNT(*)" function counts the number of customers in that filtered list.



More IN, BETWEEN, LIKE, CASE WHEN questions

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