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
Question 30
Inactive customers in May
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 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
Solution
postgresSELECT 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
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
5:00
Your results
Run your query to preview results here.