- 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.
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 a
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
has_rented | count -------------+------- rented | 123 never-rented | 456
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;
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 JOIN questions
|194||Daily active users report for new market snap||easy||
|191||Number of replies by group twitter||medium||
|172||Comments distribution facebook||hard||
|168||Average number of streams in the US tiktok||medium||
|140||Daily sales of restaurant 100011 doordash||hard||
|118||Daily bookings in the US airbnb||hard||
|100||Advertiser ROI facebook||medium||
|56||Total number of actors||easy||
|55||Busy days and slow days||medium||
|54||Customer groups by movie rental spend||medium||
|53||Movie groups by rental income||hard||
|52||Movies cast by movie only actors||easy||
|51||Movie only actor||easy||
|45||Movie inventory optimization||hard||
|44||In-demand vs not-in-demand movies||medium||
|42||Films that are in stock vs not in stock||medium||
|41||Productive actors vs less-productive actors||hard||