Instruction
- Write a query to return the total number of customers who didn't rent any movies in May 2020.
- You can use
NOT INto 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.
Copied
Your results