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 43
Customers who rented vs. those who did not
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 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
postgresSELECT 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.
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.
10:00
Your results
Run your query to preview results here.