Instruction
- Write a query to return the number of customers who rented at least one movie vs. those who didn't in May 2020.
- The order of your results doesn't matter.
- Use
customertable as the base table for all customers (assuming all customers have signed up before May 2020) Rented: if a customer rented at least one movie.- Bonus: Develop a
LEFT JOINas well as aRIGHT JOINsolution
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
has_rented | count -------------+------- rented | 123 never-rented | 456
Solution postgres
SELECT have_rented, COUNT(*)
FROM (
SELECT
C.customer_id,
CASE WHEN R.customer_id IS NOT NULL THEN 'rented' ELSE 'never-rented' END AS have_rented
FROM customer C
LEFT JOIN (
SELECT DISTINCT customer_id
FROM rental
WHERE DATE(rental_ts) >= '2020-05-01'
AND DATE(rental_ts) <= '2020-05-31'
) R
ON R.customer_id = C.customer_id
) X
GROUP BY have_rented;
Explanation
This query is trying to find out whether a customer has rented any movie during the month of May 2020 or not. It does this by joining the customer table with the rental table and filtering the results to only consider rentals made during the month of May 2020. The query then groups the results into two categories - 'rented' and 'never-rented' and calculates the count of customers in each category. This information can be useful for a data analyst to understand the rental behavior of customers and identify potential opportunities to increase rentals.
Expected results