43. Customers who rented vs. those who did not

medium

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 customer table 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 JOIN as well as a RIGHT JOIN solution

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

Expected results

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.



More OUTER JOINS questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy -
194 Daily active users report for new market snap easy -
191 Number of replies by group twitter medium
100%
172 Comments distribution social hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
9%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI social medium
9%
56 Total number of actors easy
32%
55 Busy days and slow days medium
11%
54 Customer groups by movie rental spend medium
21%
53 Movie groups by rental income hard
11%
52 Movies cast by movie only actors easy
13%
51 Movie only actor easy
43%
45 Movie inventory optimization hard
7%
44 In-demand vs not-in-demand movies medium
6%
42 Films that are in stock vs not in stock medium
11%
41 Productive actors vs less-productive actors hard
6%